A relational model encodes business facts and impossible states close to the data. Primary keys establish identity, foreign keys preserve references, checks restrict valid values, unique constraints prevent duplicates, and indexes support measured query patterns. Tenant ownership must be represented so every relationship and query can preserve isolation.
Using timestamptz stores an instant; convert to a user's timezone at the presentation boundary. Application code must still update updated_at, or a database trigger must own it consistently.
If a child table contains both organization_id and ticket_id, use a composite foreign key to (organization_id, id) so a row cannot pair tenant A with tenant B's ticket. This is defense in depth; application queries must also carry tenant predicates. PostgreSQL row-level security may add another control when implemented and tested carefully.
An index trades write cost and storage for faster matching and ordering. Design it from the exact query predicate and order. Use representative data and:
Do not run ANALYZE casually against a write statement in production; it executes the statement.
Knowledge documents, chunks, embeddings, AI runs, and citations all require tenant ownership and lineage. A vector similarity result is not authorization. Join or filter through tenant and access metadata before content becomes model context.
Design users, memberships, and ticket_comments. Enforce unique membership per organization and prevent a comment from referencing a ticket in another tenant.
Acceptance criteria: invalid SQL inserts prove every key/check constraint; the ticket-list query uses its intended index on representative data.