Data Model
Complete entity-relationship model for Certexi — 15 core tables, 25 indexes, immutable event ledger, and computed state derivation patterns.
Last updated: 2025-02-18
Data Model
Certexi uses PostgreSQL with Drizzle ORM for type-safe database access. The schema follows event-sourcing principles — mutable state is always derived from an immutable event ledger.
Entity-Relationship Overview
Core Tables
operators
The authenticated users who interact with the system.
| Column | Type | Description |
|---|---|---|
operator_id | text (PK) | Unique identifier |
name | text | Display name |
email | text | Email address |
role | enum | operator, supervisor, admin |
nextcloud_id | text | Linked Nextcloud account |
created_at | timestamp | Account creation time |
last_active | timestamp | Last activity timestamp |
events (Immutable Ledger)
Append-Only
The events table is the immutable ledger. Rows are NEVER updated or deleted. All state is derived from replaying events in chronological order.
| Column | Type | Description |
|---|---|---|
id | serial (PK) | Auto-increment ID |
event_type | enum | PLACED, REMOVED, VERIFIED, DISPUTED |
slot_id | int (FK) | Target slot |
asset_barcode | text | Asset identifier |
operator | text (FK) | Who performed the action |
timestamp | timestamptz | When it happened |
photo_url | text | Evidence photo URL |
scale_weight_kg | float | Weight at time of event |
evidence_hash | text | SHA-256 hash of event bundle |
merkle_root | text | Daily Merkle tree root |
cctv_clip_url | text | Optional linked CCTV clip |
work_order_ref | text | Optional work order reference |
workflow_stage_history
Tracks every stage transition for transport units.
| Column | Type | Description |
|---|---|---|
id | serial (PK) | Auto-increment ID |
transport_unit_id | int (FK) | Transport unit reference |
from_stage | text | Previous stage |
to_stage | text | New stage |
operator | text (FK) | Who triggered the transition |
evidence | json | Evidence bundle |
hash | text | SHA-256 hash of transition |
created_at | timestamptz | Transition time |
Indexes
The schema includes 25 indexes optimized for common query patterns:
| Table | Index | Columns | Purpose |
|---|---|---|---|
| events | idx_events_slot_time | slot_id, timestamp | Slot history queries |
| events | idx_events_asset | asset_barcode | Asset location lookup |
| events | idx_events_operator | operator, timestamp | Operator activity |
| events | idx_events_type_time | event_type, timestamp | Type-filtered queries |
| events | idx_events_time_geo | timestamp, location | Map time-window queries |
| slots | idx_slots_zone | zone | Zone slot listing |
| transport_units | idx_tu_stage | current_stage | Kanban queries |
| transport_units | idx_tu_operator | assigned_operator | Operator workload |
| workflow_history | idx_wh_tu | transport_unit_id | Unit history |
State Derivation
Current state is never stored directly — it's computed from the event ledger:
Slot Occupancy
-- Get current occupancy for a slot
SELECT DISTINCT ON (slot_id)
slot_id, event_type, asset_barcode, timestamp
FROM events
WHERE slot_id = $1
ORDER BY slot_id, timestamp DESC;
-- If event_type = 'PLACED' → occupied
-- If event_type = 'REMOVED' → empty
Asset Location
-- Find where an asset is currently located
SELECT DISTINCT ON (asset_barcode)
slot_id, event_type, timestamp
FROM events
WHERE asset_barcode = $1
ORDER BY asset_barcode, timestamp DESC;
Zone Utilization
-- Compute utilization per zone
WITH latest_events AS (
SELECT DISTINCT ON (slot_id)
s.zone, e.event_type
FROM events e
JOIN slots s ON s.id = e.slot_id
ORDER BY slot_id, e.timestamp DESC
)
SELECT zone,
COUNT(*) FILTER (WHERE event_type = 'PLACED') AS occupied,
COUNT(*) AS total
FROM latest_events
GROUP BY zone;
Migrations
Drizzle ORM manages schema migrations with version control:
# Generate migration from schema changes
pnpm drizzle-kit generate:pg
# Apply pending migrations
pnpm drizzle-kit push:pg
# View migration status
pnpm drizzle-kit status