1. Requirements Restatement This PostgreSQL 15 schema supports a multi-tenant legal practice management SaaS for solo attorneys and small firms. Data is isolated by firm, with users, clients, matters, billable time, invoices, document metadata, and audit events all scoped to a tenant. The model prioritizes legal-grade correctness: immutable invoiced time entries, strong foreign keys, explicit lifecycle states, soft deletion for privileged records, encrypted PII payload columns, and auditable access to billable time. Documents are stored externally in S3, while the database stores access metadata used to authorize signed URL generation. The design assumes moderate initial write volume but prepares for later partitioning of time entries and audit logs. Assumptions: Application-layer envelope encryption is used for PII; encrypted values are stored as BYTEA. Tenant isolation is enforced by application authorization plus optional PostgreSQL RLS session settings. Time entries may be attached directly to one invoice through invoice_id. Document access is role-based in the core schema; per-user overrides are deferred. Payments, calendar events, detailed invoice line items, and document versions are deferred. 2. Entity-Relationship Overview Entities firms — tenant/account root. users — firm-scoped lawyers and staff with role-based permissions. clients — firm-owned legal clients with encrypted identifying data. matters — legal cases linked to one client and one primary lawyer. invoices — firm/client/matter billing documents with lifecycle status. time_entries — immutable billable/non-billable work units. documents — S3-backed file metadata with role-based DB access metadata. audit_log — append-only event record for billable-time reads and writes. Relationships Firm 1:N User — ON DELETE RESTRICT Firm 1:N Client — ON DELETE RESTRICT Firm 1:N Matter — ON DELETE RESTRICT Firm 1:N Invoice — ON DELETE RESTRICT Firm 1:N Document — ON DELETE RESTRICT Firm 1:N AuditLog — ON DELETE RESTRICT Client 1:N Matter — ON DELETE RESTRICT Client 1:N Invoice — ON DELETE RESTRICT Matter 1:N TimeEntry — ON DELETE RESTRICT Matter 1:N Invoice — ON DELETE RESTRICT Matter 1:N Document — ON DELETE RESTRICT User 1:N Matter as primary lawyer — ON DELETE RESTRICT User 1:N TimeEntry as worker — ON DELETE RESTRICT Invoice 1:N TimeEntry — ON DELETE RESTRICT ER Diagram +---------+ +---------+ | firms | 1--N | users | +---------+ +---------+ | | | | primary_lawyer_id | v | 1--N +---------+ 1--N +--------------+ +------| clients |----------| matters | | +---------+ +--------------+ | | | | | | | +--N documents | | | | | +--N invoices | | | +--N time_entries | | | N--1 invoices | +--N audit_log 3. Table Definitions firms Purpose: Stores tenant-level account identity, billing status, and firm-wide configuration. Columns: Column Type Nullable Default Notes id UUID No gen_random_uuid() Stable tenant identifier. name TEXT No None Legal or trade name of the firm. slug CITEXT No None Human-readable unique tenant slug. status firm_status No 'active' Account lifecycle state. settings JSONB No '{}'::jsonb Firm-level preferences and feature flags. created_at TIMESTAMPTZ No now() Creation timestamp. updated_at TIMESTAMPTZ No now() Last update timestamp. Primary key: id as UUID; avoids sequence leakage across tenants and is safe for distributed application-generated references. Foreign keys: None. Unique & Check constraints: UNIQUE (slug) CHECK (length(trim(name)) > 0) CHECK (length(trim(slug::text)) >= 3) Audit columns: created_at, updated_at users Purpose: Stores firm-scoped application users, authentication identity, and role used for authorization. Columns: Column Type Nullable Default Notes id UUID No gen_random_uuid() Stable user identifier. firm_id UUID No None Tenant owner. email CITEXT No None Case-insensitive login/contact email. full_name TEXT No None Display name; not encrypted because it is internal staff PII, not client privileged matter PII. role firm_role No None Authorization role. status user_status No 'active' User lifecycle state. last_login_at TIMESTAMPTZ Yes None Most recent successful login. created_at TIMESTAMPTZ No now() Creation timestamp. updated_at TIMESTAMPTZ No now() Last update timestamp. Primary key: id as UUID; user references appear in audit, matters, documents, and time entries. Foreign keys: firm_id REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE Unique & Check constraints: UNIQUE (firm_id, email) CHECK (length(trim(full_name)) > 0) Audit columns: created_at, updated_at clients Purpose: Stores firm-owned client records with encrypted privileged identifying fields and soft-delete lifecycle. Columns: Column Type Nullable Default Notes id UUID No gen_random_uuid() Stable client identifier. firm_id UUID No None Tenant owner. client_number TEXT No None Firm-visible client reference. client_name_enc BYTEA No None Application-encrypted client name. client_email_enc BYTEA Yes None Application-encrypted client email. client_phone_enc BYTEA Yes None Application-encrypted client phone. metadata JSONB No '{}'::jsonb Non-sensitive structured attributes. created_at TIMESTAMPTZ No now() Creation timestamp. updated_at TIMESTAMPTZ No now() Last update timestamp. deleted_at TIMESTAMPTZ Yes None Soft-delete marker. Primary key: id as UUID; suitable for tenant-scoped URLs without exposing row counts. Foreign keys: firm_id REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE Unique & Check constraints: UNIQUE (firm_id, client_number) CHECK (length(trim(client_number)) > 0) CHECK (octet_length(client_name_enc) > 0) Audit columns: created_at, updated_at, deleted_at matters Purpose: Stores legal cases linked to a client and a primary lawyer, with encrypted descriptions and soft deletion. Columns: Column Type Nullable Default Notes id UUID No gen_random_uuid() Stable matter identifier. firm_id UUID No None Tenant owner. client_id UUID No None Owning client. primary_lawyer_id UUID No None Main responsible attorney. matter_number TEXT No None Firm-visible matter reference. title_enc BYTEA No None Application-encrypted matter title. description_enc BYTEA Yes None Application-encrypted privileged description. status matter_status No 'open' Matter lifecycle state. opened_on DATE No CURRENT_DATE Matter open date. closed_on DATE Yes None Matter close date. created_at TIMESTAMPTZ No now() Creation timestamp. updated_at TIMESTAMPTZ No now() Last update timestamp. deleted_at TIMESTAMPTZ Yes None Soft-delete marker. Primary key: id as UUID; supports cross-table references and external-safe identifiers. Foreign keys: firm_id REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE client_id REFERENCES clients(id) ON DELETE RESTRICT ON UPDATE CASCADE primary_lawyer_id REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE Unique & Check constraints: UNIQUE (firm_id, matter_number) CHECK (length(trim(matter_number)) > 0) CHECK (octet_length(title_enc) > 0) CHECK (closed_on IS NULL OR closed_on >= opened_on) CHECK ((status = 'closed' AND closed_on IS NOT NULL) OR status <> 'closed') Audit columns: created_at, updated_at, deleted_at invoices Purpose: Stores invoice headers and monetary totals for time-entry rollups and billing lifecycle. Columns: Column Type Nullable Default Notes id UUID No gen_random_uuid() Stable invoice identifier. firm_id UUID No None Tenant owner. client_id UUID No None Billed client. matter_id UUID No None Billed matter. invoice_number TEXT No None Firm-visible invoice number. status invoice_status No 'draft' Billing lifecycle state. issue_date DATE Yes None Date sent or issued. due_date DATE Yes None Payment due date. currency_code CHAR(3) No 'USD' ISO-style uppercase currency code. subtotal_cents BIGINT No 0 Sum before tax/discount. tax_cents BIGINT No 0 Tax amount. discount_cents BIGINT No 0 Discount amount. total_cents BIGINT No 0 Final amount due. paid_cents BIGINT No 0 Amount paid to date. sent_at TIMESTAMPTZ Yes None Timestamp when sent. paid_at TIMESTAMPTZ Yes None Timestamp when fully paid. created_at TIMESTAMPTZ No now() Creation timestamp. updated_at TIMESTAMPTZ No now() Last update timestamp. Primary key: id as UUID; invoice numbers remain tenant-scoped business identifiers. Foreign keys: firm_id REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE client_id REFERENCES clients(id) ON DELETE RESTRICT ON UPDATE CASCADE matter_id REFERENCES matters(id) ON DELETE RESTRICT ON UPDATE CASCADE Unique & Check constraints: UNIQUE (firm_id, invoice_number) CHECK (length(trim(invoice_number)) > 0) CHECK (currency_code = upper(currency_code) AND currency_code ~ '^[A-Z]{3}$') CHECK (subtotal_cents >= 0 AND tax_cents >= 0 AND discount_cents >= 0 AND total_cents >= 0 AND paid_cents >= 0) CHECK (paid_cents <= total_cents) CHECK (due_date IS NULL OR issue_date IS NULL OR due_date >= issue_date) CHECK ((status IN ('sent','paid','overdue') AND issue_date IS NOT NULL) OR status = 'draft') CHECK ((status = 'paid' AND paid_at IS NOT NULL AND paid_cents = total_cents) OR status <> 'paid') Audit columns: created_at, updated_at time_entries Purpose: Stores billable work units, their monetary calculation basis, and invoice attachment state. Columns: Column Type Nullable Default Notes id UUID No gen_random_uuid() Stable time-entry identifier. firm_id UUID No None Tenant owner. matter_id UUID No None Related matter. user_id UUID No None Lawyer/staff member who performed the work. invoice_id UUID Yes None Invoice once billed; non-null makes entry immutable. work_date DATE No None Date work occurred. started_at TIMESTAMPTZ Yes None Optional precise start time. ended_at TIMESTAMPTZ Yes None Optional precise end time. duration_minutes INTEGER No None Rounded billable duration. billing_rate_cents INTEGER No None Rate per hour in minor units. amount_cents BIGINT No None Stored calculated amount for invoice stability. is_billable BOOLEAN No true Whether entry is billable. description_enc BYTEA No None Application-encrypted privileged time narrative. created_at TIMESTAMPTZ No now() Creation timestamp. updated_at TIMESTAMPTZ No now() Last update timestamp. Primary key: id as UUID; high-volume table can later partition by work_date without changing external references. Foreign keys: firm_id REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE matter_id REFERENCES matters(id) ON DELETE RESTRICT ON UPDATE CASCADE user_id REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE invoice_id REFERENCES invoices(id) ON DELETE RESTRICT ON UPDATE CASCADE Unique & Check constraints: CHECK (duration_minutes > 0 AND duration_minutes <= 1440) CHECK (billing_rate_cents >= 0) CHECK (amount_cents >= 0) CHECK (octet_length(description_enc) > 0) CHECK (ended_at IS NULL OR started_at IS NULL OR ended_at > started_at) Trigger-enforced immutability prevents updating or deleting entries once invoice_id IS NOT NULL. Audit columns: created_at, updated_at documents Purpose: Stores S3 object metadata and role-based access metadata for signed URL authorization. Columns: Column Type Nullable Default Notes id UUID No gen_random_uuid() Stable document identifier. firm_id UUID No None Tenant owner. matter_id UUID No None Related matter. uploaded_by_user_id UUID No None User who uploaded the file. s3_bucket TEXT No None S3 bucket name. s3_key TEXT No None S3 object key. file_name TEXT No None Original/display file name. content_type TEXT No None MIME type. size_bytes BIGINT No None Object size. sha256_hex CHAR(64) No None Content digest for integrity/deduplication. allowed_roles firm_role[] No ARRAY['admin','lawyer']::firm_role[] Roles permitted to request signed URLs. metadata JSONB No '{}'::jsonb Non-sensitive metadata such as tags. created_at TIMESTAMPTZ No now() Creation timestamp. updated_at TIMESTAMPTZ No now() Last update timestamp. deleted_at TIMESTAMPTZ Yes None Soft-delete marker. Primary key: id as UUID; external links should reference the DB id, never raw S3 key alone. Foreign keys: firm_id REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE matter_id REFERENCES matters(id) ON DELETE RESTRICT ON UPDATE CASCADE uploaded_by_user_id REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE Unique & Check constraints: UNIQUE (firm_id, s3_bucket, s3_key) CHECK (length(trim(s3_bucket)) > 0) CHECK (length(trim(s3_key)) > 0) CHECK (length(trim(file_name)) > 0) CHECK (size_bytes > 0) CHECK (sha256_hex ~ '^[0-9a-f]{64}$') CHECK (cardinality(allowed_roles) > 0) Audit columns: created_at, updated_at, deleted_at audit_log Purpose: Stores append-only security and compliance events, especially every time_entries read and write. Columns: Column Type Nullable Default Notes id UUID No gen_random_uuid() Stable audit event identifier. firm_id UUID No None Tenant owner. actor_user_id UUID Yes None User who performed the action; nullable for system events. action audit_action No None Event action category. entity_table TEXT No None Logical table name, e.g. time_entries. entity_id UUID No None Target entity id. occurred_at TIMESTAMPTZ No now() Event timestamp. ip_address INET Yes None Request source IP. user_agent TEXT Yes None Request user agent. request_id UUID Yes None Application request correlation id. metadata JSONB No '{}'::jsonb Event details; avoid raw privileged narrative. Primary key: id as UUID; append-only audit entries are individually addressable for compliance exports. Foreign keys: firm_id REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE actor_user_id REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE Unique & Check constraints: CHECK (length(trim(entity_table)) > 0) Trigger prevents updates and deletes after insert. Application must write time_entry_read events whenever billable time is read. Audit columns: occurred_at is the immutable audit timestamp; no updated_at because rows are append-only. 4. Index Plan Table Index name Columns Type Query it optimizes Trade-off users idx_users_firm_role_status (firm_id, role, status) B-tree User lookup by firm and authorization role. Slight write overhead when roles/status change. clients idx_clients_firm_active_client_number (firm_id, client_number) WHERE deleted_at IS NULL Partial B-tree Active client lookup by firm reference. Excludes deleted clients; restore queries need another path. matters idx_matters_firm_client_active (firm_id, client_id, status) WHERE deleted_at IS NULL Partial B-tree Client matter lists. Larger than a narrow client-only index. matters idx_matters_primary_lawyer_active (firm_id, primary_lawyer_id, status) WHERE deleted_at IS NULL Partial B-tree Lawyer dashboard matter lists. Adds write cost on matter reassignment. invoices idx_invoices_firm_status_due (firm_id, status, due_date) B-tree Overdue/sent invoice queues. Low-cardinality status benefits only with firm and due date. invoices idx_invoices_matter_created (firm_id, matter_id, created_at DESC) B-tree Matter billing history. Extra storage for chronological invoice retrieval. time_entries idx_time_entries_matter_work_date_uninvoiced (firm_id, matter_id, work_date) WHERE invoice_id IS NULL Partial B-tree Draft invoice generation from uninvoiced time. Does not help billed history. time_entries idx_time_entries_invoice (invoice_id) WHERE invoice_id IS NOT NULL Partial B-tree Invoice detail page and rollups. Partial index still grows with billed volume. time_entries idx_time_entries_user_work_date (firm_id, user_id, work_date DESC) B-tree Lawyer time reports. Heavy write table, so index count is intentionally limited. documents idx_documents_matter_active_created (firm_id, matter_id, created_at DESC) WHERE deleted_at IS NULL Partial B-tree Matter document lists. Soft-deleted documents require separate compliance queries. documents idx_documents_allowed_roles_gin (allowed_roles) GIN Role-based document filtering. GIN maintenance overhead on document ACL changes. audit_log idx_audit_log_firm_entity_time (firm_id, entity_table, entity_id, occurred_at DESC) B-tree Audit trail for a time entry or invoice. Audit writes pay for chronological lookup. audit_log idx_audit_log_firm_action_time (firm_id, action, occurred_at DESC) B-tree Compliance report of time-entry reads/writes. Grows quickly; partition later. 5. Migration SQL -- file: 001_init.sql -- PostgreSQL 15 BEGIN; CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE EXTENSION IF NOT EXISTS citext; CREATE TYPE firm_status AS ENUM ('active', 'suspended', 'closed'); CREATE TYPE firm_role AS ENUM ('admin', 'lawyer', 'paralegal', 'staff', 'billing'); CREATE TYPE user_status AS ENUM ('invited', 'active', 'disabled'); CREATE TYPE matter_status AS ENUM ('open', 'on_hold', 'closed'); CREATE TYPE invoice_status AS ENUM ('draft', 'sent', 'paid', 'overdue'); CREATE TYPE audit_action AS ENUM ( 'time_entry_read', 'time_entry_create', 'time_entry_update', 'time_entry_delete_attempt', 'invoice_create', 'invoice_update', 'document_signed_url_create' ); CREATE TABLE firms ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL, slug CITEXT NOT NULL, status firm_status NOT NULL DEFAULT 'active', settings JSONB NOT NULL DEFAULT '{}'::jsonb, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT uq_firms_slug UNIQUE (slug), CONSTRAINT chk_firms_name_not_blank CHECK (length(trim(name)) > 0), CONSTRAINT chk_firms_slug_len CHECK (length(trim(slug::text)) >= 3) ); CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), firm_id UUID NOT NULL, email CITEXT NOT NULL, full_name TEXT NOT NULL, role firm_role NOT NULL, status user_status NOT NULL DEFAULT 'active', last_login_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT fk_users_firm FOREIGN KEY (firm_id) REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT uq_users_firm_email UNIQUE (firm_id, email), CONSTRAINT chk_users_full_name_not_blank CHECK (length(trim(full_name)) > 0) ); CREATE TABLE clients ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), firm_id UUID NOT NULL, client_number TEXT NOT NULL, client_name_enc BYTEA NOT NULL, client_email_enc BYTEA, client_phone_enc BYTEA, metadata JSONB NOT NULL DEFAULT '{}'::jsonb, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), deleted_at TIMESTAMPTZ, CONSTRAINT fk_clients_firm FOREIGN KEY (firm_id) REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT uq_clients_firm_client_number UNIQUE (firm_id, client_number), CONSTRAINT chk_clients_client_number_not_blank CHECK (length(trim(client_number)) > 0), CONSTRAINT chk_clients_name_enc_not_empty CHECK (octet_length(client_name_enc) > 0) ); CREATE TABLE matters ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), firm_id UUID NOT NULL, client_id UUID NOT NULL, primary_lawyer_id UUID NOT NULL, matter_number TEXT NOT NULL, title_enc BYTEA NOT NULL, description_enc BYTEA, status matter_status NOT NULL DEFAULT 'open', opened_on DATE NOT NULL DEFAULT CURRENT_DATE, closed_on DATE, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), deleted_at TIMESTAMPTZ, CONSTRAINT fk_matters_firm FOREIGN KEY (firm_id) REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_matters_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_matters_primary_lawyer FOREIGN KEY (primary_lawyer_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT uq_matters_firm_matter_number UNIQUE (firm_id, matter_number), CONSTRAINT chk_matters_matter_number_not_blank CHECK (length(trim(matter_number)) > 0), CONSTRAINT chk_matters_title_enc_not_empty CHECK (octet_length(title_enc) > 0), CONSTRAINT chk_matters_closed_after_opened CHECK (closed_on IS NULL OR closed_on >= opened_on), CONSTRAINT chk_matters_closed_status_has_date CHECK ( (status = 'closed' AND closed_on IS NOT NULL) OR status <> 'closed' ) ); CREATE TABLE invoices ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), firm_id UUID NOT NULL, client_id UUID NOT NULL, matter_id UUID NOT NULL, invoice_number TEXT NOT NULL, status invoice_status NOT NULL DEFAULT 'draft', issue_date DATE, due_date DATE, currency_code CHAR(3) NOT NULL DEFAULT 'USD', subtotal_cents BIGINT NOT NULL DEFAULT 0, tax_cents BIGINT NOT NULL DEFAULT 0, discount_cents BIGINT NOT NULL DEFAULT 0, total_cents BIGINT NOT NULL DEFAULT 0, paid_cents BIGINT NOT NULL DEFAULT 0, sent_at TIMESTAMPTZ, paid_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT fk_invoices_firm FOREIGN KEY (firm_id) REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_invoices_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_invoices_matter FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT uq_invoices_firm_invoice_number UNIQUE (firm_id, invoice_number), CONSTRAINT chk_invoices_invoice_number_not_blank CHECK (length(trim(invoice_number)) > 0), CONSTRAINT chk_invoices_currency_code CHECK ( currency_code = upper(currency_code) AND currency_code ~ '^[A-Z]{3}$' ), CONSTRAINT chk_invoices_amounts_non_negative CHECK ( subtotal_cents >= 0 AND tax_cents >= 0 AND discount_cents >= 0 AND total_cents >= 0 AND paid_cents >= 0 ), CONSTRAINT chk_invoices_paid_lte_total CHECK (paid_cents <= total_cents), CONSTRAINT chk_invoices_due_after_issue CHECK (due_date IS NULL OR issue_date IS NULL OR due_date >= issue_date), CONSTRAINT chk_invoices_sent_states_have_issue_date CHECK ( (status IN ('sent', 'paid', 'overdue') AND issue_date IS NOT NULL) OR status = 'draft' ), CONSTRAINT chk_invoices_paid_state_complete CHECK ( (status = 'paid' AND paid_at IS NOT NULL AND paid_cents = total_cents) OR status <> 'paid' ) ); CREATE TABLE time_entries ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), firm_id UUID NOT NULL, matter_id UUID NOT NULL, user_id UUID NOT NULL, invoice_id UUID, work_date DATE NOT NULL, started_at TIMESTAMPTZ, ended_at TIMESTAMPTZ, duration_minutes INTEGER NOT NULL, billing_rate_cents INTEGER NOT NULL, amount_cents BIGINT NOT NULL, is_billable BOOLEAN NOT NULL DEFAULT true, description_enc BYTEA NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT fk_time_entries_firm FOREIGN KEY (firm_id) REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_time_entries_matter FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_time_entries_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_time_entries_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT chk_time_entries_duration CHECK (duration_minutes > 0 AND duration_minutes <= 1440), CONSTRAINT chk_time_entries_rate_non_negative CHECK (billing_rate_cents >= 0), CONSTRAINT chk_time_entries_amount_non_negative CHECK (amount_cents >= 0), CONSTRAINT chk_time_entries_description_enc_not_empty CHECK (octet_length(description_enc) > 0), CONSTRAINT chk_time_entries_time_order CHECK (ended_at IS NULL OR started_at IS NULL OR ended_at > started_at) ); CREATE TABLE documents ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), firm_id UUID NOT NULL, matter_id UUID NOT NULL, uploaded_by_user_id UUID NOT NULL, s3_bucket TEXT NOT NULL, s3_key TEXT NOT NULL, file_name TEXT NOT NULL, content_type TEXT NOT NULL, size_bytes BIGINT NOT NULL, sha256_hex CHAR(64) NOT NULL, allowed_roles firm_role[] NOT NULL DEFAULT ARRAY['admin','lawyer']::firm_role[], metadata JSONB NOT NULL DEFAULT '{}'::jsonb, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), deleted_at TIMESTAMPTZ, CONSTRAINT fk_documents_firm FOREIGN KEY (firm_id) REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_documents_matter FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_documents_uploaded_by_user FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT uq_documents_firm_s3_object UNIQUE (firm_id, s3_bucket, s3_key), CONSTRAINT chk_documents_bucket_not_blank CHECK (length(trim(s3_bucket)) > 0), CONSTRAINT chk_documents_key_not_blank CHECK (length(trim(s3_key)) > 0), CONSTRAINT chk_documents_file_name_not_blank CHECK (length(trim(file_name)) > 0), CONSTRAINT chk_documents_size_positive CHECK (size_bytes > 0), CONSTRAINT chk_documents_sha256_hex CHECK (sha256_hex ~ '^[0-9a-f]{64}$'), CONSTRAINT chk_documents_allowed_roles_non_empty CHECK (cardinality(allowed_roles) > 0) ); CREATE TABLE audit_log ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), firm_id UUID NOT NULL, actor_user_id UUID, action audit_action NOT NULL, entity_table TEXT NOT NULL, entity_id UUID NOT NULL, occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(), ip_address INET, user_agent TEXT, request_id UUID, metadata JSONB NOT NULL DEFAULT '{}'::jsonb, CONSTRAINT fk_audit_log_firm FOREIGN KEY (firm_id) REFERENCES firms(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_audit_log_actor_user FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT chk_audit_log_entity_table_not_blank CHECK (length(trim(entity_table)) > 0) ); CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at := now(); RETURN NEW; END; $$; CREATE TRIGGER trg_firms_set_updated_at BEFORE UPDATE ON firms FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER trg_users_set_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER trg_clients_set_updated_at BEFORE UPDATE ON clients FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER trg_matters_set_updated_at BEFORE UPDATE ON matters FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER trg_invoices_set_updated_at BEFORE UPDATE ON invoices FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER trg_time_entries_set_updated_at BEFORE UPDATE ON time_entries FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER trg_documents_set_updated_at BEFORE UPDATE ON documents FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE OR REPLACE FUNCTION prevent_invoiced_time_entry_mutation() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = 'DELETE' THEN IF OLD.invoice_id IS NOT NULL THEN RAISE EXCEPTION 'Cannot delete invoiced time entry %', OLD.id; END IF; RETURN OLD; END IF; IF OLD.invoice_id IS NOT NULL THEN RAISE EXCEPTION 'Cannot update invoiced time entry %', OLD.id; END IF; RETURN NEW; END; $$; CREATE TRIGGER trg_time_entries_prevent_invoiced_update BEFORE UPDATE ON time_entries FOR EACH ROW EXECUTE FUNCTION prevent_invoiced_time_entry_mutation(); CREATE TRIGGER trg_time_entries_prevent_invoiced_delete BEFORE DELETE ON time_entries FOR EACH ROW EXECUTE FUNCTION prevent_invoiced_time_entry_mutation(); CREATE OR REPLACE FUNCTION prevent_audit_log_mutation() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'audit_log is append-only'; END; $$; CREATE TRIGGER trg_audit_log_no_update BEFORE UPDATE ON audit_log FOR EACH ROW EXECUTE FUNCTION prevent_audit_log_mutation(); CREATE TRIGGER trg_audit_log_no_delete BEFORE DELETE ON audit_log FOR EACH ROW EXECUTE FUNCTION prevent_audit_log_mutation(); CREATE OR REPLACE FUNCTION log_time_entry_write() RETURNS TRIGGER LANGUAGE plpgsql AS $$ DECLARE v_actor UUID; v_request UUID; BEGIN v_actor := NULLIF(current_setting('app.current_user_id', true), '')::UUID; v_request := NULLIF(current_setting('app.request_id', true), '')::UUID; IF TG_OP = 'INSERT' THEN INSERT INTO audit_log ( firm_id, actor_user_id, action, entity_table, entity_id, request_id, metadata ) VALUES ( NEW.firm_id, v_actor, 'time_entry_create', 'time_entries', NEW.id, v_request, jsonb_build_object('matter_id', NEW.matter_id, 'invoice_id', NEW.invoice_id) ); RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN INSERT INTO audit_log ( firm_id, actor_user_id, action, entity_table, entity_id, request_id, metadata ) VALUES ( NEW.firm_id, v_actor, 'time_entry_update', 'time_entries', NEW.id, v_request, jsonb_build_object('matter_id', NEW.matter_id, 'invoice_id', NEW.invoice_id) ); RETURN NEW; END IF; RETURN NEW; END; $$; CREATE TRIGGER trg_time_entries_log_write AFTER INSERT OR UPDATE ON time_entries FOR EACH ROW EXECUTE FUNCTION log_time_entry_write(); CREATE OR REPLACE FUNCTION log_time_entry_read( p_firm_id UUID, p_actor_user_id UUID, p_time_entry_id UUID, p_ip_address INET DEFAULT NULL, p_user_agent TEXT DEFAULT NULL, p_request_id UUID DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN INSERT INTO audit_log ( firm_id, actor_user_id, action, entity_table, entity_id, ip_address, user_agent, request_id ) VALUES ( p_firm_id, p_actor_user_id, 'time_entry_read', 'time_entries', p_time_entry_id, p_ip_address, p_user_agent, p_request_id ); END; $$; ALTER TABLE firms ENABLE ROW LEVEL SECURITY; ALTER TABLE users ENABLE ROW LEVEL SECURITY; ALTER TABLE clients ENABLE ROW LEVEL SECURITY; ALTER TABLE matters ENABLE ROW LEVEL SECURITY; ALTER TABLE invoices ENABLE ROW LEVEL SECURITY; ALTER TABLE time_entries ENABLE ROW LEVEL SECURITY; ALTER TABLE documents ENABLE ROW LEVEL SECURITY; ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_firms ON firms USING (id = NULLIF(current_setting('app.current_firm_id', true), '')::UUID); CREATE POLICY tenant_users ON users USING (firm_id = NULLIF(current_setting('app.current_firm_id', true), '')::UUID); CREATE POLICY tenant_clients ON clients USING (firm_id = NULLIF(current_setting('app.current_firm_id', true), '')::UUID); CREATE POLICY tenant_matters ON matters USING (firm_id = NULLIF(current_setting('app.current_firm_id', true), '')::UUID); CREATE POLICY tenant_invoices ON invoices USING (firm_id = NULLIF(current_setting('app.current_firm_id', true), '')::UUID); CREATE POLICY tenant_time_entries ON time_entries USING (firm_id = NULLIF(current_setting('app.current_firm_id', true), '')::UUID); CREATE POLICY tenant_documents ON documents USING ( firm_id = NULLIF(current_setting('app.current_firm_id', true), '')::UUID AND deleted_at IS NULL AND allowed_roles @> ARRAY[NULLIF(current_setting('app.current_role', true), '')::firm_role] ); CREATE POLICY tenant_audit_log ON audit_log USING (firm_id = NULLIF(current_setting('app.current_firm_id', true), '')::UUID); COMMIT; -- file: 002_indexes.sql BEGIN; CREATE INDEX idx_users_firm_role_status ON users (firm_id, role, status); CREATE INDEX idx_clients_firm_active_client_number ON clients (firm_id, client_number) WHERE deleted_at IS NULL; CREATE INDEX idx_matters_firm_client_active ON matters (firm_id, client_id, status) WHERE deleted_at IS NULL; CREATE INDEX idx_matters_primary_lawyer_active ON matters (firm_id, primary_lawyer_id, status) WHERE deleted_at IS NULL; CREATE INDEX idx_invoices_firm_status_due ON invoices (firm_id, status, due_date); CREATE INDEX idx_invoices_matter_created ON invoices (firm_id, matter_id, created_at DESC); CREATE INDEX idx_time_entries_matter_work_date_uninvoiced ON time_entries (firm_id, matter_id, work_date) WHERE invoice_id IS NULL; CREATE INDEX idx_time_entries_invoice ON time_entries (invoice_id) WHERE invoice_id IS NOT NULL; CREATE INDEX idx_time_entries_user_work_date ON time_entries (firm_id, user_id, work_date DESC); CREATE INDEX idx_documents_matter_active_created ON documents (firm_id, matter_id, created_at DESC) WHERE deleted_at IS NULL; CREATE INDEX idx_documents_allowed_roles_gin ON documents USING GIN (allowed_roles); CREATE INDEX idx_audit_log_firm_entity_time ON audit_log (firm_id, entity_table, entity_id, occurred_at DESC); CREATE INDEX idx_audit_log_firm_action_time ON audit_log (firm_id, action, occurred_at DESC); COMMIT; 6. Seed Data -- file: 003_seed.sql BEGIN; SET LOCAL app.current_user_id = ''; SET LOCAL app.request_id = ''; INSERT INTO firms (id, name, slug, status, settings, created_at, updated_at) VALUES ('11111111-1111-1111-1111-111111111111', 'Northstar Legal PLLC', 'northstar-legal', 'active', '{"billing_increment_minutes": 6}'::jsonb, '2026-01-05T09:00:00Z', '2026-01-05T09:00:00Z'), ('22222222-2222-2222-2222-222222222222', 'Harbor & Finch LLP', 'harbor-finch', 'active', '{"billing_increment_minutes": 10}'::jsonb, '2026-01-07T10:00:00Z', '2026-01-07T10:00:00Z'), ('33333333-3333-3333-3333-333333333333', 'Cedar Solo Law', 'cedar-solo', 'active', '{"billing_increment_minutes": 6}'::jsonb, '2026-01-10T11:00:00Z', '2026-01-10T11:00:00Z'); INSERT INTO users (id, firm_id, email, full_name, role, status, last_login_at, created_at, updated_at) VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1', '11111111-1111-1111-1111-111111111111', 'ava.morgan@northstar.example', 'Ava Morgan', 'admin', 'active', '2026-05-01T13:00:00Z', '2026-01-05T09:10:00Z', '2026-05-01T13:00:00Z'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa2', '11111111-1111-1111-1111-111111111111', 'noah.reed@northstar.example', 'Noah Reed', 'lawyer', 'active', '2026-05-02T14:30:00Z', '2026-01-05T09:15:00Z', '2026-05-02T14:30:00Z'), ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb1', '22222222-2222-2222-2222-222222222222', 'mia.chen@harborfinch.example', 'Mia Chen', 'admin', 'active', '2026-05-02T12:00:00Z', '2026-01-07T10:15:00Z', '2026-05-02T12:00:00Z'), ('cccccccc-cccc-cccc-cccc-ccccccccccc1', '33333333-3333-3333-3333-333333333333', 'eli.carter@cedarsolo.example', 'Eli Carter', 'lawyer', 'active', '2026-05-03T09:00:00Z', '2026-01-10T11:15:00Z', '2026-05-03T09:00:00Z'); INSERT INTO clients (id, firm_id, client_number, client_name_enc, client_email_enc, client_phone_enc, metadata, created_at, updated_at, deleted_at) VALUES ('10000000-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111', 'NS-C-0001', decode('a101', 'hex'), decode('a102', 'hex'), decode('a103', 'hex'), '{"type": "individual"}'::jsonb, '2026-01-06T09:00:00Z', '2026-01-06T09:00:00Z', NULL), ('10000000-0000-0000-0000-000000000002', '11111111-1111-1111-1111-111111111111', 'NS-C-0002', decode('a201', 'hex'), decode('a202', 'hex'), NULL, '{"type": "business"}'::jsonb, '2026-01-08T09:00:00Z', '2026-01-08T09:00:00Z', NULL), ('20000000-0000-0000-0000-000000000001', '22222222-2222-2222-2222-222222222222', 'HF-C-0001', decode('b101', 'hex'), decode('b102', 'hex'), NULL, '{"type": "individual"}'::jsonb, '2026-01-09T10:00:00Z', '2026-01-09T10:00:00Z', NULL), ('30000000-0000-0000-0000-000000000001', '33333333-3333-3333-3333-333333333333', 'CS-C-0001', decode('c101', 'hex'), NULL, NULL, '{"type": "business"}'::jsonb, '2026-01-11T11:00:00Z', '2026-01-11T11:00:00Z', NULL); INSERT INTO matters (id, firm_id, client_id, primary_lawyer_id, matter_number, title_enc, description_enc, status, opened_on, closed_on, created_at, updated_at, deleted_at) VALUES ('11000000-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111', '10000000-0000-0000-0000-000000000001', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa2', 'NS-M-2026-0001', decode('d101', 'hex'), decode('d102', 'hex'), 'open', '2026-01-12', NULL, '2026-01-12T09:00:00Z', '2026-01-12T09:00:00Z', NULL), ('11000000-0000-0000-0000-000000000002', '11111111-1111-1111-1111-111111111111', '10000000-0000-0000-0000-000000000002', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1', 'NS-M-2026-0002', decode('d201', 'hex'), decode('d202', 'hex'), 'open', '2026-01-15', NULL, '2026-01-15T09:00:00Z', '2026-01-15T09:00:00Z', NULL), ('22000000-0000-0000-0000-000000000001', '22222222-2222-2222-2222-222222222222', '20000000-0000-0000-0000-000000000001', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb1', 'HF-M-2026-0001', decode('e101', 'hex'), decode('e102', 'hex'), 'open', '2026-01-16', NULL, '2026-01-16T10:00:00Z', '2026-01-16T10:00:00Z', NULL), ('33000000-0000-0000-0000-000000000001', '33333333-3333-3333-3333-333333333333', '30000000-0000-0000-0000-000000000001', 'cccccccc-cccc-cccc-cccc-ccccccccccc1', 'CS-M-2026-0001', decode('f101', 'hex'), NULL, 'open', '2026-01-20', NULL, '2026-01-20T11:00:00Z', '2026-01-20T11:00:00Z', NULL); INSERT INTO invoices (id, firm_id, client_id, matter_id, invoice_number, status, issue_date, due_date, currency_code, subtotal_cents, tax_cents, discount_cents, total_cents, paid_cents, sent_at, paid_at, created_at, updated_at) VALUES ('91000000-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111', '10000000-0000-0000-0000-000000000001', '11000000-0000-0000-0000-000000000001', 'NS-INV-2026-0001', 'sent', '2026-02-01', '2026-03-03', 'USD', 90000, 0, 0, 90000, 0, '2026-02-01T15:00:00Z', NULL, '2026-02-01T14:45:00Z', '2026-02-01T15:00:00Z'), ('91000000-0000-0000-0000-000000000002', '11111111-1111-1111-1111-111111111111', '10000000-0000-0000-0000-000000000002', '11000000-0000-0000-0000-000000000002', 'NS-INV-2026-0002', 'draft', NULL, NULL, 'USD', 45000, 0, 0, 45000, 0, NULL, NULL, '2026-02-10T10:00:00Z', '2026-02-10T10:00:00Z'), ('92000000-0000-0000-0000-000000000001', '22222222-2222-2222-2222-222222222222', '20000000-0000-0000-0000-000000000001', '22000000-0000-0000-0000-000000000001', 'HF-INV-2026-0001', 'paid', '2026-02-05', '2026-03-07', 'USD', 120000, 0, 0, 120000, 120000, '2026-02-05T16:00:00Z', '2026-02-20T13:00:00Z', '2026-02-05T15:40:00Z', '2026-02-20T13:00:00Z'), ('93000000-0000-0000-0000-000000000001', '33333333-3333-3333-3333-333333333333', '30000000-0000-0000-0000-000000000001', '33000000-0000-0000-0000-000000000001', 'CS-INV-2026-0001', 'sent', '2026-02-15', '2026-03-17', 'USD', 30000, 0, 0, 30000, 0, '2026-02-15T12:00:00Z', NULL, '2026-02-15T11:45:00Z', '2026-02-15T12:00:00Z'); INSERT INTO time_entries (id, firm_id, matter_id, user_id, invoice_id, work_date, started_at, ended_at, duration_minutes, billing_rate_cents, amount_cents, is_billable, description_enc, created_at, updated_at) VALUES ('81000000-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111', '11000000-0000-0000-0000-000000000001', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa2', '91000000-0000-0000-0000-000000000001', '2026-01-22', '2026-01-22T14:00:00Z', '2026-01-22T16:00:00Z', 120, 45000, 90000, true, decode('aa01', 'hex'), '2026-01-22T16:05:00Z', '2026-01-22T16:05:00Z'), ('81000000-0000-0000-0000-000000000002', '11111111-1111-1111-1111-111111111111', '11000000-0000-0000-0000-000000000002', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1', NULL, '2026-02-08', '2026-02-08T10:00:00Z', '2026-02-08T11:00:00Z', 60, 45000, 45000, true, decode('aa02', 'hex'), '2026-02-08T11:05:00Z', '2026-02-08T11:05:00Z'), ('82000000-0000-0000-0000-000000000001', '22222222-2222-2222-2222-222222222222', '22000000-0000-0000-0000-000000000001', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb1', '92000000-0000-0000-0000-000000000001', '2026-01-29', '2026-01-29T09:00:00Z', '2026-01-29T12:00:00Z', 180, 40000, 120000, true, decode('bb01', 'hex'), '2026-01-29T12:10:00Z', '2026-01-29T12:10:00Z'), ('83000000-0000-0000-0000-000000000001', '33333333-3333-3333-3333-333333333333', '33000000-0000-0000-0000-000000000001', 'cccccccc-cccc-cccc-cccc-ccccccccccc1', '93000000-0000-0000-0000-000000000001', '2026-02-12', '2026-02-12T13:00:00Z', '2026-02-12T14:00:00Z', 60, 30000, 30000, true, decode('cc01', 'hex'), '2026-02-12T14:05:00Z', '2026-02-12T14:05:00Z'); INSERT INTO documents (id, firm_id, matter_id, uploaded_by_user_id, s3_bucket, s3_key, file_name, content_type, size_bytes, sha256_hex, allowed_roles, metadata, created_at, updated_at, deleted_at) VALUES ('71000000-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111', '11000000-0000-0000-0000-000000000001', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa2', 'legal-prod-documents', 'firms/11111111/matters/NS-M-2026-0001/engagement-letter.pdf', 'engagement-letter.pdf', 'application/pdf', 184320, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', ARRAY['admin','lawyer']::firm_role[], '{"category": "engagement"}'::jsonb, '2026-01-13T10:00:00Z', '2026-01-13T10:00:00Z', NULL), ('71000000-0000-0000-0000-000000000002', '11111111-1111-1111-1111-111111111111', '11000000-0000-0000-0000-000000000002', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1', 'legal-prod-documents', 'firms/11111111/matters/NS-M-2026-0002/draft-agreement.docx', 'draft-agreement.docx', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', 245760, 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', ARRAY['admin','lawyer','paralegal']::firm_role[], '{"category": "draft"}'::jsonb, '2026-01-18T10:00:00Z', '2026-01-18T10:00:00Z', NULL), ('72000000-0000-0000-0000-000000000001', '22222222-2222-2222-2222-222222222222', '22000000-0000-0000-0000-000000000001', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb1', 'legal-prod-documents', 'firms/22222222/matters/HF-M-2026-0001/pleading.pdf', 'pleading.pdf', 'application/pdf', 532480, 'cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc', ARRAY['admin','lawyer']::firm_role[], '{"category": "court_filing"}'::jsonb, '2026-01-21T10:00:00Z', '2026-01-21T10:00:00Z', NULL), ('73000000-0000-0000-0000-000000000001', '33333333-3333-3333-3333-333333333333', '33000000-0000-0000-0000-000000000001', 'cccccccc-cccc-cccc-cccc-ccccccccccc1', 'legal-prod-documents', 'firms/33333333/matters/CS-M-2026-0001/board-consent.pdf', 'board-consent.pdf', 'application/pdf', 98304, 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd', ARRAY['lawyer']::firm_role[], '{"category": "corporate"}'::jsonb, '2026-01-22T10:00:00Z', '2026-01-22T10:00:00Z', NULL); INSERT INTO audit_log (id, firm_id, actor_user_id, action, entity_table, entity_id, occurred_at, ip_address, user_agent, request_id, metadata) VALUES ('61000000-0000-0000-0000-000000000001', '11111111-1111-1111-1111-111111111111', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa2', 'time_entry_read', 'time_entries', '81000000-0000-0000-0000-000000000001', '2026-02-01T15:10:00Z', '203.0.113.10', 'Mozilla/5.0 LegalSaaS', '51000000-0000-0000-0000-000000000001', '{"purpose": "invoice_review"}'::jsonb), ('61000000-0000-0000-0000-000000000002', '11111111-1111-1111-1111-111111111111', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1', 'document_signed_url_create', 'documents', '71000000-0000-0000-0000-000000000002', '2026-02-11T09:30:00Z', '203.0.113.11', 'Mozilla/5.0 LegalSaaS', '51000000-0000-0000-0000-000000000002', '{"expires_in_seconds": 300}'::jsonb), ('62000000-0000-0000-0000-000000000001', '22222222-2222-2222-2222-222222222222', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb1', 'time_entry_read', 'time_entries', '82000000-0000-0000-0000-000000000001', '2026-02-20T13:05:00Z', '198.51.100.20', 'Mozilla/5.0 LegalSaaS', '52000000-0000-0000-0000-000000000001', '{"purpose": "payment_reconciliation"}'::jsonb), ('63000000-0000-0000-0000-000000000001', '33333333-3333-3333-3333-333333333333', 'cccccccc-cccc-cccc-cccc-ccccccccccc1', 'time_entry_read', 'time_entries', '83000000-0000-0000-0000-000000000001', '2026-02-15T12:05:00Z', '192.0.2.30', 'Mozilla/5.0 LegalSaaS', '53000000-0000-0000-0000-000000000001', '{"purpose": "invoice_send"}'::jsonb); COMMIT; 7. Top 5 Query Patterns Query 1 — List active matters for a client SELECT m.id, m.matter_number, m.status, m.opened_on, m.closed_on, m.primary_lawyer_id, u.full_name AS primary_lawyer_name FROM matters AS m JOIN users AS u ON u.id = m.primary_lawyer_id WHERE m.firm_id = $1 AND m.client_id = $2 AND m.deleted_at IS NULL ORDER BY m.opened_on DESC, m.matter_number; Expected plan: Uses idx_matters_firm_client_active to find a small client-scoped set, then primary-key lookups into users. Expected rows are usually 1–50 per client. Risk at scale is sorting many historical matters for very large institutional clients; add (firm_id, client_id, opened_on DESC) if that becomes common. Query 2 — Generate draft invoice candidates from uninvoiced billable time SELECT te.id, te.work_date, te.user_id, te.duration_minutes, te.billing_rate_cents, te.amount_cents, te.description_enc FROM time_entries AS te WHERE te.firm_id = $1 AND te.matter_id = $2 AND te.invoice_id IS NULL AND te.is_billable = true AND te.work_date >= $3 AND te.work_date < $4 ORDER BY te.work_date, te.id; Expected plan: Uses idx_time_entries_matter_work_date_uninvoiced for the firm/matter/date range, filtering is_billable after index retrieval. Expected rows are tens to low thousands for a billing period. Risk at scale is a matter with years of uninvoiced entries; operational alerts should flag aged uninvoiced time. Query 3 — Show invoice detail with attached time entries SELECT i.id AS invoice_id, i.invoice_number, i.status, i.issue_date, i.due_date, i.total_cents, i.paid_cents, te.id AS time_entry_id, te.work_date, te.user_id, te.duration_minutes, te.amount_cents, te.description_enc FROM invoices AS i JOIN time_entries AS te ON te.invoice_id = i.id WHERE i.firm_id = $1 AND i.id = $2 ORDER BY te.work_date, te.id; Expected plan: Uses the invoice primary key, then idx_time_entries_invoice for attached entries. Expected rows are typically 1–500 per invoice. Risk at scale is extremely large consolidated invoices; introduce invoice line items if invoices routinely include thousands of entries. Query 4 — List accessible documents for a matter and role SELECT d.id, d.file_name, d.content_type, d.size_bytes, d.sha256_hex, d.created_at, d.metadata FROM documents AS d WHERE d.firm_id = $1 AND d.matter_id = $2 AND d.deleted_at IS NULL AND d.allowed_roles @> ARRAY[$3::firm_role] ORDER BY d.created_at DESC LIMIT 100; Expected plan: Uses idx_documents_matter_active_created to fetch recent matter documents, then filters by allowed_roles; the GIN index is more useful when searching by role across many matters. Expected rows are usually under 100 per matter page. Risk at scale is high document volume per matter; add cursor pagination on (created_at, id). Query 5 — Compliance audit trail for billable-time access SELECT al.id, al.occurred_at, al.actor_user_id, u.full_name AS actor_name, al.action, al.entity_id AS time_entry_id, al.ip_address, al.request_id, al.metadata FROM audit_log AS al LEFT JOIN users AS u ON u.id = al.actor_user_id WHERE al.firm_id = $1 AND al.action IN ('time_entry_read', 'time_entry_create', 'time_entry_update', 'time_entry_delete_attempt') AND al.occurred_at >= $2 AND al.occurred_at < $3 ORDER BY al.occurred_at DESC LIMIT 500; Expected plan: Uses idx_audit_log_firm_action_time, potentially once per action value with bitmap combination or repeated index scans. Expected rows vary from hundreds to millions depending on the reporting window. Risk at scale is audit volume; monthly partitioning by occurred_at should begin before compliance exports become slow. 8. Scaling & Optimization Notes Partitioning: Partition audit_log monthly by occurred_at once it exceeds 50 million rows or compliance reports exceed 2 seconds. Partition time_entries yearly, or monthly for high-growth firms, by work_date once the table exceeds 100 million rows or vacuum pressure becomes visible. Archival / soft-delete: Keep time_entries and invoices online for at least 7 years; after that, move closed-period partitions to lower-cost storage or a separate archive database. Soft-deleted clients, matters, and documents remain queryable for compliance by privileged admin tooling but are excluded from normal indexes and product screens. Read replicas / caching: Route dashboard summaries, invoice history, document lists, and audit reports to the read replica when read-after-write consistency is not required. Keep invoice creation, time-entry mutation, and signed URL authorization on the primary. Cache signed URL authorization decisions only for very short TTLs, preferably less than the signed URL expiry. Cache firm settings and active user role/session claims outside PostgreSQL, but always re-check privileged document/time access on sensitive operations. Denormalization candidates at scale: matters.unbilled_amount_cents and matters.unbilled_minutes maintained asynchronously for billing dashboards. clients.active_matter_count for client list screens. invoices.balance_due_cents if payment volume grows and payment application becomes complex. 9. Anti-Patterns Deliberately Avoided Storing privileged client names and matter narratives as plaintext searchable columns. Using tenant-specific schemas or databases for small firms, which would complicate migrations and connection pooling prematurely. Hard-deleting clients, matters, documents, invoices, or time entries in a legally sensitive system. Making invoice totals depend only on live time-entry recalculation after issuance. Using generic TEXT status fields instead of constrained PostgreSQL enum types. 10. Deferred Tables payments — payment transactions, allocation to invoices, refunds, and reconciliation metadata. invoice_line_items — explicit invoice presentation lines if invoices later include fees, expenses, discounts, or grouped time. calendar_events — firm/user/matter-linked events with recurrence and external calendar sync. document_acl — per-user or per-group document access overrides beyond role-based access. document_versions — immutable S3 object version history and replacement tracking. tasks — matter-linked deadlines, assignments, and reminders. contacts — additional client contacts and opposing counsel records. trust_account_ledger — client trust/IOLTA accounting ledger entries.