Meridian Field Services: instructional example, not project evidence
One view per bounded context, generated from versioned source: eight to ten entities per view, split by aggregate past the cap. The diagram carries structure, keys, and cardinality; precision, defaults, and enums live in the field tables a diagram cannot express. System-of-record stays the architecture's decision.
Dispatch core: five entities, under the cap; crow's foot cardinality; generated from dispatch-core.mmd, stamped 2026-07-16. Fallback: Job holds zero-or-many Assignments; Technician receives Assignments and reports Positions via vehicle; ReasonCode optionally explains an override. Inventory reconciles with the architecture's data chapter[1] both ways; Job's pilot-to-cutover authority is cited there, never re-decided here.
Conventions stated once (audit, delete policy, tenancy), then per entity the key strategy and what it means. Business meaning links to the glossary; this document owns structure.
| Entity | PK strategy | Why | Business meaning |
|---|---|---|---|
| Assignment | surrogate event_id | Natural key job-plus-sequence rejected: replay ordering | TERM-001[4] |
| Job | surrogate id | Lifecycle states created → assigned → completed → archived | TERM-009[4] |
| Position | natural (vehicle_id, observed_at) | Readings are immutable facts | TERM-012[4] |
| ReasonCode | natural code | Governed list: the code is the identity | TERM-004[4] |
| Technician | natural hr_id | Read copy; system of record is Meridian HR (architecture[1]) | none |
Per field: type with precision, nullability and default stated (a blank cell is a gap), sensitivity citing the governing classification, source, and a description that adds meaning beyond the name. Every entity is fully specified, one table each.
| Field | Type | Null / default | Sensitivity | Source | Description (meaning beyond the name) |
|---|---|---|---|---|---|
| event_id | uuid PK | not null / generated | internal | system | Surrogate event identity; ordering key for replay |
| job_id | uuid FK | not null / no default | internal | system | The job this event binds; partial-unique enforces one active per job (§5) |
| technician_id | text FK | not null / no default | internal | system | The technician bound: HR id, read copy keyed (§2) |
| reason_code | text FK | nullable / no default | internal | user-entered on override | Why the dispatcher overrode; null means an accepted suggestion, never a blank string; immutable after write |
| note | varchar(250) | nullable / no default | internal | user-entered | Optional override context; the 250 boundary is the story's tested edge (US-3 row AT-3[3]) |
| created_at / created_by | timestamptz · text | not null / now · session | internal | system | The audit convention (§2); events are immutable, so no updated columns |
| Field | Type | Null / default | Sensitivity | Source | Description |
|---|---|---|---|---|---|
| id | uuid PK | not null / generated | internal | system | Surrogate; the customer-facing job number is presentation, not identity |
| skill_required | text | not null / no default | internal | user-entered at intake | Validated against the skill taxonomy; unknown values reject at intake (ERR-02/03[5]) |
| priority | text | not null / routine | internal | user-entered | Drives queue order; priority jobs pin above routine regardless of age |
| status | text (app constant, §6) | not null / created | internal | system | Lifecycle state; transitions are code-governed, not data-governed |
| source_system | text | nullable / no default | internal | integration (INT-3) | Transitional pilot marker; removal trigger in §8 |
| address / lat / lon | text · decimal(9,6) | not null / no default | confidential | user-entered | Service location: customer site, not technician position; distinct sensitivity class |
| Field | Type | Null / default | Sensitivity | Source | Description |
|---|---|---|---|---|---|
| Position.vehicle_id + observed_at | text · timestamptz, composite PK | not null / no default | internal | integration (INT-1) | Natural key: readings are immutable facts; staleness computes from observed_at, never arrival time |
| Position.lat / lon | decimal(9,6) | not null / no default | personal-restricted, per classification[2] | integration | Vehicle reading, never a technician's assignment state: the disambiguation the data contract enforces (TERM-012[4]) |
| Position.stale_flag | boolean | not null / false | internal | calculated at adapter | True past 60 seconds: the honesty bit the board must render |
| ReasonCode.code / meaning | text PK · text | not null / no default | internal | seed data (§6) | RC-01…RC-06; a code in use retires in its list, never deletes |
| Technician.hr_id / name / certifications | text PK · text · text | not null / no default | confidential | integration (Meridian HR) | Read copy, one-hour freshness; the system of record is HR (§2); certifications gate suggestion eligibility (FR-013[5]) |
Agent export carries the schema as data, the consolidated DBML block beside these tables (excerpt):
| Relationship | Cardinality (both ways) | On delete | Note |
|---|---|---|---|
| Job · Assignment | one job holds zero-or-many assignment events; each event belongs to exactly one job | restrict; jobs archive, never cascade | identifying |
| Technician · Assignment | one-to-zero-or-many / exactly-one | restrict | none |
| Technician · Position | one-to-zero-or-many via vehicle mapping | scheduled purge, not cascade | non-identifying |
| ReasonCode · Assignment | zero-or-one / zero-or-many | restrict; a code in use retires in its list, never deletes | none |
No junction tables; no polymorphic references. Stated, so their absence is a fact and not a gap.
Invariants beyond keys, each in business terms and its enforcement form. The FRD owns the business rule; this section owns its storage realization; the two cite each other, neither restates.
| Invariant (business terms) | Enforcement | Handshake |
|---|---|---|
| One active assignment per job | partial unique index on assignments(job_id) where active | FR-010[5] cites this row; this row cites FR-010 |
| Override note never exceeds 250 characters | check length(note) ≤ 250 | FR-012 fit criterion[5] |
| Reason code required on the override path | application-enforced; the rule spans the override flag | enforcement split recorded, with reason |
| List | Storage | Owner (adds values) | Values |
|---|---|---|---|
| ReasonCode | reference table, ships as seed data | Product lead; additions are a product decision (open question OQ-2[5]) | RC-01 … RC-06, meanings in the table |
| Job lifecycle states | application constant | Engineering lead; states change with code, not configuration | created · assigned · completed · archived |
No other closed lists exist; stated.
Every index cites the access pattern it serves; "performance" is not a pattern. Volumetrics appear exactly where they drove a decision.
| Index | Access pattern served | Trade acknowledged |
|---|---|---|
| assignments(job_id) partial unique where active | Idempotent acceptance (AC-010[5]; US-2 row AT-2[3]) | none |
| positions(vehicle_id, observed_at desc) | Latest-position reads at 400 rows/min peak (scale frame[1]) | Write cost on an append-heavy table, seen and accepted; ~500k rows/month before purge drove this composite and the 30-day cadence (volumetric note[6]) |
| assignments(technician_id, created_at) | Day-view timeline (mobile flow[5]) | none |
All foreign keys indexed; no exceptions this project.
Policy: expand-and-contract; breaking changes to consumer-visible structures owe the field-tools team 90 days notice (API surface[1]).
| Question | Owner | Answer by | Blocks |
|---|---|---|---|
| Does assignment archival move to cold storage at year two of the seven-year retention? | E. Sandoval | Sep 15, 2026 | The archival index decision only (lifecycle[1]) |
Omission note (how §8 renders on a greenfield project): "§8 Migration & Versioning omitted: pre-launch greenfield schema, no live consumers. Rationale recorded in adaptation event #2."