Capstone 1: Build Your First Business Database

Build the non-AI core of SignalDesk as a complete, reproducible PostgreSQL database. Starting from business rules, create organizations, users, memberships, customers, tickets, messages, tags, assignments, and audit events; then prove constraints, SQL reports, transactions, tenant ownership, rollback, and restore. The deliverable is evidence and reasoning, not only a schema file.

What will you be able to do?

• Convert requirements into an ERD and invariant catalog.
• Deliver versioned DDL, seed data, queries, and negative tests.
• Demonstrate atomic workflows and tenant-safe relationships.
• Restore the database and validate it independently.

Prerequisites and mental model

Complete Parts 1–5. Work in an isolated database. Treat the capstone as a miniature production delivery with source control, reproducible commands, and a review packet.

Requirements

The system must support:

• users in multiple organizations with membership roles/status;
• tenant-local customers and unique emails;
• tickets with state, priority, assignee, optimistic version, and timestamps;
• ordered messages with customer/agent/system authorship;
• tenant-owned tags and assignments;
• append-only ticket events for assignment and state change;
• deletion/retention behavior documented for every relationship.

Required queries:

1. stable paginated active inbox;
2. one row per ticket with customer, message count, and latest message;
3. tickets without agent response;
4. daily status report in a declared zone;
5. top issue tags without join double-counting.

Build sequence

1. Write row predicates, keys, cardinalities, and forbidden states.
2. Draw ERD and choose type/time/delete semantics.
3. Create numbered forward migrations.
4. Seed two tenants with overlapping labels and adversarial null/tie cases.
5. Write positive and negative constraint tests.
6. Implement queries with expected result fixtures.
7. Demonstrate one atomic assignment workflow and stale-version rejection.
8. Dump and restore into a different database.
9. Produce a concise architecture decision record.

Why does this matter in AI-native systems?

This database becomes the trustworthy substrate for later retrieval and tools. If tenant ownership, event history, state transitions, time, or concurrency is ambiguous here, AI will amplify the ambiguity rather than repair it.

Prove it worked: rubric

Any cross-tenant acceptance is an automatic fail.

Common mistakes

• Schema first, rules later: constraints miss domain → approve invariant catalog first.
• Happy data only: defects remain invisible → seed collisions, ties, nulls, zero/many children.
• Screenshot as evidence: not reproducible → submit commands, queries, outputs, and checksums.

AI Pair-Programmer Prompt

Hands-on exercise

Complete the capstone and ask another person to reproduce it from an empty database.

Acceptance criterion: they can build, test, dump, restore, and explain the system using only submitted artifacts.

Knowledge check

1. What is an automatic capstone failure?
2. Why include overlapping tenant data?
3. What is stronger than a screenshot?

---

Answers

1. Any cross-tenant acceptance/leak.
2. It exposes missing ownership keys and filters.
3. Reproducible commands, fixtures, assertions, outputs, and restore validation.

Completion checklist

• All rubric dimensions pass.
• A second person reproduced the build.
• Tenant-negative tests are explicit.
• Restore evidence is attached.

Primary references

• PostgreSQL SQL language
• Data definition
• Backup and restore