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¶
- System Overview
- Entity Reference
- Entity Relationships
- Key Design Decisions
- Pending Items & Future Work
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 vehicle ownership, VIN numbers, and registration details. Falls under ZRP. | API integration planned |
| 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 |
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 |
| fines-service | fine_categories, fines, parking_tickets (planned) |
| payments-service | payments, payment_items (planned) |
| notifications-service | notifications, notification_preferences |
| audit-service | audit_logs |
| system-parameters | system_parameters schema consisting of currencies, organizations, fine categories, notification_types and payment methods tables |
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_ADMINaccounts are created bySUPER_ADMINonly.OFFICERaccounts are created byAUTHORITY_ADMINonly. OnlyCITIZENScan 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. |
| 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. |
| is_active | BOOLEAN | NOT NULL, DEFAULT true | Soft delete flag. Deactivated users cannot log in. |
| 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. |
2.2 organisations¶
Owned by system-parameters-service
Represents the government bodies and agencies that interact with TrafficGrid. Officers belong to an organisation. Fine categories 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. |
created_at |
TIMESTAMP |
NOT NULL | Set on insert. |
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 against VTS and ZINARA records is planned.
| 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. |
colour |
VARCHAR(50) |
NULLABLE | |
vehicle_type |
ENUM |
NOT NULL | See VehicleType below. |
is_verified |
BOOLEAN |
NOT NULL, DEFAULT false | Set to true once verified against VTS/ZINARA API. |
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. |
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
Join table implementing the many-to-many relationship between citizens and vehicles. A citizen can link multiple vehicles. A vehicle can be linked by multiple citizens (e.g. a family car). 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 | |
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 linked this vehicle. |
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. false if sourced from ZINARA API. |
created_at |
TIMESTAMP |
NOT NULL | |
updated_at |
TIMESTAMP |
NOT NULL |
Enum: DocumentType¶
***Owned by: `vehicle-service
| Value | Description |
|---|---|
VEHICLE_LICENCE |
Annual vehicle licence disc issued by ZINARA. |
INSURANCE |
Third-party vehicle insurance certificate. |
ZBC_LICENCE |
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¶
Owned by: system-parameters-service
Reference table defining the types of offences and their standard amounts. Maintained by admins. Fine amounts here represent the current standard — historical fines store their own amount snapshot so past records are never affected by future changes to this table.
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID |
PK | Auto-generated. |
code |
VARCHAR(50) |
NOT NULL, UNIQUE | Machine-readable code. E.g. SPEEDING_01, NO_LICENCE_01. |
name |
VARCHAR(255) |
NOT NULL | Human-readable name. E.g. Exceeding Speed Limit. |
description |
TEXT |
NULLABLE | Extended description of the offence. |
amount |
DECIMAL(10,2) |
NOT NULL | Current standard fine amount. Copied onto fines at issue time. |
issuing_organisation |
ENUM |
NOT NULL | See IssuingOrganisation below. |
is_active |
BOOLEAN |
NOT NULL, DEFAULT true | Inactive categories cannot be used to issue new fines. |
created_at |
TIMESTAMP |
NOT NULL |
Enum: IssuingOrganisation¶
Owned by: system-parameters-service
Controls which officer types can issue fines under this category.
| Value | Description |
|---|---|
ZRP |
Only ZRP officers can issue fines in this category. |
COUNCIL |
Only Council officers can issue fines in this category. |
BOTH |
Both ZRP and Council officers can issue fines in this category. |
2.7 fines¶
Owned by: fines-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 type of offence. |
issued_by |
UUID |
NOT NULL, FK → users | The officer who issued the fine. |
organisation_id |
UUID |
NOT NULL, FK → organisations | The issuing organisation. Snapshot for reporting. |
amount |
DECIMAL(10,2) |
NOT NULL | Copied from fine_categories.amount at issue time. Unaffected by future category changes. |
status |
ENUM |
NOT NULL, DEFAULT PENDING | See FineStatus below. |
location |
TEXT |
NULLABLE | Where the offence occurred. |
notes |
TEXT |
NULLABLE | Officer notes on the incident. |
due_date |
DATE |
NOT NULL | Default 30 days from issue date. Used for overdue reminders. |
issued_at |
TIMESTAMP |
NOT NULL | When the fine was issued. |
updated_at |
TIMESTAMP |
NOT 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);
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 |
Citizen has formally disputed the fine. Awaiting resolution. |
CANCELLED |
Fine cancelled before payment. Officer or admin action. |
2.8 parking_tickets¶
Owned by: fines-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 |
DECIMAL(10,2) |
NOT NULL | |
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: PAY-YYYY-NNNNN. E.g. PAY-2026-00789. |
paid_by |
UUID |
NOT NULL, FK → users | The citizen making the payment. |
payment_method |
ENUM |
NOT NULL | See PaymentMethod below. |
amount |
DECIMAL(10,2) |
NOT NULL | Total payment amount. Must equal the sum of payment_items amounts. |
status |
ENUM |
NOT NULL | See PaymentStatus below. |
provider_reference |
TEXT |
NULLABLE | Transaction reference from EcoCash or bank. Used for reconciliation. |
recorded_by |
UUID |
FK → users, NULLABLE | Set only for CASH payments logged by an officer. NULL for online payments. |
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 |
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
⚠️ Status: Planned — to be implemented alongside the payments feature.
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. |
amount |
DECIMAL(10,2) |
NOT NULL | 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
Immutable log of all notifications sent to users. A new record is created for each notification attempt. Used for delivery tracking and auditing.
| Column | Type | Constraints | Notes |
|---|---|---|---|
id |
UUID |
PK | Auto-generated. |
user_id |
UUID |
NOT NULL, FK → users | The recipient. |
type |
ENUM |
NOT NULL | See NotificationType below. |
channel |
ENUM |
NOT NULL | See NotificationChannel below. |
title |
VARCHAR(255) |
NOT NULL | |
message |
TEXT |
NOT NULL | The full message body. |
status |
ENUM |
NOT NULL | See NotificationStatus below. |
reference_id |
UUID |
NULLABLE | ID of the related entity (fine, ticket, or document). |
reference_type |
ENUM |
NULLABLE | One of: FINE, PARKING_TICKET, VEHICLE_DOCUMENT. |
sent_at |
TIMESTAMP |
NULLABLE | Set when delivery is confirmed. |
created_at |
TIMESTAMP |
NOT NULL |
Enum: NotificationType¶
Owned by: system-parameters-service
| Value | Description |
|---|---|
FINE_ISSUED |
Sent to the vehicle owner when a new fine is logged against their vehicle. |
PAYMENT_SUCCESS |
Sent to the payer when a payment is confirmed. |
DOCUMENT_EXPIRY_REMINDER |
Sent when a vehicle document (licence, insurance, etc.) is approaching expiry. |
FINE_DUE_REMINDER |
Sent when an unpaid fine is approaching its due date. |
Enum: NotificationChannel¶
***Owned by: `system-parameters-service
| Value | Description |
|---|---|
SMS |
Text message to the user's registered phone number. |
EMAIL |
Email to the user's registered email address. |
PUSH |
Mobile push notification via the TrafficGrid app. |
Enum: NotificationStatus¶
| Value | Description |
|---|---|
PENDING |
Notification queued but not yet dispatched. |
SENT |
Successfully delivered to the provider. sent_at is set. |
FAILED |
Delivery failed after all retry attempts. |
2.12 notification_preferences¶
Stores a user's preferred notification channels. One record per user, created on registration with sensible defaults.
| 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. |
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) |
NOT NULL | Snapshot of the actor's role at time of action. Stored separately so role changes don't alter historical records. |
organisation_id |
UUID |
NULLABLE | Snapshot of the actor's organisation. NULL for citizens. |
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. |
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 | FK within vehicle-service schema |
| 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 | fines-service calls vehicle-service via OpenFeign to verify existence before creating fine |
| fines.fine_category_id | fine_categories.id | Many fines → one category | FK within fines-service schema |
| 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 (fines-service) | Many items → one payable | payments-service calls fines-service via OpenFeign to validate and update status on payment.success event |
| notifications.user_id | users.id (auth-service) | Many notifications → one user | User ID extracted from RabbitMQ event payload |
| audit_logs.actor_id | users.id (auth-service) | Many logs → one actor | actor_id extracted from RabbitMQ 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 RabbitMQ. 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 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 same principle applies to actor_role and organisation_id in audit_logs.
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. Since fines and parking_tickets both live in fines-service, payments-service calls fines-service's API passing the payable_type to route the lookup correctly.
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 fines-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 consumes RabbitMQ events to trigger outbound notification dispatch, and exposes REST endpoints for notification history and preference management. 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
5. Pending Items¶
5.1 Schema Pending Implementation¶
- parking_tickets — pending City Parking API integration specification
- payment_items — to be implemented alongside the payments feature
- 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
- 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 provider integration for notifications