Add Transactions, Indexes, Security, and Evolution Plans

Learning outcomes

A normalized schema is necessary but not sufficient. Production design must also survive concurrent users, hot queries, tenant isolation, failures, schema evolution, and operational monitoring. This lesson turns the capstone schema into a production architecture.

Command: OpenWorkOrder

Transaction:

1. validate tenant, Asset, and current ownership/business rules;
2. insert WorkOrder;
3. insert initial status event;
4. insert audit/outbox event;
5. commit.

Command: AssignTechnician

Protect:

• WorkOrder must accept assignments;
• Technician belongs to same tenant;
• Technician is active;
• primary-assignment uniqueness;
• optional scheduling overlap rule.

Command: RecordPartUsage

BEGIN;UPDATE part_inventorySET available_qty = available_qty - :qtyWHERE tenant_id = :tenant  AND part_id = :part  AND available_qty >= :qty;-- verify one row changedINSERT INTO part_usage (...);INSERT INTO audit_event (...);COMMIT;

This avoids a lost-update inventory race.

Command: CloseWorkOrder

Check within one protected transaction:

• current status allows closure;
• no active assignments remain;
• required fields/checklists complete;
• closed_at is set;
• status event and audit event are written.

Optimistic versioning

UPDATE work_orderSET problem_description = :new_text,    version = version + 1WHERE tenant_id = :tenant  AND work_order_id = :id  AND version = :expected_version;

Use for user edits where conflicts are relatively rare.

Pessimistic coordination

For invariant-heavy commands, lock the WorkOrder parent row or use serializable isolation if appropriate. Keep transactions short and never wait on external APIs while holding database locks.

Index portfolio

Index restraint

PartUsage may be write-heavy. Do not add every reporting index to the OLTP primary. Keep integrity/hot-path indexes and move heavy analytics to materialized/warehouse structures when needed.

Tenant isolation

Use defense in depth:

• tenant_id on tenant-owned rows;
• tenant-aware FKs;
• tenant-scoped unique constraints;
• tenant-first indexes;
• application predicates;
• row-level security where appropriate;
• cross-tenant negative tests.

Least privilege

Separate service roles:

dispatch_serviceinventory_servicebilling_servicereporting_readonlymigration_admin

Do not give runtime services DDL privileges.

Sensitive data

Keep tax identifiers, credentials, and unrelated billing secrets out of WorkOrder/technician operational views. Minimize copies into audit and analytics systems.

Auditability

Audit events should capture:

tenantactoractionentityrequest/correlation IDtimereason where needed

Avoid unrestricted full-row sensitive snapshots.

Denormalized read projection

WorkOrderListProjection(  tenant_id,  work_order_id,  work_order_number,  asset_display,  customer_display,  status_display,  opened_at,  source_version)

Use an outbox/event stream and define a freshness SLA, e.g. ≤5 seconds.

Dashboard summary

DailyWorkOrderSummary(  tenant_id,  day,  status_code,  work_order_count)

Derived, rebuildable, and explicitly not the source of truth.

JSON strategy

For DiagnosticCapture:

• store raw/versioned payload;
• keep common fields typed;
• index only proven hot JSON paths;
• promote fields that become business-critical.

Retention plan

Evolution plan: add service_region_id

1. add nullable column;
2. deploy writers;
3. backfill in batches;
4. add/index FK;
5. validate;
6. enforce NOT NULL;
7. remove compatibility code.

Evolution plan: rename concept

Use expand-and-contract for a customer/account rename rather than direct destructive rename during a rolling deployment.

Observability

Monitor:

• p95/p99 query latency;
• lock waits/deadlocks;
• serialization retries;
• replication lag;
• index usage and size;
• projection freshness;
• tenant-isolation policy failures.

Failure scenarios

Test:

1. duplicate RecordPartUsage request;
2. concurrent inventory decrement;
3. two primary assignments concurrently;
4. cache unavailable;
5. projection lagging;
6. backfill worker crash;
7. old/new app versions overlapping.

Checkpoint

Summary and next lesson

The capstone is now hardened around transactions, concurrency, indexes, tenant isolation, least privilege, auditability, projections, retention, and safe evolution. The final lesson tests and documents the design so it can be defended to engineers, stakeholders, and operators.

References

• Course Chapters 10–17.
• Martin Kleppmann, Designing Data-Intensive Applications.
• DBMS documentation on transactions, indexes, roles, and online migrations.