# Admin Area Features

> **Status 2026-08-04 — original requirements doc, largely delivered. Kept for the intent
> behind the admin area, not as a description of it.**
>
> Read it as "what we set out to build". Several specifics below have since been overtaken,
> and where they conflict with the plans named here, the plans win:
>
> - **"Field Management" is now "Module Management"** and covers a module's sections,
>   categories, fields, statuses *and* dashboard settings, laid out as a left rail rather
>   than a tab strip — `docs/2026-08-03-admin-section-category-organisation.md`.
> - **Dashboard config is an area of that page**, not the separate `?do=admin_dashboard`
>   page described below; that route survives only for existing links.
> - **Statuses are admin-managed with per-status styling** —
>   `docs/2026-08-03-admin-status-management.md`.
> - **Sub-categories are hidden** from the UI, with the backend left in place.
> - `?do=admin` is a module × area configuration matrix, not a set of link cards.
>
> `CLAUDE.md` → *Admin Structure* is the current summary.

## Features to add

### User profile management

** Priority 2 **

- Control user profiles.
- Control user permissions with roles and item ownership based control, e.g. read/write access to accounts, projects, stocklists. Also allow acces to an item if a user has been assigned to it.

### Project Management

** Priority 1 **

Add admin functionality to manage projects
- Add/remove/modify dynamic fields
- Static fields defined in the projects.projects table should be listed but as read only

### Account Management

** Priority 1 **

Add admin functionality to manage accounts
- Add/remove/modify dynamic fields
- Static fields defined in the accounts.accounts table should be listed but as read only

### Stocklist Management

** Priority 1 **

Add admin functionality to manage stocklists
- Add/remove/modify dynamic fields
- Static fields defined in the stocklists.stocklists table should be listed but as read only

### Module management


** Priority N/A **

- TBC Do not work on this yet. Eventually GeoLynx customers will be able to add/remove modules, to suit their needs.

---

## Implementation Plan

### Routing & File Conventions

Following the existing pattern in `www/fn/global_functions.php::load_file()`, new admin routes use `?do=admin*`. Each feature gets its own entry in `$pageFiles` mapping to an HTML template, JS file, and CSS file.

New files to create:

| Type | File | Purpose |
|------|------|---------|
| HTML | `www/html/html_body_admin_home.php` | Admin landing page / nav hub |
| HTML | `www/html/html_body_admin_users.php` | User management UI |
| HTML | `www/html/html_body_admin_fields.php` | Dynamic field management UI (shared for all three modules) |
| JS | `www/js/admin_users.js` | User management logic |
| JS | `www/js/admin_fields.js` | Dynamic field management logic (shared) |
| CSS | `www/css/admin.css` | Admin-specific styles |
| PHP | `www/fn/admin_load.php` | All admin GET/read endpoints |
| PHP | `www/fn/admin_save.php` | All admin POST/write endpoints |

Routes to register in `load_file()`:

```
?do=admin              → Admin home
?do=admin_users        → User management
?do=admin_fields       → Dynamic field management (module passed as query param, e.g. &module=projects)
```

Admin access should be gated in `admin_load.php` and `admin_save.php` by checking `$_SESSION['role']` against `users.roles`. Only users with an admin role (to be agreed — suggest `role_id = 1`) should reach these endpoints; all others receive a 403 JSON response.

---

### Priority 1 — Dynamic Field Management (Projects, Accounts, Stocklists)

#### Database tables involved

All three modules share the same field schema pattern:

| Module | Fields table | Value tables |
|--------|-------------|--------------|
| Projects | `projects.project_fields` | `projects.project_field_values_{boolean,date,int,numeric,text}` |
| Accounts | `accounts.account_fields` | `accounts.account_field_values_{boolean,date,int,numeric,text}` |
| Stocklists | `stocklists.stocklist_fields` | `stocklists.stocklist_field_values_{boolean,date,int,numeric,text}` |

The `*_fields` tables have the following key columns:

- `field_id` — primary key
- `field_name` — display label
- `field_data_type` — determines which value table is used (`boolean`, `date`, `int`, `numeric`, `text`)
- `field_input_type` — UI input type (e.g. `text`, `select`, `checkbox`, `date`)
- `field_form_id` — HTML element ID used in the front-end form
- `field_section`, `field_category`, `field_sub_category` — grouping/organisation
- `field_display_order` — render order within a section
- `field_required` — validation flag
- `field_active` — soft-delete / show/hide flag
- `field_spacer_after` — layout hint
- `field_type` — additional type classification

