# Backend module

## Purpose

The Backend module stores the hosted PHP API source, PHP session authentication, module-owned database schemas, migrations, authorization rules, and route handlers.

## Deployment

```text
Repository source: backend/apis/
Hosted public path: /apis/
Private config: /home/dabounce/sites/warehouse-tools-database.php
```

Deploy the **contents** of `backend/apis/`, never the wrapper directory or private credentials.

## Important files

- `backend/apis/index.php` - top-level routing, public health/auth routes, authenticated module dispatch, CORS credentials, and database checks.
- `backend/apis/bootstrap.php` - Product/User schema and migrations `001`, `002`, `003`, and `005`.
- `backend/apis/module-bootstrap.php` - operational module table bootstrap and migration `004`.
- `backend/apis/lib/module-json-resource.php` - shared safe JSON-record persistence helper.
- `backend/apis/lib/sql-safety.php` - strict identifier, ORDER BY, database-name, and schema-fragment validation for SQL structure that cannot use placeholders.
- `backend/apis/modules/auth/` - PHP sessions, setup/login/logout, role helpers, password hashes, and password updates.
- `backend/apis/modules/products/` - Product master-data API.
- `backend/apis/modules/users/` - User master-data API and `administrators`-only account changes.
- `backend/apis/modules/access-control/` - Role, Access group, and Access policy schemas/APIs, migrations, User memberships, and lifecycle protections.
- `backend/apis/modules/grns/` - GRN schema, record persistence, completion/reopen lifecycle, and ownership permissions.
- `backend/apis/modules/locations/` - Location master data, per-opening slot profiles/clearance, capacity, occupancy, and lifecycle validation.
- `backend/apis/modules/warehouse-map/` - revisioned measured 2D layout, Location footprints, collision validation, and live occupancy projection.
- `backend/apis/modules/pallets/` - live GRN/opening-stock Pallet inventory, transactional CSV onboarding, authoritative slot assignment, receipt synchronisation, Location actions, and movement history.
- `backend/apis/modules/settings/` - generic revisioned Warehouse and Visual settings with independent allow-listed validation.
- `backend/apis/modules/visual-settings/` - compatibility endpoint for earlier Visual settings clients.
- `backend/apis/modules/custom-fields/` - revisioned allow-listed module field definitions, field-level Access groups, validation, lifecycle, and shared related values.
- `backend/apis/modules/mhe/` - forklift, check-definition, and check-record schemas/routes.
- `backend/apis/modules/planner/` - Planner load schema/routes.
- `backend/apis/modules/kanban/` - board/list/card schemas/routes.
- `backend/apis/modules/data-imports/` - transactional cross-module import orchestration and batch summaries.

## Ownership rules

Each business module owns:

- its backend folder;
- its API route hierarchy;
- its MySQL table or related table group;
- its indexed business columns;
- its authorization and lifecycle rules;
- its local migration and compatibility rules.

Shared helpers may reduce duplicated SQL, but requests must never supply arbitrary table names. Do not add a generic catch-all operational table as the production architecture.

## Tables

```text
products
users
roles
access_groups
access_group_users
access_policies
grns
locations
location_slots
warehouse_maps
warehouse_map_placements
pallets
pallet_movements
page_layouts
visual_settings
settings
custom_field_definitions
product_custom_field_values
custom_field_values
mhe_forklifts
mhe_check_definitions
mhe_check_records
planner_loads
kanban_boards
kanban_lists
kanban_cards
data_import_batches
```

`visual_settings` remains as the migration/rollback source for established deployments; the revisioned `settings.visual` row is authoritative after migration `021`.

Operational JSON-resource tables retain compatible JSON in `record_json` while exposing indexed columns for common lookups and future reporting. GRN JSON stores only stable Pallet references; the authoritative receipt quantity, lifecycle status, and Location are hydrated from `pallets`.

## Migrations

```text
001-initial-schema
002-remove-product-pallet-qty
003-users-crud-api
004-module-owned-operational-tables
005-session-authentication
006-grn-archive-lifecycle
007-grn-locks-search-activity
008-role-groups-access-control
009-access-group-users
010-pallets-locations-inventory
011-drive-in-lane-capacity
012-admin-page-layouts
013-grn-live-pallet-inventory
014-planner-schedule-board
015-visual-settings
016-product-custom-fields
017-shared-custom-fields
018-configured-super-administrator
019-configurable-access-policies
020-location-slot-profiles
021-settings-hub
022-ground-based-location-levels
023-pallet-opening-stock
024-data-imports
025-staff-data-imports
026-form-page-layouts
027-kanban-board-memberships
028-planner-drag-scroll-settings
029-warehouse-map-2d
030-warehouse-map-visual-settings
031-warehouse-map-editor-enhancements
```

Migration `004` creates module-owned operational tables and does not remove legacy browser data.

Migration `005` adds password hashes and last-login timestamps to Users. Password hashes must never be included in public User records.

Migration `011` extends Location/Pallet storage with separate Drive-in Standard/Euro capacity, Ground-based lane levels, automatic internal depth, and matching movement-history fields.

Migration `022` makes Aisle level counts Ground-based too, shifting established slot, current Pallet, and movement-history coordinates from 1..N to 0..N-1 once while retaining physical slot relationships.

