TrafficGrid — Data Model & System Design Reference¶

Version 1.0 | 2026

This document describes the complete data model for the TrafficGrid platform — a unified traffic and vehicle management system consolidating vehicle records, traffic offences, parking fines, and payments across the Zimbabwe Republic Police (ZRP), ZINARA, VTS, City Parking, and City Council.

It is intended as the authoritative reference for developers, system architects, and technical stakeholders. It covers all database entities, their attributes, relationships, constraints, indexes, and design decisions including deliberate denormalization choices.

Table of Contents¶

1. System Overview¶

TrafficGrid is built as a modular monolith using Java 25, Spring Boot 4, PostgreSQL 18 as the primary database, and Redis for caching and token management. The architecture is designed to be extractable into microservices as the system scales, but begins as a single deployable unit to reduce operational complexity during the initial build phase.

1.1 Stakeholders & Integrations¶

Organisation	Role	Integration Status
ZRP (ZIMBABWE REPUBLIC POLICE)	Issues traffic offences and spot fines. Officers log fines via mobile.	Manual entry — no existing digital system
ZINARA	Source of vehicle licence and registration data. Triggers renewal reminders.	API integration planned
VTS (Vehicle Theft Squad)	Verifies VIN numbers and registration details. Falls under ZRP. Not involved in ownership.	API integration planned
CVR (Central Vehicle Registry)	The ownership registry: verifies that a citizen who claims a vehicle actually owns it (see §2.4).	Mocked inside vehicle-service — real integration pending
City Parking	Issues parking tickets and manages parking sessions.	API integration planned
City Council (Harare)	Issues council-related fines and violations.	Manual entry — no existing digital system
System Provider (Us)	Builds, owns, and administers the platform. Holds super-admin access.	Full administrative access
Expo push service	Receives push notifications from notification-service and relays them to the citizen app on each platform's delivery network.	Integrated — needs the app's EAS project
Google Firebase Cloud Messaging (FCM)	Delivers Android push notifications; Expo hands every Android message to FCM. Needs a Firebase project, its `google-services.json` in the app build and an FCM v1 service-account key uploaded to Expo. No Firebase SDK is used in the app or the backend.	Setup pending
Apple Push Notification service (APNs)	Delivers iOS push notifications, again through Expo. Needs an Apple developer account and an APNs key uploaded to Expo.	Deferred — Android first

Out of Scope (Phase 1): ZIMRA (vehicle import duties), foreign vehicle registration, insurance provider API integrations.

1.2 Schema Ownership¶

Service	Owns Schemas / Tables
auth-service	users, organisations
vehicle-service	vehicles, citizen_vehicles, vehicle_documents, mock_cvr_verifications (until the real CVR is integrated)
payments-service	fines, fine_disputes, fine_dispute_evidence, parking_tickets (endpoints planned), payments, payment_items (planned)
notifications-service	notifications, notification_preferences, device_tokens
audit-service	audit_logs
system-parameters	system_parameters schema consisting of currencies, organizations, fine categories (levels), offences, notification_types and payment methods tables

fines-service is retired. It was planned as a dedicated service for fines and parking tickets but was never built. payments-service now owns the fines and parking_tickets tables permanently, alongside payments and payment_items. Fines and the payments that settle them share one service and one schema, so a payment can mark its fines PAID without a cross-service call. The fine_categories reference table is unchanged: it stays in system-parameters-service, and payments-service reads it via OpenFeign when issuing a fine. See §4.11 for the rationale.

1.3 User Roles¶

Role	Description
`CITIZEN`	Self-registered member of the public. Can view own vehicles and fines, pay online, receive renewal reminders.
`ZRP_OFFICER`	Zimbabwe Republic Police officer. Can issue fines and search vehicle history via mobile interface.
`COUNCIL_OFFICER`	City Council officer. Can issue council fines and query vehicles.
`PARKING_OFFICER`	City Parking officer. Can manage parking tickets.
`AUTHORITY_ADMIN`	Organisation-level administrator. Can view reports scoped to their organisation.
`SUPER_ADMIN`	Full system access. Can create officer accounts, manage organisations, view all audit logs.

Note: AUTHORITY_ADMIN accounts are created by SUPER_ADMIN only. OFFICER accounts are created by AUTHORITY_ADMIN only. Only CITIZENS can self-register.

2. Entity Reference¶

All primary keys are UUIDs rather than auto-increment integers to prevent enumeration attacks and to support future distributed deployment.

2.1 `users`¶

Owned By: auth-service

The central entity of the system. Represents all human actors — citizens, officers of various types, and administrators. Citizens self-register. Officer accounts are created by a SUPER_ADMIN.

Column	Type	Constraints	Notes
id	UUID	PK	Auto-generated.
full_name	VARCHAR(255)	NOT NULL	Full legal name.
email	VARCHAR(255)	NOT NULL, UNIQUE	Login credential. Indexed.
phone_number	VARCHAR(50)	NOT NULL, UNIQUE	Used for SMS notifications. Indexed.
national_id	VARCHAR(100)	NOT NULL, UNIQUE	Prevents duplicate registrations. Indexed.
password_hash	VARCHAR(255)	NOT NULL	BCrypt hash. Plain text never stored.
role	ENUM	NOT NULL	See UserRole below. Domain-local to auth-service.
organisation_id	UUID	organisations.id, NULLABLE	NULL for citizens. References officer's organisation. Set from `organizationId` on user creation and carried in the access token's `organizationId` claim.
is_active	BOOLEAN	NOT NULL, DEFAULT true	Soft delete flag. Deactivated users cannot log in or refresh.
deactivation_reason	VARCHAR(500)	NULLABLE	Why the account was deactivated. Cleared on reactivation.
password_changed_at	TIMESTAMP	NULLABLE	When the password was last reset (whole seconds). Refresh tokens issued at or before it are refused.
created_by	UUID	user.id	Admin who created this account. NULL for self-registered citizens.
created_at	TIMESTAMP	NOT NULL	Set on insert via @PrePersist.
updated_at	TIMESTAMP	NOT NULL	Updated on every change via @PreUpdate.

Indexes:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_national_id ON users(national_id);
CREATE INDEX idx_users_phone_number ON users(phone_number);

Enum: `UserRole`¶

Value	Description
`CITIZEN`	Self-registered member of the public.
`ZRP_OFFICER`	Zimbabwe Republic Police officer.
`COUNCIL_OFFICER`	City Council officer.
`PARKING_OFFICER`	City Parking officer.
`AUTHORITY_ADMIN`	Organisation-level administrator.
`SUPER_ADMIN`	Full system access.

`password_reset_tokens`¶

One-time password-reset links, also owned by auth-service: from /auth/forgot-password, and for staff accounts created without a password.

Column	Type	Constraints	Notes
id	UUID	PK	Auto-generated.
user_id	UUID	NOT NULL, users.id, ON DELETE CASCADE	Indexed.
token_hash	VARCHAR(64)	NOT NULL, UNIQUE	SHA-256 of the token. The token itself is never stored.
expires_at	TIMESTAMP	NOT NULL	One hour for a reset; three days for a new account's link.
used_at	TIMESTAMP	NULLABLE	Set when used. Using one link spends every other open link for the user.
created_at	TIMESTAMP	NOT NULL

2.2 organisations¶

Owned by system-parameters-service

