-- ============================================================ -- DRE Customer Portal — SQLite Schema (spec §1) -- DB file: /opt/dre-portal/data/dre.db -- Pragmas (set on every connection): foreign_keys=ON, journal_mode=WAL, busy_timeout=5000 -- All timestamps ISO-8601 UTC TEXT. Money as INTEGER cents. PKs TEXT UUID4. -- ============================================================ CREATE TABLE IF NOT EXISTS clients ( id TEXT PRIMARY KEY, -- uuid4 client_number TEXT UNIQUE NOT NULL, -- CLT-YYYY-NNNN company_name TEXT NOT NULL, contact_name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, -- lowercased; magic-link identity phone TEXT, tos_accepted_at TEXT, -- set when ToS accepted at intake twentycrm_id TEXT, -- nullable; set by future CRM sync created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_clients_email ON clients(email); CREATE TABLE IF NOT EXISTS debtors ( id TEXT PRIMARY KEY, -- uuid4 name TEXT NOT NULL, -- business or individual name business_type TEXT NOT NULL DEFAULT 'OTHER' CHECK (business_type IN ('INDIVIDUAL','SOLE_PROPRIETORSHIP','LLC','CORPORATION','PARTNERSHIP','OTHER')), contact_email TEXT, contact_phone TEXT, physical_address TEXT, -- free-text single line twentycrm_id TEXT, -- nullable created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS claims ( id TEXT PRIMARY KEY, -- uuid4 claim_number TEXT UNIQUE NOT NULL, -- DRE-YYYY-NNNN client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE RESTRICT, debtor_id TEXT NOT NULL REFERENCES debtors(id) ON DELETE RESTRICT, amount_cents INTEGER NOT NULL CHECK (amount_cents > 0), currency TEXT NOT NULL DEFAULT 'USD', status TEXT NOT NULL DEFAULT 'NEW' CHECK (status IN ('NEW','UNDER_REVIEW','ACTIVE','NEGOTIATION','LEGAL','SETTLED','CLOSED','WRITE_OFF','REJECTED')), tier TEXT NOT NULL DEFAULT 'TIER_1' CHECK (tier IN ('TIER_1','TIER_2','TIER_2_5','TIER_3','TIER_4')), description TEXT, client_reference TEXT, invoice_date TEXT, -- ISO date date_assigned TEXT, -- set when moved out of NEW date_resolved TEXT, -- set on SETTLED/CLOSED/WRITE_OFF -- Client signed representations at intake (added via db._migrate on existing DBs) no_other_agency_at TEXT, -- confirmed no other agency/attorney engaged no_prior_action_at TEXT, -- confirmed no prior litigation/judgment/bankruptcy -- AI analysis + approval (added via db._migrate on existing DBs) analysis_score INTEGER, -- 0-100, NULL until analyzed analysis_summary TEXT, -- plain-English narrative analysis_components TEXT, -- JSON breakdown of component scores analysis_at TEXT, -- ISO timestamp of analysis recommended_tier TEXT, -- AI-recommended starting tier approval_status TEXT NOT NULL DEFAULT 'NONE' CHECK (approval_status IN ('NONE','PENDING','APPROVED','REJECTED')), approval_decision_by TEXT, -- staff name who approved/rejected approval_decision_at TEXT, -- AI-recommended letter (staff-customizable) letter_subject TEXT, letter_body TEXT, letter_tier TEXT, -- tier the letter was generated for letter_updated_at TEXT, twentycrm_id TEXT, -- nullable created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_claims_client ON claims(client_id); CREATE INDEX IF NOT EXISTS idx_claims_status ON claims(status); CREATE INDEX IF NOT EXISTS idx_claims_debtor ON claims(debtor_id); CREATE TABLE IF NOT EXISTS documents ( id TEXT PRIMARY KEY, -- uuid4 claim_id TEXT NOT NULL REFERENCES claims(id) ON DELETE CASCADE, original_name TEXT NOT NULL, -- sanitized display name stored_path TEXT NOT NULL, -- absolute path on disk (uuid-named) mime_type TEXT NOT NULL, size_bytes INTEGER NOT NULL, sha256 TEXT NOT NULL, -- integrity + dedupe uploaded_by TEXT NOT NULL DEFAULT 'CLIENT' -- CLIENT | STAFF CHECK (uploaded_by IN ('CLIENT','STAFF')), twentycrm_id TEXT, -- nullable created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_documents_claim ON documents(claim_id); CREATE TABLE IF NOT EXISTS case_notes ( id TEXT PRIMARY KEY, -- uuid4 claim_id TEXT NOT NULL REFERENCES claims(id) ON DELETE CASCADE, author_type TEXT NOT NULL CHECK (author_type IN ('CLIENT','STAFF','SYSTEM')), author_name TEXT NOT NULL, subject TEXT, -- for client->team structured messages content TEXT NOT NULL, -- plaintext; rendered escaped visibility TEXT NOT NULL DEFAULT 'SHARED' CHECK (visibility IN ('SHARED','INTERNAL')), twentycrm_id TEXT, -- nullable created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_notes_claim ON case_notes(claim_id); CREATE TABLE IF NOT EXISTS auth_tokens ( id TEXT PRIMARY KEY, -- uuid4 client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE, token_hash TEXT UNIQUE NOT NULL, -- sha256 of raw token expires_at TEXT NOT NULL, -- created_at + 15 min consumed_at TEXT, -- NULL = unused requested_ip TEXT, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_tokens_hash ON auth_tokens(token_hash); CREATE INDEX IF NOT EXISTS idx_tokens_client ON auth_tokens(client_id); CREATE TABLE IF NOT EXISTS sessions ( id TEXT PRIMARY KEY, -- uuid4 client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE, session_hash TEXT UNIQUE NOT NULL, -- sha256 of raw session token expires_at TEXT NOT NULL, -- + 7 days revoked_at TEXT, created_at TEXT NOT NULL, last_seen_at TEXT ); CREATE INDEX IF NOT EXISTS idx_sessions_hash ON sessions(session_hash); CREATE TABLE IF NOT EXISTS audit_log ( id TEXT PRIMARY KEY, -- uuid4 entity_type TEXT NOT NULL, -- 'claim'|'client'|'document'|'note' entity_id TEXT NOT NULL, action TEXT NOT NULL, -- 'create'|'status_change'|'update'|'upload'|'note_add' field TEXT, old_value TEXT, new_value TEXT, actor TEXT NOT NULL, reason TEXT, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_audit_entity ON audit_log(entity_type, entity_id); CREATE TABLE IF NOT EXISTS number_sequences ( prefix TEXT NOT NULL, -- 'DRE'|'CLT' year INTEGER NOT NULL, last_value INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (prefix, year) ); -- Migration tracking table CREATE TABLE IF NOT EXISTS schema_migrations ( version TEXT PRIMARY KEY, applied_at TEXT NOT NULL ); -- Welcome-packet field overrides: client's final value per profile field, -- merged over pre-populated submission values at render time. CREATE TABLE IF NOT EXISTS packet_field_overrides ( id TEXT PRIMARY KEY, claim_id TEXT NOT NULL REFERENCES claims(id) ON DELETE CASCADE, profile_key TEXT NOT NULL, value TEXT, updated_by TEXT NOT NULL DEFAULT 'CLIENT' CHECK (updated_by IN ('CLIENT','STAFF')), created_at TEXT NOT NULL, updated_at TEXT NOT NULL, UNIQUE(claim_id, profile_key) ); CREATE INDEX IF NOT EXISTS idx_packet_overrides_claim ON packet_field_overrides(claim_id); -- Onboarding paperwork receipt tracking. One row per welcome-packet document -- per claim; staff mark each doc "received" once the client returns it. CREATE TABLE IF NOT EXISTS onboarding_docs ( id TEXT PRIMARY KEY, claim_id TEXT NOT NULL REFERENCES claims(id) ON DELETE CASCADE, doc_key TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','RECEIVED')), received_by TEXT, received_at TEXT, created_at TEXT NOT NULL, UNIQUE(claim_id, doc_key) ); CREATE INDEX IF NOT EXISTS idx_onboarding_claim ON onboarding_docs(claim_id); -- Physical mail queue (LetterStream). Lifecycle: DRAFT -> APPROVED -> PREAUTH -> SENT -- (+ REJECTED / CANCELLED / ERROR). One row per mailed letter. CREATE TABLE IF NOT EXISTS letters ( id TEXT PRIMARY KEY, -- uuid4 claim_id TEXT NOT NULL REFERENCES claims(id) ON DELETE CASCADE, letter_type TEXT NOT NULL DEFAULT 'demand', subject TEXT, body TEXT, -- rendered letter body snapshot recipient_json TEXT NOT NULL, -- structured {name,name2,addr1,addr2,city,state,zip} sender_json TEXT NOT NULL, -- structured {name,addr1,addr2,city,state,zip} mailtype TEXT NOT NULL DEFAULT 'firstclass', status TEXT NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT','APPROVED','PREAUTH','SENT','REJECTED','CANCELLED','ERROR')), job_id TEXT, -- LetterStream unique job name batch_id TEXT, -- LetterStream batch id doc_id TEXT, -- LetterStream doc id tracking_no TEXT, -- USPS tracking (certified mail) cost_cents INTEGER, -- pricing from preauth/response authcode TEXT, -- preauth authcode (release via doauth) pages INTEGER, pdf_path TEXT, -- rendered PDF on disk error TEXT, note TEXT, -- reject/cancel reason (terminal states) created_at TEXT NOT NULL, updated_at TEXT NOT NULL, sent_at TEXT ); CREATE INDEX IF NOT EXISTS idx_letters_claim ON letters(claim_id); CREATE INDEX IF NOT EXISTS idx_letters_status ON letters(status); CREATE INDEX IF NOT EXISTS idx_letters_doc ON letters(doc_id); -- LetterStream tracking scan events (pushed via callback every 4h). CREATE TABLE IF NOT EXISTS letter_events ( id TEXT PRIMARY KEY, letter_id TEXT NOT NULL REFERENCES letters(id) ON DELETE CASCADE, scan_code TEXT, scan_status TEXT, scan_date TEXT, scan_zip TEXT, scan_facility TEXT, tracking_id TEXT, batch_id TEXT, job_id TEXT, doc_id TEXT, raw_json TEXT, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_letter_events_letter ON letter_events(letter_id);