`accounts.account_fields` has two extra columns:
- `pass_field_to_stocklist` — whether the field value is propagated to a linked stocklist
- `pass_field_to_project` — whether the field value is propagated to a linked project

`stocklists.stocklist_fields` has:
- `pass_field_to_project`

#### Static fields (read-only display)

The following columns are static (defined in the base table, not via dynamic fields) and must be listed in the UI as read-only reference rows:

**projects.projects:** `project_id`, `parent_project_id`, `project_name`, `project_status_id`, `project_manager`, `company_id`, `account_id`, `stocklist_id`, `created_datetime`, `created_user`, `modified_datetime`, `modified_user`

**accounts.accounts:** `account_id`, `parent_account_id`, `account_name`, `account_status_id`, `account_manager`, `company_id`, `created_datetime`, `created_user`, `modified_datetime`, `modified_user`

**stocklists.stocklists:** `stocklist_id`, `parent_stocklist_id`, `stocklist_name`, `stocklist_status_id`, `stocklist_manager`, `company_id`, `account_id`, `created_datetime`, `created_user`, `modified_datetime`, `modified_user`

#### UI behaviour

A single shared page (`?do=admin_fields&module=projects|accounts|stocklists`) renders:

1. **Static fields section** — read-only table listing the base table columns above with their data types. No edit controls.
2. **Dynamic fields section** — Tabulator table listing all rows from `*_fields`. Columns: Field Name, Section, Category, Display Order, Data Type, Input Type, Required, Active. Inline row editing for all editable columns.

Actions available on dynamic fields:
- **Add field** — modal form to create a new row in `*_fields`. Required inputs: `field_name`, `field_data_type`, `field_input_type`, `field_form_id`, `field_section`, `field_display_order`, `field_required`, `field_active`. `field_form_id` should auto-generate from `field_name` (snake_case) but remain editable.
- **Edit field** — inline or modal edit of all columns except `field_id` and `field_data_type` (data type is immutable once values exist — check value tables before allowing change).
- **Deactivate / Activate field** — toggles `field_active`. This is the preferred "delete" to preserve historic values. Show a warning if the field has existing values.
- **Delete field** — hard delete. Only permitted if all five value tables contain zero rows for that `field_id`. Show a confirmation modal with the count of affected records.
- **Reorder fields** — drag-and-drop row reordering in the Tabulator table, saving updated `field_display_order` values on drop.

For `account_fields` only, show toggles for `pass_field_to_stocklist` and `pass_field_to_project`.
For `stocklist_fields` only, show a toggle for `pass_field_to_project`.

#### Backend endpoints (`admin_load.php` / `admin_save.php`)

Modes for `admin_load.php`:

| Mode | Action | SQL |
|------|--------|-----|
| `fields_list` | Load all fields for a module | `SELECT * FROM {schema}.{module}_fields ORDER BY field_section, field_display_order` |
| `field_value_count` | Check if a field has values before delete | `SELECT COUNT(*) FROM each of the 5 value tables WHERE field_id = :field_id` |

Modes for `admin_save.php`:

| Mode | Action | SQL |
|------|--------|-----|
| `field_add` | Insert new field | `INSERT INTO {schema}.{module}_fields (...)` |
| `field_update` | Update field properties | `UPDATE {schema}.{module}_fields SET ... WHERE field_id = :field_id` |
| `field_toggle_active` | Toggle `field_active` | `UPDATE ... SET field_active = NOT field_active WHERE field_id = :field_id` |
| `field_delete` | Hard delete (only if 0 values) | `DELETE FROM {schema}.{module}_fields WHERE field_id = :field_id` |
| `fields_reorder` | Bulk update display order | `UPDATE ... SET field_display_order = :order WHERE field_id = :field_id` (loop) |

The `module` parameter (`projects`, `accounts`, `stocklists`) must be validated against an allowlist before being used to construct the schema/table name.

---

### Priority 2 — User Profile Management

#### Database tables involved

- `users.users` — core user record
- `users.roles` — role definitions (name and description only — no module flags)
- `users.user_roles` — many-to-many junction: which roles a user holds *(new)*
- `users.role_permissions` — which modules a role can access and at what level *(new)*
- `users.user_item_permissions` — explicit record-level overrides *(new)*
- `users.companies` — company records (`company_id`, `company_name`, `company_active`)
- `users.users_login_log` — login history (read-only audit)