Migration `023` adds explicit GRN/opening-stock source metadata, nullable GRN ownership, creating-User/note fields, and audited all-or-nothing `pallet_opening_stock_imports` batches without changing established Pallet sources.

Migration `024` creates import-batch audit summaries. Migration `025` registers protected staff/access import actions. Module-owned validators feed one server-owned Role → Access group → User → membership → Product → Location → Pallet transaction; passwords and policy assignments remain excluded.

Migration `012` adds revisioned Administrator Page layout overrides. Migration `026` adds separate Desktop/Mobile form-layout entries with allow-listed 12-unit widths. Only validated keys, order, visibility, widths, and updater metadata are stored; trusted rendering, required state, validation, actions, and permissions stay in module code.

Migration `013` links Pallets to numeric GRN rows, creates `receiving` inventory for open GRNs, retains `available` completed inventory and managed Locations, and converts GRN Pallet JSON to stable references.

Migration `014` makes Planner load moves revision-safe, records creator/updater metadata, and adds explicit Booked/Anytime timing while retaining existing module-owned `planner_loads` records.

Migration `015` creates the global revisioned Visual settings row and seeds the toast display duration to one second.

Migration `016` creates validated Product Custom field definitions and the original Product value table. Migration `017` adds `custom_field_values` for allow-listed Products, Users, Roles, Access groups, Locations, Pallets, GRNs, and Planner records and safely copies existing Product values. Neither migration dynamically adds fields to core record tables; browser field keys are never used as SQL identifiers.

Migration `018` keeps Administrator as the sole seeded system Role and safely unlocks the five earlier job-role defaults without deleting records or changing User assignments. Super administrator authority is derived from `security.super_administrator_user_uuid` in the private server configuration.

Migration `019` creates revisioned, allow-listed module/action Access policies. Established assignments and memberships are preserved while the five earlier policy groups become ordinary renameable records; fresh databases seed only the protected Administrators group and safe defaults.

Migration `020` creates Position/Level `location_slots`, adds default and per-opening clearance/profile data, and backfills `pallets.location_slot_id` from existing compatible coordinates without changing Location addresses or movement history.

Migration `021` creates the generic revisioned `settings` table, copies the existing Visual settings value and audit metadata, seeds Warehouse Location-height presets, and retains compatibility routes while `/settings/` becomes the browser hub.

Migration `029` creates one revisioned main Warehouse map and its Location footprint rows. Coordinates, dimensions, rotation, and audit metadata are stored without duplicating Pallet inventory; occupancy remains a live join.

If an early draft `app_records` table exists on a test database, do not drop it automatically; verify it is empty or export it before manual cleanup.

## Authentication and authorization

Public routes are limited to API information, health, and authentication setup/login/logout.

All business-data routes require a valid PHP session. Authorization helpers live under `backend/apis/modules/auth/` and must be called from the server route that owns the protected action.

Current enforced rules include:

- native PDO prepared statements separate request data from SQL code;
- schema/table/column/order fragments are server-owned and allow-listed;
- `security.schema_bootstrap_enabled=false` skips all automatic DDL so production may run with a DML-only MySQL account;

- `/apis/db-check` requires the `administrators` Access group;
- User account changes and password resets require the `administrators` Access group;
- the private-configured Super administrator implicitly passes every Access group/module/action check and cannot be created through User CRUD or changed by another User;
- a signed-in Administrator cannot remove their own access or delete their own account;
- completed GRNs cannot be edited;
- completed GRNs may be reopened only by Administrator or Office operative;
- GRNs may be deleted only by creator, Administrator, or Office operative.

Browser-hidden buttons are not security controls. Every sensitive action must also be rejected by PHP when unauthorized.

## API routes

```text
/apis/auth/*
/apis/products
/apis/users
/apis/page-layouts
/apis/settings
/apis/visual-settings
/apis/grns
/apis/warehouse-map
/apis/mhe/forklifts
/apis/mhe/check-definitions
/apis/mhe/check-records
/apis/planner/loads
/apis/kanban/boards
/apis/kanban/lists
/apis/kanban/cards
/apis/data-imports
```

## Rules

- Use prepared statements for request values.
- Whitelist all table names, column names, route resources, and sort fields.
- Keep public fields camelCase and SQL columns snake_case.
- Store passwords only as hashes.
- Regenerate the PHP session ID after successful authentication.
- Keep session cookies HTTP-only, SameSite=Lax, and Secure on hosted HTTPS.
- Preserve stable IDs during controlled legacy imports.
- Do not silently fall back to browser business data when an API request fails.
- Keep `/apis/db-check` restricted to `administrators` and consider removing it after deployment work is complete.

## Known issues

- Branch API changes are not live until deployed to a disposable staging `/apis/` path.
- Schema bootstrap currently runs during normal API startup.
- A dedicated audit-event table and production session timeout policy remain future work.
- Authentication and role behavior still require multi-user staging tests before merge.

## Validation

Run the PHP syntax commands in `backend/apis/README.md`, then complete `TESTING_REMOTE_MYSQL.md` against a disposable staging database before approving draft PR #9.