Data Schema
Postgres schema & access policies
All core tables, their relationships, and the row-level security rules that gate access per role. 2,372 rows ready for migration.
customers105 rows
| id | uuid | PK |
| name | text | |
| text | unique | |
| phone | text | |
| address | text | |
| status | enum | active/inactive |
| created_at | timestamptz |
vehicles118 rows
| id | uuid | PK |
| customer_id | uuid | FK → customers |
| rego | text | unique |
| vin | text | |
| make | text | |
| model | text | |
| year | int | |
| odometer | int |
work_orders943 rows
| id | uuid | PK |
| job_number | text | unique |
| customer_id | uuid | FK |
| vehicle_id | uuid | FK |
| technician_id | uuid | FK → users |
| status | enum | |
| priority | enum | |
| booked_for | timestamptz | |
| total | numeric(10,2) |
invoices927 rows
| id | uuid | PK |
| number | text | unique |
| job_id | uuid | FK → work_orders |
| customer_id | uuid | FK |
| amount | numeric | |
| tax | numeric | |
| total | numeric | |
| status | enum | draft/sent/paid/overdue |
| xero_id | text | synced |
stock_items238 rows
| id | uuid | PK |
| sku | text | unique |
| name | text | |
| category | text | |
| supplier_id | uuid | FK |
| qty | int | |
| reorder_at | int | |
| cost | numeric | |
| price | numeric |
suppliers41 rows
| id | uuid | PK |
| name | text | |
| category | text | |
| contact | text | |
| text | ||
| phone | text |
Row-Level Security — what each role can see
Enforced at the database layer via Postgres RLS policies, independent of application code.
| Role | SELECT | INSERT / UPDATE / DELETE |
|---|---|---|
| customer | own customers row · own vehicles · own work_orders · own invoices | own customers (profile only) |
| technician | work_orders assigned to self · vehicles + customers linked to those jobs · stock_items | assigned work_orders (status, time, notes) |
| advisor | all customers, vehicles, work_orders, invoices, stock | all customers, vehicles, work_orders, invoices |
| admin | everything incl. users, audit_log, suppliers | everything (full operational control) |
Migration plan. CSV exports from MechanicDesk are mapped 1:1 to the schema above. Identity columns are remapped to UUIDs and historical job→invoice relationships are preserved.