`users.users` columns:

| Column | Notes |
|--------|-------|
| `id` | Primary key |
| `username` | Display name |
| `email` | Login credential |
| `password` | Bcrypt hash — never returned to frontend |
| `role` | Legacy single-role column — replaced by `user_roles` junction table, drop after migration |
| `company` | FK → `users.companies.company_id` |
| `manager` | Manager name/reference |
| `division`, `team` | Organisational grouping |
| `is_active` | 1 = active, 0 = suspended |
| `date_user_created`, `date_last_login`, `date_last_active` | Audit timestamps (read-only in UI) |

#### Permission model

Access is resolved in two tiers:

1. **Role level** — a user's combined roles are checked against `role_permissions` for the relevant module. If any role grants `write`, the user can edit. If any grants `read` (and none grant `write`), the user can view only. If no role covers the module, access is denied.
2. **Item level** — `user_item_permissions` can grant access to a specific record regardless of role, useful for assigning a user to a single project outside their normal scope.

This means adding a new module in future only requires inserting rows into `role_permissions` — no schema change.

Example role setup:

| role_name | module | permission_level |
|---|---|---|
| Admin | projects | write |
| Admin | accounts | write |
| Admin | stocklists | write |
| Project Manager | projects | write |
| Project Manager | accounts | read |
| Account Manager | accounts | write |
| Stocklist Manager | stocklists | write |

A user needing projects + accounts gets both "Project Manager" and "Account Manager" assigned in `user_roles`.

#### DDL additions required

Include these in a migration script (`admin_migration_v1.sql`):

```sql
-- Many-to-many: users ↔ roles
CREATE TABLE users.user_roles (
    user_id bigint NOT NULL REFERENCES users.users(id),
    role_id integer NOT NULL REFERENCES users.roles(role_id),
    granted_datetime timestamp without time zone DEFAULT now(),
    granted_by integer REFERENCES users.users(id),
    PRIMARY KEY (user_id, role_id)
);

-- Module-level permissions per role
-- module values: 'projects', 'accounts', 'stocklists' (extensible by inserting new rows)
-- permission_level values: 'read', 'write'
CREATE TABLE users.role_permissions (
    permission_id serial PRIMARY KEY,
    role_id integer NOT NULL REFERENCES users.roles(role_id),
    module character varying(50) NOT NULL,
    permission_level character varying(10) NOT NULL DEFAULT 'read',
    UNIQUE (role_id, module)
);

-- Record-level overrides (grants access to a specific item regardless of role)
CREATE TABLE users.user_item_permissions (
    permission_id bigserial PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users.users(id),
    item_type character varying(20) NOT NULL,  -- 'project', 'account', 'stocklist'
    item_id integer NOT NULL,
    permission_level character varying(10) NOT NULL DEFAULT 'read',
    granted_datetime timestamp without time zone DEFAULT now(),
    granted_by integer REFERENCES users.users(id),
    UNIQUE (user_id, item_type, item_id)
);

CREATE INDEX idx_user_roles_user ON users.user_roles(user_id);
CREATE INDEX idx_role_permissions_role ON users.role_permissions(role_id);
CREATE INDEX idx_user_item_permissions_user ON users.user_item_permissions(user_id);
CREATE INDEX idx_user_item_permissions_item ON users.user_item_permissions(item_type, item_id);

-- Drop legacy single-role column once user_roles is populated
-- ALTER TABLE users.users DROP COLUMN role;
```

#### UI behaviour (`?do=admin&section=users`)

**User list** — Tabulator table of all users showing: Username, Email, Active status, Company, Last Login, Last Active. Sortable and searchable.

Actions:
- **Add user** — modal form: username, email, password (hashed server-side with `password_hash()`), company, manager, division, team, is_active. Roles assigned in a separate tab after creation.
- **Edit user** — modal with tabs:
  - *Profile* — all fields above. Password blank by default; only updated if a new value is entered.
  - *Roles* — checklist of all roles in `users.roles`. Checked roles are rows in `user_roles`. Saving replaces the user's role set.
  - *Item Permissions* — three sub-tables (Projects, Accounts, Stocklists) listing rows in `user_item_permissions`. Each row shows item name + permission level. Rows can be added (item search/picker + read/write dropdown) or removed.