Represents the government bodies and agencies that interact with TrafficGrid. Officers belong to an organisation. Fine levels (fine categories), and the offences under them, are scoped by issuing organisation.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`name`	`VARCHAR(255)`	NOT NULL	Full name. E.g. Zimbabwe Republic Police.
`short_name`	`VARCHAR(50)`	NOT NULL	Abbreviated name. E.g. ZRP.
`type`	`ENUM`	NOT NULL	See `OrganisationType` below.
`is_active`	`BOOLEAN`	NOT NULL, DEFAULT true	Soft delete flag.
`deactivation_reason`	`VARCHAR(500)`	NULLABLE	Why it was deactivated. Cleared on activate.
`created_at`	`TIMESTAMP`	NOT NULL	Set on insert.
`updated_at`	`TIMESTAMP`	NOT NULL

The physical table is system_parameters.organizations. Fines snapshot short_name at issue time (fines.issuing_organisation_name).

Enum: `OrganisationType`¶

Value	Description
`LAW_ENFORCEMENT`	E.g. Zimbabwe Republic Police.
`COUNCIL`	E.g. Harare City Council.
`PARKING`	E.g. City Parking.
`REGISTRY`	E.g. ZINARA, VTS.
`SYSTEM_PROVIDER`	ShiftFocus Technologies.

2.3 `vehicles`¶

Owned by: vehicle-service

Represents registered vehicles. A vehicle exists independently of any citizen link — it is searchable by officers even if no citizen has linked it. Verification of the registration against VTS and ZINARA records is planned.

is_verified is about the vehicle's registration, not about who owns it. A citizen's ownership claim is verified separately, by CVR, and recorded on the link as citizen_vehicles.verification_status (§2.4). The two are unrelated: a vehicle can be unverified and have a verified owner, or the reverse.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`number_plate`	`VARCHAR(20)`	NOT NULL, UNIQUE	Primary search field for officers in the field. Indexed.
`vin`	`VARCHAR(50)`	UNIQUE, NULLABLE	Vehicle Identification Number.
`engine_number`	`VARCHAR(50)`	NULLABLE
`make`	`VARCHAR(100)`	NULLABLE	E.g. Toyota, Ford.
`model`	`VARCHAR(100)`	NULLABLE	E.g. Hilux, Ranger.
`year`	`INTEGER`	NULLABLE	Manufacturing year.
`color`	`VARCHAR(50)`	NULLABLE	Searchable (`GET /vehicles?color=`).
`vehicle_type`	`ENUM`	NOT NULL	See `VehicleType` below.
`is_verified`	`BOOLEAN`	NOT NULL, DEFAULT false	Set to true once the registration is verified against VTS/ZINARA API. Not link (ownership) verification.
`created_at`	`TIMESTAMP`	NOT NULL
`updated_at`	`TIMESTAMP`	NOT NULL

Indexes:

CREATE UNIQUE INDEX idx_vehicles_number_plate ON vehicles(number_plate);
CREATE UNIQUE INDEX idx_vehicles_vin ON vehicles(vin);

Enum: `VehicleType`¶

***Owned by: `vehicle-service

Value	Description
`SEDAN`	Standard passenger car.
`HATCHBACK`	Hatchback passenger car.
`SUV`	Sport utility vehicle.
`TRUCK`	Light or heavy commercial truck.
`MOTORCYCLE`	Two-wheeled motor vehicle.
`BUS`	Passenger bus or minibus.
`OTHER`	Any vehicle type not covered above.

2.4 `citizen_vehicles`¶

***Owned by: `vehicle-service

A citizen's claim to own a vehicle. Linking a vehicle creates a claim, which the Central Vehicle Registry (CVR) verifies. A citizen can claim several vehicles, but a vehicle has at most one owner: the citizen holding its one VERIFIED link. While a vehicle has a pending or verified claim, nobody else can claim it. The is_primary flag designates which vehicle appears first on the citizen's dashboard.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`citizen_id`	`UUID`	NOT NULL, FK → users	Must reference a user with role `CITIZEN`.
`vehicle_id`	`UUID`	NOT NULL, FK → vehicles	At most one `PENDING_VERIFICATION` or `VERIFIED` row per vehicle (partial unique index below).
`is_primary`	`BOOLEAN`	NOT NULL, DEFAULT false	Only one vehicle per citizen should have this set to true.
`linked_at`	`TIMESTAMP`	NOT NULL	When the citizen claimed this vehicle.
`verification_status`	`VARCHAR(30)`	NOT NULL, CHECK	See `VerificationStatus` below. V6.
`cvr_request_id`	`UUID`	NULLABLE	CVR's id for the verification request. NULL until CVR has accepted the claim (if CVR was unreachable when the claim was made, the poller submits it later). V6.
`verified_at`	`TIMESTAMP`	NULLABLE	When CVR verified the claim. V6.

Indexes and constraints (V6):

-- One active claim per vehicle, whoever holds it.
CREATE UNIQUE INDEX uq_citizen_vehicles_active_claim
    ON citizen_vehicles (vehicle_id)
    WHERE verification_status IN ('PENDING_VERIFICATION', 'VERIFIED');

-- Serves the poller.
CREATE INDEX idx_citizen_vehicles_pending
    ON citizen_vehicles (verification_status)
    WHERE verification_status = 'PENDING_VERIFICATION';

V6 dropped the old (citizen_id, vehicle_id) unique constraint, because rejected rows are kept for audit and a citizen whose claim was rejected may claim the vehicle again. Every link that existed before V6 was migrated as VERIFIED, with verified_at set to linked_at.

Lifecycle:

A citizen claims a vehicle by plate. The row is inserted as PENDING_VERIFICATION and submitted to CVR, storing cvr_request_id. If another citizen already holds a pending or verified claim, the claim is refused with 409 (the partial unique index also catches a race between two claims).
vehicle-service's CvrVerificationPoller (every trafficgrid.cvr.poll-interval, default 30 seconds) asks CVR about each pending row, one transaction per row. CVR's answer moves it to VERIFIED (setting verified_at) or REJECTED, and publishes VERIFY_CITIZEN_VEHICLE or REJECT_CITIZEN_VEHICLE. A CVR error leaves it pending for the next run.
Unlinking deletes a pending or verified row and frees the vehicle. A REJECTED row is never deleted and never shown to the citizen.

Only VERIFIED rows grant anything: access to the vehicle's documents, the owner lookup (§3), and the vehicles payments-service uses to scope a citizen's fines and disputes. The citizen's own vehicle list returns pending and verified rows, so the app can show a pending one as "Verifying…".

Enum: `VerificationStatus`¶

Owned by: vehicle-service

Value	Description
`PENDING_VERIFICATION`	Claimed; waiting for CVR. Reserves the vehicle.
`VERIFIED`	CVR confirmed the citizen owns the vehicle. Reserves the vehicle.
`REJECTED`	CVR refused the claim. Kept for audit; frees the vehicle.

`mock_cvr_verifications`¶

Requests made to the mock CVR that vehicle-service serves at /api/v1/mock/cvr/verifications (see API Contracts.md §6.4.1), also owned by vehicle-service (V7). Stored so a pending verification survives a restart. The outcome is not stored: it is decided when the request is read, once trafficgrid.cvr.verification-delay (default 2 minutes) has passed, rejecting plates whose last character is 0 and verifying all others. The table is dropped once the real CVR is integrated.

Column	Type	Constraints	Notes
id	UUID	PK, DEFAULT `gen_random_uuid()`	The request id returned to the caller and stored as `citizen_vehicles.cvr_request_id`.
number_plate	VARCHAR(20)	NOT NULL	Trimmed and in upper case.
citizen_id	UUID	NOT NULL	The claiming citizen.
requested_at	TIMESTAMP	NOT NULL, DEFAULT `CURRENT_TIMESTAMP`	The delay is counted from here.

