Mota — Data Design

SDLC phase: Design (Chapter 4.4)
Purpose: Entity model, schema, data dictionary, and retention rules.

---

1. Entity-relationship diagram

erDiagram
    USER ||--o{ VEHICLE : owns
    USER ||--o{ DEVICE : registers
    VEHICLE ||--o{ RECORD : has
    VEHICLE ||--o{ DUE_OVERRIDE : has
    VEHICLE }o--|| VEHICLE_MODEL : "is a"
    VEHICLE ||--o{ NOTIFICATION : receives
    RECORD ||--o{ ATTACHMENT : may_have
    RECORD }o--o{ SERVICE_KEY : satisfies

    VEHICLE_MODEL ||--o{ SERVICE_INTERVAL : defines

    GARAGE ||--o{ GARAGE_SERVICE : offers
    GARAGE ||--o{ PRICE_ENTRY : submits
    GARAGE ||--o{ LEAD : receives
    GARAGE ||--o{ INVOICE : billed
    GARAGE ||--o{ GARAGE_USER : "accessed by"

    USER ||--o{ LEAD : initiates
    VEHICLE ||--o{ LEAD : concerns
    PART_CATEGORY ||--o{ PRICE_ENTRY : classifies
    INVOICE ||--o{ LEAD : bills

    USER {
        uuid id PK
        text phone UK
        text display_name
        timestamptz created_at
        timestamptz deleted_at
    }
    VEHICLE {
        uuid id PK
        uuid user_id FK
        uuid model_id FK
        text registration
        int year
        int current_odometer
        timestamptz odometer_updated_at
        date licence_expiry
        date insurance_expiry
        date acquired_on
        text status
    }
    VEHICLE_MODEL {
        uuid id PK
        text make
        text model
        int year_from
        int year_to
        text transmission
        text fuel_type
    }
    SERVICE_INTERVAL {
        uuid id PK
        uuid model_id FK
        text key
        text label
        int km_interval
        int month_interval
        int severity
    }
    RECORD {
        uuid id PK
        uuid vehicle_id FK
        uuid client_id UK
        text type
        date occurred_on
        int odometer
        numeric cost_usd
        uuid garage_id FK
        text notes
        timestamptz deleted_at
    }
    PRICE_ENTRY {
        uuid id PK
        uuid garage_id FK
        uuid category_id FK
        numeric amount_usd
        text unit
        date as_of
        boolean is_sponsored
    }
    LEAD {
        uuid id PK
        uuid user_id FK
        uuid vehicle_id FK
        uuid garage_id FK
        text service_key
        text status
        timestamptz contact_released_at
        numeric fee_usd
    }

---

2. Data dictionary

USER

Column	Type	Null	Notes
id	uuid	no	PK, from Supabase Auth
phone	text	no	Unique. E.164 format. PII — restricted access
display_name	text	yes	Optional; asked lazily, not at signup
created_at	timestamptz	no
deleted_at	timestamptz	yes	Soft delete; hard-purge after 30 days

No email, no address, no date of birth. Contact only what a feature requires (CDPA data minimisation).

VEHICLE

Column	Type	Null	Notes
id	uuid	no	PK
user_id	uuid	no	FK → USER, RLS anchor
model_id	uuid	yes	FK → VEHICLE_MODEL; null when free-text used
registration	text	yes	Normalised uppercase, no spaces
year	int	no	Model year
current_odometer	int	no	km. Monotonic per vehicle except via explicit correction
odometer_updated_at	timestamptz	no	Drives staleness detection
licence_expiry	date	yes	User-entered; no ZINARA integration
insurance_expiry	date	yes	User-entered
acquired_on	date	yes	Baseline for intervals when no service records exist
status	text	no	active / archived

RECORD

Column	Type	Null	Notes
id	uuid	no	PK (server-generated)
client_id	uuid	no	Unique. Generated on device. Enforces idempotent sync
vehicle_id	uuid	no	FK
type	text	no	service / fuel / expense
occurred_on	date	no
odometer	int	yes	km at time of event
cost_usd	numeric(10,2)	yes	USD is the accounting unit
cost_zig	numeric(12,2)	yes	Display only, captured at entry for reference
garage_id	uuid	yes	FK, nullable
notes	text	yes	Max 500 chars
deleted_at	timestamptz	yes	Soft delete with 30-day undo

Constraint: UNIQUE (client_id). On replay, an insert conflict is a no-op returning 200.

SERVICE_INTERVAL / SERVICE_KEY

The join between a free-text record and a structured interval is deliberate: RECORD.service_keys is a text array of interval keys ({cvt_fluid, engine_oil}). This allows one service visit to satisfy multiple intervals without a join table explosion.

PRICE_ENTRY

Column	Type	Null	Notes
id	uuid	no	PK
garage_id	uuid	no	FK → GARAGE
category_id	uuid	no	FK → PART_CATEGORY
amount_usd	numeric(10,2)	no	Canonical currency
unit	text	no	per_service / per_litre / per_tyre
as_of	date	no	Required. No price is ever displayed without it
is_sponsored	boolean	no	Default false. Must be surfaced in UI

Constraint: UNIQUE (garage_id, category_id, as_of) — one price per garage per category per day.

PART_CATEGORY

Column	Type	Null	Notes
id	uuid	no	PK
key	text	no	Unique. e.g. cvt_fluid_service
label	text	no	Display name
unit	text	no	Default unit for this category
service_key	text	yes	Links to SERVICE_INTERVAL.key — makes the price index triggerable from a reminder
is_active	boolean	no	Retire categories without deleting history

GARAGE

Column	Type	Null	Notes
id	uuid	no	PK
name	text	no	Trading name
suburb	text	no	Harare suburb; used for "near me"
phone	text	no	Public business number — not PII
services	text[]	no	Service keys offered
verification_status	text	no	listed / claimed / reviewed / suspended — three tiers adopted from ZWPRICE. See note below.
subscription_tier	text	no	free / partner / chain
created_at	timestamptz	no

Why three tiers, not a boolean. A single verified flag conflates two very different things: a business that Mota added from public listings, and a business that signed up and proved its identity. ZWPRICE distinguishes Listed / Claimed / Reviewed and states that none is a guarantee — which is both more honest and more useful. A consumer browsing garages should be able to see that a listing exists but its owner has not engaged. Only reviewed garages appear at the top of results, and suspended is never shown.

Only claimed and reviewed garages can receive leads. A listed garage is a directory entry, not a partner.

LEAD

Column	Type	Null	Notes
id	uuid	no	PK
user_id	uuid	no	FK → USER
vehicle_id	uuid	no	FK → VEHICLE
garage_id	uuid	no	FK → GARAGE
service_key	text	yes	What was requested
status	text	no	requested / booked / lost / no_response
contact_released_at	timestamptz	yes	Set only when user explicitly requests the booking. Gates PII access
fee_usd	numeric(8,2)	yes	Set when status becomes booked. Billable only on booked
created_at	timestamptz	no

INVOICE

Column	Type	Null	Notes
id	uuid	no	PK
garage_id	uuid	no	FK → GARAGE
period_start / period_end	date	no	Billing period
amount_usd	numeric(10,2)	no	Sum of lead fees + subscription
status	text	no	draft / sent / paid / overdue
payment_method	text	yes	paynow / cash — cash is a first-class state
payment_ref	text	yes	Paynow transaction reference
paid_at	timestamptz	yes

Supporting tables

Table	Purpose	Key columns
DEVICE	Push token registry per user	user_id, push_token, platform, last_seen_at
NOTIFICATION	Reminder audit trail + in-app inbox	vehicle_id, service_key, stage, sent_at, read_at, actioned_at
ATTACHMENT	Receipt photos	record_id, storage_path, mime_type, size_bytes
DUE_OVERRIDE	User-supplied interval overrides (FR-DUE-04)	vehicle_id, service_key, km_interval, month_interval, note
GARAGE_USER	Garage console accounts	garage_id, user_id, role
GARAGE_SERVICE	Which service keys a garage offers	garage_id, service_key, price_hint_usd

Note on NOTIFICATION.actioned_at: this is the retention metric that matters (see 09-testing-strategy.md §7). read_at measures curiosity; actioned_at measures value.

LEAD

Column	Type	Null	Notes
status	text	no	requested / booked / lost / no_response
contact_released_at	timestamptz	yes	Set only when user explicitly requests the booking. Gates PII access
fee_usd	numeric(8,2)	yes	Set when status becomes booked. Billable only on booked

---

3. Row-level security policies

Isolation is enforced in the database, not the client. These are the policies that must exist before launch.

Table	Policy	Rule
vehicle	owner-only	user_id = auth.uid()
record	owner-only	via vehicle.user_id = auth.uid()
notification	owner-only	via vehicle.user_id = auth.uid()
lead (consumer read)	owner-only	user_id = auth.uid()
lead (garage read)	garage-scoped	garage_id IN (select garage_id from garage_user where user_id = auth.uid())
lead.contact_released_at	gated	Garage cannot read user phone unless contact_released_at IS NOT NULL
garage	public read, admin write	Verified garages readable by all; writes restricted
price_entry	garage write, public read	Garage writes only its own garage_id
invoice	garage-scoped	Same scoping as lead

PII isolation test (must pass before launch): authenticated as user A, request user B's vehicle by ID → 403/empty. Authenticated as garage X, request garage Y's leads → empty. Authenticated as garage X without contact_released_at, request lead phone → null.

---

4. Reference data

The interval table is the product's domain knowledge and its most underrated asset.

Aspect	Plan
Initial coverage	40 highest-volume models in the Zim fleet (used Japanese imports dominate)
Per model	6–10 intervals: engine oil, oil filter, air filter, cabin filter, brake fluid, coolant, transmission/CVT fluid, spark plugs, timing belt/chain, brake pads (inspect)
Source	Manufacturer schedules, adapted to Zimbabwean conditions
Adaptation	Severe-service intervals applied by default: dusty environment, short trips, heavy load. Report both "normal" and "severe" where they differ materially.
Governance	Admin-editable (FR-ADM-03). Every edit is versioned so a change can be traced and, if wrong, reverted.

This table is defensible in a way features are not. A competitor can copy the UI in a week; they cannot copy a correctly adapted interval table for 40 models without doing the same research.

---

5. Data volume & growth

Entity	1,000 users	10,000 users	Notes
Vehicles	~1,200	~12,000	1.2 per user
Records	~15,000	~180,000	~15/year/vehicle
Notifications	~30,000	~360,000	2 per due item; prune after 90 days
Leads	~600	~9,000	Low volume, long retention
Prices	~2,000	~20,000	Small; retained indefinitely for trend data

At 10,000 users this is comfortably under 1GB — within the free/low tier of Supabase. Storage cost is not a constraint at any plausible v1 scale.

---

6. Retention & deletion

Data	Retention	Basis
Vehicle + records	Life of account	Core product value; a deleted history is not recoverable
Soft-deleted records	30 days, then hard delete	Undo window
Notifications	90 days then purge	No analytical value after action
Leads	3 years	Billing reconciliation and dispute resolution
Price entries	Indefinite (anonymised after garage deletion)	Trend data is the long-term asset
Attachments	Life of record
Audit log (admin actions)	3 years	Accountability for data changes

Deletion path (CDPA data-subject right)

1. User requests deletion in-app.
2. Account is soft-deleted immediately; app access revoked.
3. Hard purge of PII within 30 days. Records that belong to a garage's billing history are retained in anonymised form (user_id → null, vehicle data aggregated).
4. Deletion is recorded in the audit log with a timestamp and request reference.

Do not build a deletion flow that only hides data. The CDPA right is to erasure, not to concealment.

---

7. Migration strategy

Rule	Reason
Every migration is versioned and has a down path	NFR-MAINT-03
Reference data seeded via migration, not manual entry	Reproducible environments
Schema changes are additive on the hot path	Avoid locking tables during a live app session
Any change to service_interval triggers a recompute job for affected vehicles	A stale due calculation is a trust failure