- **Suspend / Activate** — toggle `is_active`.
- **View login history** — modal showing last 20 rows from `users.users_login_log`.

**Role management** (separate tab on the admin users page):
- List all rows in `users.roles` with their `role_permissions` expanded inline.
- Add role — create a `users.roles` row, then assign module permissions (module name + read/write) as child rows.
- Edit role — change name/description; add/remove module permission rows.
- Delete role — only permitted if no users are assigned (`user_roles` has no rows for that role).

#### Backend endpoints

Modes for `admin_load.php`:

| Mode | Action |
|------|--------|
| `users_list` | All users with company name joined |
| `user_roles_list` | All `user_roles` rows for a user_id, with role names |
| `roles_list` | All roles with their `role_permissions` rows |
| `companies_list` | All active companies |
| `user_login_history` | Last 20 rows from `users.users_login_log` for a user |
| `user_item_permissions` | All `user_item_permissions` rows for a user, with item names joined |

Modes for `admin_save.php`:

| Mode | Action |
|------|--------|
| `user_add` | Insert into `users.users`, hash password |
| `user_update` | Update user; only update password hash if new password provided |
| `user_toggle_active` | Toggle `is_active` |
| `user_roles_set` | Delete all `user_roles` for user, re-insert checked role IDs |
| `role_add` | Insert into `users.roles` |
| `role_update` | Update `users.roles` name/description |
| `role_permission_set` | Delete all `role_permissions` for role, re-insert submitted module rows |
| `role_delete` | Delete role if `user_roles` has no rows for it |
| `item_permission_add` | Insert into `user_item_permissions` |
| `item_permission_remove` | Delete from `user_item_permissions` by `permission_id` |

---

### Company-based Visibility Model

Items (projects, accounts, stocklists) each carry a `company_id` FK. Access is resolved in two steps:

1. **Visibility** — can the user see the item in lists/maps?
   - `item.company_id = user.company` (item belongs to the user's company), **OR**
   - A `user_item_permissions` row exists for that user + item (explicit override)

2. **Access level** — once visible, what can the user do?
   - Resolved from `role_permissions` as normal (read / write)

This means a subcontractor at Company B automatically sees all items assigned to Company B, but still needs the appropriate role to open, edit, or create records. No extra tables are required beyond `users.users.company` (existing FK).

#### Permission modules

| Module | Controls |
|---|---|
| `projects` | Project data read/write |
| `accounts` | Account data read/write |
| `stocklists` | Stocklist data read/write |
| `admin_fields` | Field Management admin pages |
| `admin_users` | Users & Roles admin pages |
| `admin_companies` | Company Management admin page |

#### Company Management (admin)

Companies are managed via the **Companies** tab on `?do=admin_users`. CRUD operations: add, edit, toggle active. Deactivating a company hides it from dropdowns but does not affect existing records or user access. Each company row shows a user count (users whose `company` FK references that company).

---

### Priority N/A — Module Management

TBC. Do not work on this yet. Eventually GeoLynx customers will be able to add/remove modules to suit their needs.

---

## Build Order

1. Routing — register admin routes in `global_functions.php`, create `admin.css` - done
2. Admin home page — landing page with nav cards to each section - done
3. Dynamic field management — highest priority; shared across all three modules - Done
   - `admin_load.php` + `admin_save.php` (fields modes)
   - `html_body_admin_fields.php` + `admin_fields.js`
   - Test with Projects first, then re-use for Accounts and Stocklists 
4. User management - Done
   - `admin_area.sql` — DDL for `user_roles`, `role_permissions`, `user_item_permissions`; adds `user_id` to `users_login_log`; migrates legacy `role` column to junction table
   - `admin_load.php` + `admin_save.php` (users modes) — users_list, user_roles_list, roles_list, companies_list, user_login_history, user_item_permissions_list; user_add, user_update, user_toggle_active, user_roles_set, role_add, role_update, role_permission_set, role_delete, item_permission_add, item_permission_remove
   - `html_body_admin_users.php` — Users tab (Tabulator + edit modal with Profile/Roles/Item Permissions sub-tabs) + Roles tab
   - `admin_users.js` — full CRUD for users, roles, and item-level permissions
5. Integrate permission checks into existing `*_load.php` / `*_save.php` endpoints using the two-tier model (role_permissions → user_item_permissions)
 

#