2.5 `vehicle_documents`¶

Owned by: vehicle-service Tracks expiry dates for regulatory documents associated with a vehicle. Citizens can self-declare documents manually. Integration with ZINARA will allow automatic population and verification. The notification system uses this table to send renewal reminders.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`vehicle_id`	`UUID`	NOT NULL, FK → vehicles
`document_type`	`ENUM`	NOT NULL	See `DocumentType` below.
`expiry_date`	`DATE`	NOT NULL	Used to trigger renewal reminders.
`is_self_declared`	`BOOLEAN`	NOT NULL	`true` if manually entered by citizen. Kept for older clients; always agrees with `source`.
`source`	`VARCHAR(20)`	NOT NULL, DEFAULT `SELF_DECLARED`	`SELF_DECLARED` (added by a citizen) or `ZINARA` (added by an officer from ZINARA's records). Backfilled from `is_self_declared`.
`created_at`	`TIMESTAMP`	NOT NULL
`updated_at`	`TIMESTAMP`	NOT NULL

A document's expiryStatus (VALID, EXPIRING within 30 days, EXPIRED) is computed on read and not stored.

Enum: `DocumentType`¶

***Owned by: `vehicle-service

Value	Description
`VEHICLE_LICENSE`	Annual vehicle licence disc issued by ZINARA.
`INSURANCE`	Third-party vehicle insurance certificate.
`ZBC_LICENSE`	Zimbabwe Broadcasting Corporation licence fee.
`VEHICLE_FITNESS`	Vehicle fitness certificate issued by VTS. Required annually for vehicles over a certain age.

2.6 `fine_categories` (levels)¶

Owned by: system-parameters-service Reference table of fine levels: price bands that each belong to one issuing organisation, e.g. ZRP's Level 1 ($5), Level 2 ($15), Level 3 ($30) and Escalated. "Level" is the user-facing name; the table, the fine-categories API resource and fines.fine_category_id keep the fine category name. The offences officers choose from sit under the levels (§2.6.1) and take their price, currency and organisation from them. Maintained by a Super Admin. Amounts here are the current standard: fines store their own amount snapshot, so a price change only affects fines issued afterwards.

A level marked requires_court (ZRP's Escalated) has no amount: the court sets the penalty. Every other level must have one. Levels are never deleted, only deactivated.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated, or fixed for seeded levels (below).
`code`	`VARCHAR(50)`	NOT NULL, UNIQUE per organisation	Machine-readable code. E.g. `LEVEL_1`, `ESCALATED`. Not editable.
`name`	`VARCHAR(255)`	NOT NULL, UNIQUE per organisation	Human-readable name. E.g. Level 1. Each organisation names its own levels, so ZRP and a council can both have a "Level 1".
`description`	`TEXT`	NULLABLE	What kind of offence the level covers.
`amount`	`INTEGER`	NULLABLE	Current amount in cents. NULL exactly when `requires_court` is true (check constraint). Copied onto fines at issue time.
`currency`	`VARCHAR(5)`	NOT NULL, DEFAULT `USD`	Code of an active currency (checked on create and edit). Copied onto fines at issue time.
`requires_court`	`BOOLEAN`	NOT NULL, DEFAULT false	A court level: fines under it go to court, which sets the penalty. Not editable. Code checks this flag, never the code `ESCALATED`.
`issuing_organization`	`UUID`	NOT NULL, FK → organizations	The organisation that owns the level. Only its officers may issue fines under it, and only its users (and a Super Admin) can read it. Not editable.
`is_active`	`BOOLEAN`	NOT NULL, DEFAULT true	Inactive levels cannot be used to issue new fines. Deactivating a level does not change its offences' own `is_active`.
`deactivation_reason`	`VARCHAR(500)`	NULLABLE	Why it was deactivated. Cleared on activate.
`created_at`	`TIMESTAMP`	NOT NULL
`updated_at`	`TIMESTAMP`	NOT NULL

Constraints:

CONSTRAINT ck_fine_categories_court_amount
    CHECK ((requires_court AND amount IS NULL) OR (NOT requires_court AND amount IS NOT NULL))
CONSTRAINT uk_fine_categories_organization_code UNIQUE (issuing_organization, code)
CONSTRAINT uk_fine_categories_organization_name UNIQUE (issuing_organization, name)

V3 created this table without name; V11 adds name (backfilled from the description, else the code), currency and deactivation_reason. V12 turns categories into levels: it deletes every existing row (they were one category per offence, from before levels existed; the system is pre-production, so they are not converted and issued fines keep their snapshots), makes amount nullable, adds requires_court and the check constraint, and replaces the system-wide unique constraints and indexes on code and name with the per-organisation ones above. It drops those by looking them up, so it also runs on a database whose constraints Hibernate's ddl-auto: update had already named.

Seeded levels¶

V13 seeds ZRP's levels with fixed ids, so offence seeds, other systems and the integration suite can refer to them. Organisation ids are random, so the seed finds ZRP by short_name. It is idempotent (ON CONFLICT DO NOTHING). No other organisation is seeded: a Super Admin creates a council's levels when it is onboarded.

`id`	`code`	`name`	`amount` (cents)	`currency`	`requires_court`
`f1000000-0000-4000-8000-000000000001`	`LEVEL_1`	Level 1	500	USD	false
`f1000000-0000-4000-8000-000000000002`	`LEVEL_2`	Level 2	1500	USD	false
`f1000000-0000-4000-8000-000000000003`	`LEVEL_3`	Level 3	3000	USD	false
`f1000000-0000-4000-8000-000000000004`	`ESCALATED`	Escalated	NULL	USD	true

2.6.1 `offences`¶

Owned by: system-parameters-service The offence catalogue. Each offence belongs to exactly one level and takes its price, currency and issuing organisation from it: it never stores its own, so two offences in the same level can never be priced differently. Officers pick a level, then an offence in it. Maintained by a Super Admin; reads follow the level's organisation.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated, or fixed for seeded offences.
`code`	`VARCHAR(50)`	NOT NULL, UNIQUE	Unique across every organisation, because integrations key on it. Not editable. The same offence under two organisations gets two codes, e.g. `ZRP_NO_SEATBELT` and `COH_NO_SEATBELT`.
`name`	`VARCHAR(255)`	NOT NULL	Human-readable name. E.g. Driving without a seatbelt.
`description`	`TEXT`	NULLABLE
`legal_reference`	`VARCHAR(255)`	NULLABLE	The statute or regulation, for officers to cite.
`fine_category_id`	`UUID`	NOT NULL, FK → fine_categories	The offence's level. Editable: moving an offence moves it to the new level's price.
`is_active`	`BOOLEAN`	NOT NULL, DEFAULT true	Inactive offences cannot be used to issue new fines. Independent of the level's flag.
`deactivation_reason`	`VARCHAR(500)`	NULLABLE	Why it was deactivated. Cleared on activate.
`created_at`	`TIMESTAMP`	NOT NULL
`updated_at`	`TIMESTAMP`	NOT NULL

There is no amount, currency or organisation column.

Constraints and indexes:

CONSTRAINT uk_offences_code UNIQUE (code)
CONSTRAINT fk_offences_fine_category
    FOREIGN KEY (fine_category_id) REFERENCES system_parameters.fine_categories (id)
CREATE INDEX idx_offences_fine_category ON system_parameters.offences (fine_category_id);

V14 creates the table. V15 seeds the court catch-all under ZRP's Escalated level, for court matters that are not in the catalogue (officers must then describe the offence in the fine's notes):

`id`	`code`	`name`	Level
`0ff00000-0000-4000-8000-000000000001`	`ZRP_OTHER_COURT_OFFENCE`	Other court offence	`f1000000-0000-4000-8000-000000000004` (ZRP Escalated)

Seeding offences¶

The offence catalogue is seeded by its own Flyway migration in system-parameters-service. Copy this template for each offence:

INSERT INTO system_parameters.offences
(id, code, name, description, legal_reference, fine_category_id, is_active, created_at, updated_at)
VALUES
    (gen_random_uuid(), 'ZRP_NO_SEATBELT', 'Driving without a seatbelt',
     'Driver or passenger not wearing a seatbelt.', NULL,
     'f1000000-0000-4000-8000-000000000001', true, NOW(), NOW()),
    (gen_random_uuid(), 'ZRP_NO_THIRD_PARTY_INSURANCE', 'No third-party insurance',
     'Driving a vehicle without valid third-party insurance.', NULL,
     'f1000000-0000-4000-8000-000000000003', true, NOW(), NOW())
ON CONFLICT (code) DO NOTHING;

fine_category_id is one of the seeded level ids above: Level 1 ...0001, Level 2 ...0002, Level 3 ...0003, Escalated ...0004.
code must be unique across every organisation, not just ZRP. Prefix it with the organisation (ZRP_) and use UPPER_SNAKE case. ON CONFLICT (code) DO NOTHING makes the migration safe to run again.
Give an offence a fixed UUID in place of gen_random_uuid() when other systems need to refer to it by id.

V16 seeds ZRP's catalogue this way: 85 offences from the National Schedule of Deposit Fines, each under the level the schedule gives it, with fixed ids 0ff00000-0000-4000-8000-000000000002 to ...000000000086 in code order (ZRP_LIC_001 to ZRP_LIC_031, ZRP_RTA_001 to ZRP_RTA_022, ZRP_MOV_001 to ZRP_MOV_015, ZRP_SIG_001 to ZRP_SIG_004, ZRP_EQU_001 to ZRP_EQU_008, ZRP_SPD_001 to ZRP_SPD_005). ZRP_SPD_001 (1 to 5 km/h over) is seeded inactive, because the schedule only cautions for it. Speeding more than 50 km/h over goes to court under "Other court offence". - Don't add an amount or currency: they come from the level. - Write names and descriptions in plain text, with no markup (e.g. "no third-party insurance", not LaTeX). Double any single quote (driver''s).

2.7 `fines`¶

Owned by: payments-service

Records of traffic and council offences issued against vehicles. Each fine has a unique human-readable reference number. The amount is copied from the fine category at the time of issue — this is a deliberate denormalization to preserve historical accuracy regardless of future category changes.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`reference_number`	`VARCHAR(50)`	NOT NULL, UNIQUE	Format: `TG-YYYY-NNNNN`. E.g. `TG-2026-00123`. Indexed.
`vehicle_id`	`UUID`	NOT NULL, FK → vehicles	The vehicle the fine was issued against. Indexed.
`fine_category_id`	`UUID`	NOT NULL, FK → fine_categories	The level (fine category) the fine was issued under. Plain UUID — `fine_categories` lives in system-parameters-service.
`issued_by`	`UUID`	NOT NULL, FK → users	The officer who issued the fine.
`issuing_organization`	`UUID`	NOT NULL	The issuing organisation. Snapshot for reporting.
`amount`	`INTEGER`	NOT NULL	In cents. Copied from the level's `fine_categories.amount` at issue time. Unaffected by future level changes.
`currency`	`VARCHAR(10)`	NOT NULL, DEFAULT `USD`	Copied from `fine_categories.currency` at issue time.
`status`	`ENUM`	NOT NULL, DEFAULT PENDING	See `FineStatus` below.
`status_change_reason`	`VARCHAR(500)`	NULLABLE	Reason given with the latest manual status change, or the note of a dispute decision. Cleared when a dispute is filed.
`location`	`TEXT`	NULLABLE	Where the offence occurred.
`notes`	`TEXT`	NULLABLE	Officer notes on the incident.
`due_date`	`TIMESTAMP`	NOT NULL	Default 30 days from issue. Used for overdue reminders.
`vehicle_number_plate`	`VARCHAR(50)`	NULLABLE	Snapshot of the vehicle at issue time (from vehicle-service). Searched ignoring spaces and case.
`vehicle_make`	`VARCHAR(100)`	NULLABLE	Snapshot.
`vehicle_model`	`VARCHAR(100)`	NULLABLE	Snapshot.
`fine_category_code`	`VARCHAR(100)`	NULLABLE	Snapshot of the level at issue time.
`fine_category_name`	`VARCHAR(255)`	NULLABLE	Snapshot.
`issued_by_name`	`VARCHAR(255)`	NULLABLE	The issuing officer's name at issue time.
`issuing_organisation_name`	`VARCHAR(100)`	NULLABLE	The issuing organisation's short name at issue time. NULL if the lookup failed.
`created_at`	`TIMESTAMP`	NOT NULL	When the fine was issued (returned as both `createdAt` and `issuedAt`).
`updated_at`	`TIMESTAMP`	NOT NULL

The snapshot columns (V13) are written once at issue time and never looked up on read; fines issued before V13 have them NULL.

Indexes:

CREATE INDEX idx_fines_vehicle_id ON fines(vehicle_id);
CREATE INDEX idx_fines_status ON fines(status);
CREATE UNIQUE INDEX idx_fines_reference_number ON fines(reference_number);
CREATE INDEX idx_fines_plate_normalised ON fines(lower(replace(vehicle_number_plate, ' ', '')));

Enum: `FineStatus`¶

Value	Description
`PENDING`	Default state. Fine has been issued and payment is outstanding.
`PAID`	Full payment received and confirmed.
`CLEARED`	Administratively cleared by an authorised admin (e.g. duplicate entry, wrongful issue).
`DISPUTED`	The fine has an `OPEN` dispute (see §2.7.1). Can't be paid or changed through the status change. Set only by filing a dispute, and left only by withdrawing it (back to `PENDING`) or deciding it (`CLEARED` if upheld, `PENDING` if rejected).
`CANCELLED`	Fine cancelled before payment. Officer or admin action.

2.7.1 `fine_disputes`¶

Owned by: payments-service

A citizen's challenge to one fine (V16). A fine has at most one dispute, ever: a withdrawn or decided dispute still counts, so the fine can't be disputed again.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`fine_id`	`UUID`	NOT NULL, UNIQUE, FK → fines	The disputed fine. `UNIQUE` enforces one dispute per fine.
`grounds`	`VARCHAR(30)`	NOT NULL, CHECK	See `DisputeGrounds` below.
`statement`	`VARCHAR(1000)`	NOT NULL	The citizen's account.
`status`	`VARCHAR(20)`	NOT NULL, CHECK	See `DisputeStatus` below.
`filed_by`	`UUID`	NULLABLE	The citizen who filed. NULL for disputes backfilled by V16.
`filed_by_name`	`VARCHAR(255)`	NULLABLE	The filer's name at filing time.
`filed_at`	`TIMESTAMP`	NOT NULL
`reviewed_by`	`UUID`	NULLABLE	The admin who decided. NULL until decided, and for a withdrawn dispute.
`reviewed_by_name`	`VARCHAR(255)`	NULLABLE	The reviewer's name at decision time.
`reviewing_organization`	`UUID`	NULLABLE	The organisation the reviewer acted for: always the fine's issuing organisation, a super admin included.
`reviewing_organisation_name`	`VARCHAR(100)`	NULLABLE	Snapshot of its short name, copied from the fine.
`decision_note`	`VARCHAR(500)`	NULLABLE	The reviewer's note, also copied to the fine's `status_change_reason`.
`decided_at`	`TIMESTAMP`	NULLABLE	When the dispute was decided or withdrawn.
`created_at`	`TIMESTAMP`
`updated_at`	`TIMESTAMP`

V16 backfilled an OPEN dispute for every fine already DISPUTED, so fines an admin had marked disputed by hand can be decided through the dispute flow: grounds OTHER, the fine's status_change_reason as the statement (or a fixed placeholder if it was empty), no filer, and filed_at set to the fine's updated_at.

Indexes:

CREATE INDEX idx_fine_disputes_status ON fine_disputes(status);

Enum: `DisputeGrounds`¶

Value	Description
`NOT_MY_VEHICLE`	The fine was recorded against the wrong vehicle.
`INCORRECT_DETAILS`	The location, time or offence is wrong.
`DID_NOT_COMMIT`	The citizen contests the offence itself.
`ALREADY_PAID`	Settled outside the platform.
`OTHER`	Any other reason. Also used for backfilled disputes.

Enum: `DisputeStatus`¶

Value	Description
`OPEN`	Awaiting review. The fine is `DISPUTED`.
`UPHELD`	Final. The fine was cleared.
`REJECTED`	Final. The fine is `PENDING` again, with at least 7 days from the decision to pay.
`WITHDRAWN`	Final. Withdrawn by the filer; the fine is `PENDING` again with its due date unchanged.

2.7.2 `fine_dispute_evidence`¶

Owned by: payments-service

Files the citizen described as evidence for a dispute (V16), at most 3 per dispute (checked by the API). Only the details are kept: nothing is uploaded yet.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`dispute_id`	`UUID`	NOT NULL, FK → fine_disputes ON DELETE CASCADE	Indexed.
`file_name`	`VARCHAR(255)`	NOT NULL
`content_type`	`VARCHAR(100)`	NOT NULL	`image/jpeg`, `image/png` or `application/pdf` (checked by the API).
`size_bytes`	`BIGINT`	NOT NULL	At most 5 MB (checked by the API).
`storage_key`	`VARCHAR(255)`	NOT NULL	A placeholder, `unstored/<uuid>`, until file storage exists.

2.8 `parking_tickets`¶

Owned by: payments-service

⚠️ Status: Planned — pending City Parking API integration specification.

Parking tickets are managed separately from traffic fines because they originate from City Parking's existing system and follow a different operational process. They share the payments infrastructure through the payment_items bridge table.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`reference_number`	`VARCHAR(50)`	NOT NULL, UNIQUE	Format: `PKT-YYYY-NNNNN`. E.g. `PKT-2026-00456`.
`vehicle_id`	`UUID`	NOT NULL, FK → vehicles
`issued_by`	`UUID`	NOT NULL, FK → users	The parking officer.
`location`	`TEXT`	NOT NULL	Parking zone or address.
`amount`	`INTEGER`	NOT NULL	In cents.
`status`	`ENUM`	NOT NULL, DEFAULT PENDING	See `ParkingTicketStatus` below.
`external_reference`	`TEXT`	NULLABLE	Reference number from City Parking's own system for reconciliation.
`due_date`	`DATE`	NOT NULL
`issued_at`	`TIMESTAMP`	NOT NULL
`updated_at`	`TIMESTAMP`	NOT NULL

Enum: `ParkingTicketStatus`¶

Value	Description
`PENDING`	Ticket issued, payment outstanding.
`PAID`	Payment received.
`CLEARED`	Administratively cleared.
`CANCELLED`	Ticket cancelled.

2.9 `payments`¶

Owned by: payments-service

Records payment transactions. A single payment can cover multiple fines and/or parking tickets through the payment_items bridge table. The idempotency_key prevents duplicate processing — if the same payment request arrives twice due to a network retry, the second request is safely ignored.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`reference_number`	`VARCHAR(50)`	NOT NULL, UNIQUE	Format: `TGPAY-YYYY-NNNNN`. E.g. `TGPAY-2026-00789`.
`paid_by`	`UUID`	NULLABLE	The user who paid (`ECOCASH`, `BANK_TRANSFER`). NULL for `CASH`: the payer is a walk-in person with no account. V14 cleared the vehicle ids earlier builds stored here for cash payments.
`paid_by_name`	`VARCHAR(255)`	NULLABLE	The payer's name at payment time (the caller's name, or the walk-in payer's for `CASH`).
`payment_method`	`ENUM`	NOT NULL	See `PaymentMethod` below.
`amount`	`INTEGER`	NOT NULL	Total in cents. Equals the sum of `payment_items` amounts.
`currency`	`VARCHAR(10)`	NOT NULL, DEFAULT `USD`	The items' currency; every item in a payment shares it.
`status`	`ENUM`	NOT NULL	See `PaymentStatus` below.
`provider_reference`	`TEXT`	NULLABLE	Transaction reference from EcoCash or bank. Used for reconciliation.
`recorded_by`	`UUID`	NULLABLE	Set only for CASH payments logged by an officer. NULL for online payments.
`recorded_by_name`	`VARCHAR(255)`	NULLABLE	The recording officer's name at payment time.
`payer_name`	`VARCHAR(255)`	NULLABLE	Payer contact as given (`details.payer`).
`payer_phone`	`VARCHAR(50)`	NULLABLE	Required for `ECOCASH`.
`provider_transaction_id`	`VARCHAR(255)`	NULLABLE	From the provider callback.
`provider_status_message`	`VARCHAR(500)`	NULLABLE	From the provider callback.
`provider_callback_at`	`TIMESTAMP`	NULLABLE	When the provider called back.
`cash_receipt_number`	`VARCHAR(100)`	NULLABLE	`CASH` only. Given by the officer, or `TGRCT-YYYY-NNNNN` from `cash_receipt_seq`.
`cash_receipt_location`	`VARCHAR(255)`	NULLABLE	`CASH` only.
`bank_name`	`VARCHAR(255)`	NULLABLE	`BANK_TRANSFER` only (required).
`bank_account_reference`	`VARCHAR(255)`	NULLABLE	`BANK_TRANSFER` only (required).
`bank_proof_reference`	`VARCHAR(255)`	NULLABLE	`BANK_TRANSFER` only.
`idempotency_key`	`TEXT`	NOT NULL, UNIQUE	Client-generated key to prevent duplicate payment processing on retries.
`paid_at`	`TIMESTAMP`	NULLABLE	Set when payment status becomes SUCCESS.
`created_at`	`TIMESTAMP`	NOT NULL
`updated_at`	`TIMESTAMP`	NOT NULL

The name, contact, provider, cash and bank columns were added by V14. Numbers come from sequences in the payments_service schema: payment_reference_seq (payment references), cash_receipt_seq (cash receipt numbers, V14) and fine_reference_seq (fine references).

Enum: `PaymentMethod`¶

Value	Description
`ECOCASH`	EcoCash mobile money. Primary online payment method.
`BANK_TRANSFER`	Direct bank transfer. Requires manual reconciliation.
`CASH`	Cash payment recorded by an officer at a payment point. `recorded_by` is set.

Enum: `PaymentStatus`¶

Value	Description
`PENDING`	Payment initiated but not yet confirmed by the payment provider.
`SUCCESS`	Payment confirmed. `paid_at` is set. Associated fines are marked `PAID`.
`FAILED`	Payment was declined or failed at the provider level.
`REVERSED`	Payment was reversed after initial success (e.g. EcoCash reversal or chargeback).

2.10 `payment_items`¶

Owned by: payments-service

Bridge table linking a payment to the specific fines or parking tickets it covers. This many-to-many structure allows a single payment to settle multiple outstanding items in one transaction.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`payment_id`	`UUID`	NOT NULL, FK → payments	The parent payment.
`payable_type`	`ENUM`	NOT NULL	See `PayableType` below. Identifies which table `payable_id` references.
`payable_id`	`UUID`	NOT NULL	References either `fines.id` or `parking_tickets.id` depending on `payable_type`.
`reference`	`VARCHAR(255)`	NULLABLE	The payable's reference number (a fine's `reference_number`), snapshotted when the payment is created. V14 backfilled existing items.
`amount`	`INTEGER`	NOT NULL	In cents. The portion of the total payment allocated to this item.

Enum: `PayableType`¶

Value	Description
`FINE`	The payment item references a record in the `fines` table.
`PARKING_TICKET`	The payment item references a record in the `parking_tickets` table.

2.11 `notifications`¶

Owned by: notifications-service One record per notification: one event for one citizen. notification-service creates it from a Kafka event, then pushes it to the citizen's devices; the record exists, and the citizen sees it in the app's Alerts tab, whatever the push outcome. Records are never deleted. V1 created the table; V2 added read state, the display payload, event de-duplication and the new types.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`user_id`	`UUID`	NOT NULL, FK → users	The recipient.
`type`	`VARCHAR(40)`	NOT NULL, CHECK	See `NotificationType` below. Determines the category (`FINES`, `PAYMENTS`, `VEHICLES`) the API filters by.
`channel`	`VARCHAR(10)`	NOT NULL, CHECK	See `NotificationChannel` below. Always `PUSH` for now.
`title`	`VARCHAR(255)`	NOT NULL
`message`	`TEXT`	NOT NULL	The full message body (`body` in the API).
`status`	`VARCHAR(10)`	NOT NULL, CHECK	The push attempt. See `NotificationStatus` below.
`reference_id`	`UUID`	NULLABLE	ID of the related entity (fine, payment or vehicle).
`reference_type`	`VARCHAR(30)`	NULLABLE, CHECK	One of: `FINE`, `PARKING_TICKET`, `VEHICLE_DOCUMENT`, `VEHICLE`, `PAYMENT` (`VEHICLE` and `PAYMENT` added in V2).
`sent_at`	`TIMESTAMP WITH TIME ZONE`	NULLABLE	Set when at least one device accepted the push.
`created_at`	`TIMESTAMP WITH TIME ZONE`	NOT NULL
`read_at`	`TIMESTAMP WITH TIME ZONE`	NULLABLE	When the citizen read it; NULL while unread. Set once: marking it read again keeps the first time. V2.
`payload`	`JSONB`	NULLABLE	Deep-link target and display data, such as the fine id, plate, amount and due date. Keys per type in `API Contracts.md` §6.9. V2.
`event_id`	`UUID`	NULLABLE	The `eventId` of the Kafka event that caused it. V2.

Indexes and constraints:

CREATE INDEX notifications_user_history_idx ON notifications (user_id, created_at DESC, id DESC);
-- V2: one notification per event per recipient, so a redelivered event is not notified twice.
ALTER TABLE notifications ADD CONSTRAINT notifications_event_user_uk UNIQUE (event_id, user_id);
-- V2: serves the unread count and read-all.
CREATE INDEX notifications_user_unread_idx ON notifications (user_id) WHERE read_at IS NULL;

Enum: `NotificationType`¶

Owned by: notifications-service

Value	Category	Description
`VEHICLE_VERIFIED`	`VEHICLES`	Sent to the claiming citizen when CVR verifies their claim to a vehicle.
`VEHICLE_VERIFICATION_FAILED`	`VEHICLES`	Sent to the claiming citizen when CVR rejects their claim, with CVR's reason.
`FINE_ISSUED`	`FINES`	Sent to the vehicle owner when a fine is issued on their vehicle (court wording when it is referred to court).
`FINE_PAID`	`FINES`	Sent to the vehicle owner when someone else paid their fine.
`FINE_DISPUTE_DECIDED`	`FINES`	Sent to the vehicle owner when a dispute is upheld or rejected (with the new due date when rejected).
`FINE_COURT_OUTCOME`	`FINES`	Sent to the vehicle owner when a court sets a penalty or dismisses the fine.
`FINE_STATUS_CHANGED`	`FINES`	Sent to the vehicle owner when an admin clears or cancels a fine.
`PAYMENT_SUCCESS`	`PAYMENTS`	Sent to the payer when a payment succeeds, listing the fines it covered.
`PAYMENT_FAILED`	`PAYMENTS`	Sent to the payer when a payment fails, with the provider's message.
`DOCUMENT_EXPIRY_REMINDER`	`VEHICLES`	Planned: a vehicle document is approaching expiry. Nothing produces it yet.
`FINE_DUE_REMINDER`	`FINES`	Planned: an unpaid fine is approaching its due date. Nothing produces it yet.

The "vehicle owner" is the citizen holding the vehicle's VERIFIED link (§2.4), looked up when the event is processed. The full event-to-notification mapping is in Kafka Design.md §3.

Enum: `NotificationChannel`¶

***Owned by: `system-parameters-service

Value	Description
`SMS`	Text message to the user's registered phone number. Not delivered yet.
`EMAIL`	Email to the user's registered email address. Not delivered yet.
`PUSH`	Mobile push notification via the TrafficGrid app. The only channel used today.

Enum: `NotificationStatus`¶

Value	Description
`PENDING`	Recorded; the push has not been attempted yet.
`SENT`	At least one of the recipient's devices was accepted by Expo's push service. `sent_at` is set.
`FAILED`	No device accepted the push: the recipient has no registered devices, Expo refused every device, or Expo could not be reached. The notification is still listed. Failed pushes are not retried.

2.11.1 `device_tokens`¶

Owned by: notifications-service (V3)

The citizen's phones, by Expo push token. A citizen can have several, and each gets every push. A token belongs to one user at a time: registering it again refreshes last_seen_at, and a different user registering it (another citizen signing in on the same phone) takes it over. Signing out deletes it. A token Expo reports as DeviceNotRegistered (the app was uninstalled) is deleted on the next push.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`user_id`	`UUID`	NOT NULL	The citizen the phone is registered to. Indexed.
`expo_push_token`	`VARCHAR(255)`	NOT NULL, UNIQUE	e.g. `ExponentPushToken[...]`.
`platform`	`VARCHAR(10)`	NOT NULL, CHECK	`ANDROID` or `IOS`.
`last_seen_at`	`TIMESTAMP WITH TIME ZONE`	NOT NULL	Last registration or refresh (every app start while signed in).
`created_at`	`TIMESTAMP WITH TIME ZONE`	NOT NULL

CREATE INDEX device_tokens_user_idx ON device_tokens (user_id);

2.12 `notification_preferences`¶

Stores a user's preferred notification channels. One record per user, created with SMS and EMAIL the first time it is read. The channels themselves are in notification_preference_channels (preference_id, channel).

Kept, but not used when sending: every notification is recorded and pushed whatever the preferences say.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`user_id`	`UUID`	NOT NULL, UNIQUE	One-to-one with users.
`channel`	`ENUM`	NOT NULL	See `NotificationChannel` under `notifications`. Stored as rows of `notification_preference_channels`.
`updated_at`	`TIMESTAMP`	NOT NULL

2.13 `audit_logs`¶

Owned by: audit-service Immutable record of every state-changing action in the system. Records are never updated or deleted. The before_state and after_state columns store full JSON snapshots of the affected entity, providing a complete audit trail for compliance and forensic investigation.

Column	Type	Constraints	Notes
`id`	`UUID`	PK	Auto-generated.
`actor_id`	`UUID`	NOT NULL, FK → users	The user who performed the action.
`actor_role`	`VARCHAR(50)`	NULLABLE	Snapshot of the actor's role at time of action. Stored separately so role changes don't alter historical records. NULL if the event carried an unknown role.
`actor_name`	`VARCHAR(255)`	NULLABLE	Snapshot of the actor's full name, from the event's `actorName` (V3). NULL for older entries.
`organisation_id`	`UUID`	NULLABLE	Snapshot of the actor's organisation. NULL for actors outside any organisation (citizens, the super admin).
`action`	`TEXT`	NOT NULL	Description of what happened. E.g. `FINE_CREATED`, `PAYMENT_RECORDED`, `USER_DEACTIVATED`.
`entity_type`	`VARCHAR(100)`	NOT NULL	The table that was affected. E.g. `fines`, `users`, `payments`.
`entity_id`	`VARCHAR(100)`	NOT NULL	ID of the affected record. Stored as VARCHAR to support any entity type without foreign key coupling.
`before_state`	`JSONB`	NULLABLE	Full JSON snapshot of the entity before the change. NULL for create operations.
`after_state`	`JSONB`	NULLABLE	Full JSON snapshot of the entity after the change. NULL for delete operations.
`ip_address`	`VARCHAR(50)`	NULLABLE	IP address of the request.
`user_agent`	`TEXT`	NULLABLE	Client user agent string.
`created_at`	`TIMESTAMP`	NOT NULL	Immutable. Never updated.

2.14 Reference tables in `system-parameters-service`¶

Owned by: system-parameters-service (schema system_parameters)

Alongside organizations, fine_categories and offences, the service owns three reference tables. Each has id, is_active, created_at and updated_at, plus:

Table	Columns
`currencies`	`name` (UNIQUE), `code` `VARCHAR(5)` (UNIQUE, e.g. `USD`, `ZWG`), `rate` `INTEGER` (units per US dollar), `deactivation_reason` `VARCHAR(500)`
`payment_methods`	`code` `VARCHAR(50)` (NOT NULL, UNIQUE), `name`, `description`, `deactivation_reason` `VARCHAR(500)`
`notification_channels`	`code` `VARCHAR(50)` (NOT NULL, UNIQUE), `name`, `description`, `deactivation_reason` `VARCHAR(500)`

code on payment methods and notification channels is a stable UPPER_SNAKE identifier. V11 added it, backfilled from the name (Bank transfer → BANK_TRANSFER; names that collapse to the same code get _2, _3, ...). deactivation_reason on all five tables (V11) is set on deactivate and cleared on activate.

3. Cross-Service Relationships¶

Because services own separate schemas, traditional FK constraints do not cross service boundaries. The table below shows logical relationships and how they are enforced at the application layer.

From	To	Relationship	Enforcement
citizen_vehicles.citizen_id	users.id (auth-service)	Many citizens → one user	Application validates via auth-service API on link creation
citizen_vehicles.vehicle_id	vehicles.id	Many links → one vehicle; at most one pending or verified	FK within vehicle-service schema; partial unique index `uq_citizen_vehicles_active_claim`
vehicle owner (of a fine's vehicle)	citizen_vehicles (`VERIFIED` row, vehicle-service)	One vehicle → zero or one owner	No copy is stored. vehicle-service answers `GET /vehicles/{id}/owner` (users with `VEHICLE_READ`) and `GET /internal/v1/vehicles/{id}/owner` (services, with the shared `X-Service-Key`); 404 when there is no verified owner. The owner is looked up when needed, so it is whoever owns the vehicle at that moment
vehicle_documents.vehicle_id	vehicles.id	Many docs → one vehicle	FK within vehicle-service schema
fines.vehicle_id	vehicles.id (vehicle-service)	Many fines → one vehicle	payments-service calls vehicle-service via OpenFeign to verify existence before creating fine
fines.fine_category_id	fine_categories.id (system-parameters-service)	Many fines → one level	payments-service calls system-parameters-service via OpenFeign to resolve the category (amount, issuing organisation, active status) at issue time
parking_tickets.vehicle_id	vehicles.id (vehicle-service)	Many tickets → one vehicle	Same pattern as fines.vehicle_id
payments.paid_by	users.id (auth-service)	Many payments → one user	User ID extracted from JWT claims — no API call needed
payment_items.payment_id	payments.id	Many items → one payment	FK within payments-service schema
payment_items.payable_id	fines.id or parking_tickets.id	Many items → one payable	FK within payments-service schema — fines, parking_tickets, and payment_items are now co-located, so this is an in-process lookup, not a cross-service call; payable status is updated locally when a payment succeeds
notifications.user_id	users.id (auth-service)	Many notifications → one user	Taken from the Kafka event: the claiming citizen (vehicle events), the payer's `paidBy.id` (payment events), or the vehicle's verified owner (fine events), looked up through vehicle-service's internal owner endpoint when the event is processed
device_tokens.user_id	users.id (auth-service)	Many devices → one user	The caller's id from the gateway's identity headers when the app registers the device
audit_logs.actor_id	users.id (auth-service)	Many logs → one actor	actor_id extracted from Kafka event payload. No FK — audit_logs must never be constrained by user existence.

4. Key Design Decisions¶

4.1 UUIDs as Primary Keys¶

All primary keys use UUIDs rather than auto-incrementing integers. This prevents enumeration attacks (guessing sequential IDs to access other users' records), supports future distributed database deployments where multiple instances might generate IDs independently, and avoids leaking record counts to external parties.

4.2 No cross service foreign keys¶

Services own their own schema. When service A needs to reference an entity from service B, it stores the UUID as a plain column (no database-level FK). Referential integrity is enforced at the application layer via OpenFeign API calls before writes, or by consuming events from Kafka. This is mandatory for service independence — a service must be deployable without requiring another service's database to be available.

4.3 Deliberate Amount Denormalization on Fines¶

The fine amount is copied from fine_categories (system-parameters-service) onto the fines record at the time of issue. Fine amounts are set by regulation and can change over time. A driver fined $200 in 2024 must still show $200 on their record even if the government later changes the speeding fine to $250. The currency is copied with the amount. The same principle applies to actor_role and organisation_id in audit_logs.

All amounts are stored as INTEGER cents (20000 is $200.00), never as decimals.

4.4 Soft Deletes¶

Users, vehicles, organisations, and fine categories use is_active flags for deactivation rather than hard deletes. Hard deletes would break the logical relationships maintained at the application layer. audit_logs is append-only and is never soft-deleted or hard-deleted under any circumstances.

4.5 Idempotency on Payments¶

The payments table has an idempotency_key column with a UNIQUE constraint. When a client submits a payment request it generates a unique key. If the network drops and the client retries, the server returns the existing payment record instead of creating a duplicate charge.

4.6 Polymorphic Reference in `payment_items`¶

The payment_items table uses payable_type + payable_id to reference either a fine or a parking ticket without requiring two separate FK columns. fines, parking_tickets, and payment_items are now all owned by payments-service (see §4.11), so routing on payable_type is an in-process repository lookup, not a cross-service call, and payable status is updated locally when a payment succeeds.

4.7 JSONB for Audit Snapshots¶

The before_state and after_state columns in audit_logs use PostgreSQL's JSONB type rather than plain TEXT. JSONB stores JSON in a binary format that supports indexing and querying inside the JSON structure. This means audit records can be searched by specific field values when investigating incidents.

4.8 Domain-local enums¶

Every enum type is defined in the service that owns the domain it describes. No enum is placed in core-service or shared across service codebases. This ensures each service can evolve its domain independently without coupling through a shared library. For example, FineStatus lives in payments-service; NotificationStatus lives in notifications-service.

4.9 notifications-service as a dedicated service¶

notifications-service is a standalone running process on port 8088 (previously co-located in payments-service in v2.0). It is the live consumer of trafficgrid.fines, trafficgrid.payments and trafficgrid.vehicles, in consumer group notifications-service: it turns the events those services already publish into citizen notifications, records them, and pushes them through Expo. It also exposes the citizen's own REST endpoints for the notification list, read state, devices and preferences (API Contracts.md §6.9).

The services that own vehicles, fines and payments don't know notifications exist: they publish domain events as before, and all notification logic (who is told, and what) lives here. The cost is that notification-service parses each event's afterState JSON, and de-duplicates on the event's eventId. This separation keeps payments-service focused on financial transactions and gives notifications its own deployable unit.

4.10 System-parameters-service¶

system-parameters is a live running service (port 8083) that owns currencies, notification channels, organisations, and operational configuration. Other services call it via OpenFeign at runtime when they need reference data. it is home for all aspects of the system that are to be configured at system initialization

4.11 Fines consolidated into payments-service¶

fines-service was planned as its own deployed process but was never built — it's retired from the architecture rather than merely delayed. fines and parking_tickets (schema, enums, and business logic) are instead a permanent part of payments-service. This wasn't just filling a gap with the nearest available service: payment success has to mark a fine PAID, so fines and payments were always the two most tightly coupled domains in the system. Co-locating them removes what would otherwise be a cross-service call on that single most frequent interaction, while fine_categories correctly stays external in system-parameters-service as reference data shared with other concerns (currencies, payment methods, notification channels). Fines also adopted the platform's permission-code (PBAC) model from day one as part of this move — see Roles and Permissions.md for the FINE_CREATE/FINE_READ/FINE_STATUS_UPDATE codes — rather than the role-based gating an earlier, now-superseded revision of API Contracts.md describes.

4.12 Name and Label Snapshots¶

Records that name a person or show another service's data store a copy taken at write time instead of looking it up on read: fines store the vehicle's plate, make and model, the category's code and name, the officer's name and the organisation's short name; payments store the payer's and recording officer's names; payment items store the fine's reference; audit logs store the actor's name. Lists and detail views therefore need no cross-service calls, and history does not change when a name does. The actor's name reaches each service in the gateway's X-User-Name header (see API Contracts.md §2).

5. Pending Items¶

5.1 Schema Pending Implementation¶

parking_tickets — table relocated / schema provisioned in payments-service; endpoints pending City Parking API integration specification
users.created_by — to be added via Flyway migration when SUPER_ADMIN account creation flow is built

5.2 External Integrations Pending¶

VTS API — vehicle fitness and roadworthiness verification
Central Vehicle Registry (CVR) API — real ownership verification; vehicle-service's mock CVR stands in, and only CvrVerificationClient changes when it is integrated
ZINARA API — vehicle registration and document verification
City Parking API — parking ticket import and synchronisation
EcoCash payment gateway — real-time payment processing
Bank transfer webhook integration
SMS and email delivery for notifications (no provider yet; notifications are push only, and the SMS/EMAIL channels and notification_preferences are not used when sending)
Firebase project and FCM v1 key for Android push (the code is in place; see specs/Notifications.md)
APNs key for iOS push. The app and notification-service are platform-neutral, so iOS needs only the key uploaded to Expo
Expo push receipts: notification-service records Expo's immediate ticket (SENT/FAILED) but does not poll the delayed delivery receipts, and does not retry failed pushes

TrafficGrid — Data Model & System Design Reference¶

Table of Contents¶

1. System Overview¶

1.1 Stakeholders & Integrations¶

1.2 Schema Ownership¶

1.3 User Roles¶

2. Entity Reference¶

2.1 users¶

Enum: UserRole¶

password_reset_tokens¶

2.2 organisations¶

Enum: OrganisationType¶

2.3 vehicles¶

Enum: VehicleType¶

2.4 citizen_vehicles¶

Enum: VerificationStatus¶

mock_cvr_verifications¶

2.5 vehicle_documents¶

Enum: DocumentType¶

2.6 fine_categories (levels)¶

Seeded levels¶

2.6.1 offences¶

Seeding offences¶

2.7 fines¶

Enum: FineStatus¶

2.7.1 fine_disputes¶

Enum: DisputeGrounds¶

Enum: DisputeStatus¶

2.7.2 fine_dispute_evidence¶

2.8 parking_tickets¶

Enum: ParkingTicketStatus¶

2.9 payments¶

Enum: PaymentMethod¶

Enum: PaymentStatus¶

2.10 payment_items¶

Enum: PayableType¶

2.11 notifications¶

Enum: NotificationType¶

Enum: NotificationChannel¶

Enum: NotificationStatus¶

2.11.1 device_tokens¶

2.12 notification_preferences¶

2.13 audit_logs¶

2.14 Reference tables in system-parameters-service¶

3. Cross-Service Relationships¶

4. Key Design Decisions¶

4.1 UUIDs as Primary Keys¶

4.2 No cross service foreign keys¶

4.3 Deliberate Amount Denormalization on Fines¶

4.4 Soft Deletes¶

4.5 Idempotency on Payments¶

4.6 Polymorphic Reference in payment_items¶

4.7 JSONB for Audit Snapshots¶

4.8 Domain-local enums¶

4.9 notifications-service as a dedicated service¶

4.10 System-parameters-service¶

4.11 Fines consolidated into payments-service¶

4.12 Name and Label Snapshots¶

5. Pending Items¶

5.1 Schema Pending Implementation¶

5.2 External Integrations Pending¶

2.1 `users`¶

Enum: `UserRole`¶

`password_reset_tokens`¶

Enum: `OrganisationType`¶

2.3 `vehicles`¶

Enum: `VehicleType`¶

2.4 `citizen_vehicles`¶

Enum: `VerificationStatus`¶

`mock_cvr_verifications`¶

2.5 `vehicle_documents`¶

Enum: `DocumentType`¶

2.6 `fine_categories` (levels)¶

2.6.1 `offences`¶

2.7 `fines`¶

Enum: `FineStatus`¶

2.7.1 `fine_disputes`¶

Enum: `DisputeGrounds`¶

Enum: `DisputeStatus`¶

2.7.2 `fine_dispute_evidence`¶

2.8 `parking_tickets`¶

Enum: `ParkingTicketStatus`¶

2.9 `payments`¶

Enum: `PaymentMethod`¶

Enum: `PaymentStatus`¶

2.10 `payment_items`¶

Enum: `PayableType`¶

2.11 `notifications`¶

Enum: `NotificationType`¶

Enum: `NotificationChannel`¶

Enum: `NotificationStatus`¶

2.11.1 `device_tokens`¶

2.12 `notification_preferences`¶

2.13 `audit_logs`¶

2.14 Reference tables in `system-parameters-service`¶

4.6 Polymorphic Reference in `payment_items`¶