Skip to content

Phase 1 — Work Orders service: data modelling and API design

Trade-offs exercised while building the first real service. Each section is a decision point that comes up in system design discussions.

UUID vs serial primary keys

Chose uuid (v4, gen_random_uuid()).

  • For: IDs can be generated by any node without coordination (essential once events carry IDs across services); not guessable/enumerable in URLs; safe to merge datasets.
  • Against: 16 bytes vs 4/8; random v4 scatters inserts across the B-tree hurting index locality at high write volume.
  • Escape hatch: UUIDv7 (time-ordered) restores locality while keeping decentralised generation — worth migrating to if insert volume becomes a concern.

Enum values: PostgreSQL ENUM type vs varchar + CHECK constraint

Chose varchar + CHECK.

  • Adding a value to a pg ENUM is ALTER TYPE ... ADD VALUE (couldn't run inside a transaction until pg 12, still can't remove/reorder values). A CHECK constraint swap is a plain, transactional DDL change.
  • Same integrity guarantee for this cardinality; the TS enum remains the single source of truth in code.
  • pg ENUM stores a 4-byte OID vs the string bytes — a real but irrelevant saving at this scale.

Status as a guarded state machine

Transitions live in one table (work-order-transitions.ts), not scattered ifs:

open → assigned → in_progress → completed
  └──────┴────────────┴─→ cancelled        (terminal states: completed, cancelled)
  • Invalid transitions are a domain conflict → HTTP 409 (not 400: the payload is well-formed; it's the state that refuses it).
  • assigned is only reachable through the assign endpoint, so an assignee is always present. The same invariant is enforced in the database (CHECK (status <> 'assigned' OR assignee_id IS NOT NULL)) — defence in depth: the app gives good errors, the DB makes the invariant unbreakable.
  • Known gap (deliberate, fixed in Phase 7): read-modify-write without locking means two concurrent transitions can race (lost update). Options: optimistic locking (@VersionColumn), SELECT ... FOR UPDATE, or a conditional UPDATE ... WHERE status = $expected.

No cross-service foreign keys

property_id is an indexed plain column. The properties table will live in another service's database, so a DB-level FK is impossible by design — referential integrity across services is eventual and enforced at the application/event level. Inside a service's own schema, FKs remain the default.

Offset vs cursor pagination

Chose offset (page/limit) for an admin-style listing.

  • Offset: trivially supports "jump to page N" and total counts; degrades linearly (OFFSET 10000 scans 10k rows) and can skip/duplicate items when rows are inserted between pages.
  • Cursor (keyset, e.g. WHERE (created_at, id) < ($cursor)): stable under writes and O(log n), but no random page access. The right choice for infinite-scroll feeds — the Phase 5 activity feed will use it.

Migrations on boot vs on deploy

migrationsRun: true keeps dev and tests friction-free. In production with several replicas this is a race (concurrent migrators) and couples rollout to schema changes — the hardening phase moves migrations to an explicit deploy step before the new version rolls out.

Testing strategy

  • Unit (work-orders.service.spec.ts): repository mocked via getRepositoryToken — verifies our logic (transition guards, pagination math), not the ORM's.
  • e2e (test/work-orders.e2e-spec.ts): boots the real AppModule against a dedicated work_orders_test database, exercising the same validation pipeline as production (configureApp shared with main.ts). Asserts the HTTP contract: status codes, error shapes, defaults applied by the DB.
  • The table is truncated before each test — tests stay independent and order-insensitive.