Skip to content

Data Model & PostgreSQL Schema

Field Value
Document ID DA-DATA-001
Pillar Data
Standard Alignment DAMA-DMBOK
Status Active / Synchronized with Codebase
Last Updated 2026-11-21
Source of Truth app/schema.sql, app/db.py, app/retrieval.py, app/knowledge_authority.py, app/cases.py, app/ticket_flow.py, app/handoff.py, app/documents.py, app/knowledge_intake.py, app/broadcasts.py, app/ticket_notifications.py, app/topic_categorization.py, app/worker.py

1. Database Architecture & Migration Model

Section titled “1. Database Architecture & Migration Model”

Dirman Assist uses PostgreSQL 17 (postgres:17-alpine) with the pg_trgm extension enabled. There is no external ORM or separate migration binary:

  • Idempotent DDL Bootstrapping: app/db.py:migrate() acquires transaction advisory lock 902720 (pg_advisory_xact_lock(902720)) and executes app/schema.sql (CREATE TABLE IF NOT EXISTS, ALTER TABLE <table> ADD COLUMN IF NOT EXISTS, CREATE INDEX IF NOT EXISTS).
  • Additive Backward-Compatible Evolution: Every schema expansion appends idempotent statements to app/schema.sql so automatic deployment rollbacks can restore earlier application code without destructive schema down-migrations.
  • Total Persisted Relations: 53 tables across 7 logical domains.

2. Entity-Relationship Diagram (Core Domains)

Section titled “2. Entity-Relationship Diagram (Core Domains)”
erDiagram
    members ||--o{ credentials : "holds API keys"
    members ||--o| dashboard_accounts : "admin login"
    dashboard_accounts ||--o{ dashboard_sessions : "active sessions"
    dashboard_accounts ||--o{ password_resets : "single-use tokens"
    members ||--o{ management_events : "audits"

    members ||--o{ jobs : "submits"
    members ||--o{ conversation_turns : "short-term memory"
    members ||--o{ support_cases : "opens"
    support_cases ||--o{ case_messages : "archives 30d"
    support_cases ||--o{ case_notes : "internal notes"
    support_cases ||--o{ staff_replies : "human outbox"
    support_cases ||--o{ tickets : "escalates to"

    members ||--o{ tickets : "owns / assigned"
    tickets ||--o{ ticket_events : "audit trail"
    tickets ||--o{ ticket_open_events : "triggers"
    ticket_open_events ||--o{ ticket_notifications : "fans out"
    members ||--o| admin_duty : "opts in"

    knowledge_documents ||--o{ faq : "contains sections"
    knowledge_documents ||--o| knowledge_documents : "replaces"
    faq ||--o{ knowledge_relations : "left/right governance"
    faq ||--o| knowledge_topics : "AI content labels"
    faq ||--o{ knowledge_checks : "expected evidence"
    knowledge_submissions }o--o| faq : "publishes result"
    knowledge_submissions ||--o{ knowledge_intake_events : "logs"

    ai_config_revisions ||--o| ai_configuration : "active pointer"
    members ||--o{ ai_config_tests : "dry-run sandbox"

    members ||--o| broadcast_subscriptions : "opt-in state"
    broadcast_campaigns ||--o{ broadcast_audience : "targets"
    broadcast_campaigns ||--o{ broadcast_runs : "executes"
    broadcast_runs ||--o{ broadcast_deliveries : "at-most-once sends"

3.1 Identity, Authentication & Governance (9 Tables)

Section titled “3.1 Identity, Authentication & Governance (9 Tables)”
Table Primary Key Key Columns, Foreign Keys & Constraints Indexes
members id uuid name text, phone text UNIQUE, email text, role text CHECK('member','support','admin'), active boolean DEFAULT true, name_source text DEFAULT 'admin' UNIQUE(phone)
credentials hash text member_id uuid FK->members.id, scopes text[], revoked boolean DEFAULT false PK on SHA-256 hash
dashboard_accounts id uuid member_id uuid FK->members.id, username text UNIQUE, password_hash text (scrypt), enabled boolean DEFAULT true, created_at timestamptz UNIQUE dashboard_accounts_member(member_id)
dashboard_sessions hash text account_id uuid FK->dashboard_accounts.id, csrf_hash text, expires_at timestamptz, created_at timestamptz PK on SHA-256 hash
dashboard_login_attempts id bigint IDENTITY ip_hash text, username text, created_at timestamptz PK
password_resets hash text account_id uuid FK->dashboard_accounts.id ON DELETE CASCADE, expires_at timestamptz, created_at timestamptz password_resets_account(account_id)
management_events id bigint IDENTITY member_id uuid FK->members.id, action text, target text, created_at timestamptz PK
request_log — member_id uuid FK->members.id, created_at timestamptz DEFAULT now() request_log_time(member_id, created_at)
schema_version version integer Seeded with version = 1 PK

3.2 Conversational Queue, Cases, Handoff & CRM (8 Tables)

Section titled “3.2 Conversational Queue, Cases, Handoff & CRM (8 Tables)”
Table Primary Key Key Columns, Foreign Keys & Constraints Indexes
jobs id text member_id uuid FK->members.id, question text, state text DEFAULT 'queued' CHECK('queued','processing','done','failed'), answer text, attempts integer DEFAULT 0, has_media boolean DEFAULT false, media_url text, media_mime text, created_at timestamptz, started_at timestamptz, ready_at timestamptz, merged_into text jobs_queue(state, created_at), jobs_member_queue(member_id, created_at, id) WHERE state IN ('queued','processing')
conversation_turns id bigint IDENTITY job_id text UNIQUE, member_id uuid FK->members.id, question text, answer text, expires_at timestamptz, created_at timestamptz conversation_turns_member(member_id, id DESC), conversation_turns_expiry(expires_at)
support_cases id bigint IDENTITY member_id uuid FK->members.id, subject text, status text DEFAULT 'open' CHECK('open','closed'), handoff_active boolean DEFAULT false, handoff_at timestamptz, handoff_job text, handoff_due_at timestamptz, first_staff_reply_at timestamptz, staff_owner uuid FK->members.id, bot_resumed_at timestamptz, priority text DEFAULT 'normal' CHECK('low','normal','high','urgent'), category text DEFAULT 'general' CHECK('general','account','app','agenda','assignment','membership','other'), tags jsonb DEFAULT '[]', created_at timestamptz, updated_at timestamptz, expires_at timestamptz DEFAULT now()+30d support_cases_member(member_id, id DESC), support_cases_open_idle(updated_at) WHERE status='open'
case_messages id bigint IDENTITY case_id bigint FK->support_cases.id ON DELETE CASCADE, job_id text UNIQUE, question text, answer text, has_media boolean DEFAULT false, media_bytes bytea, media_mime text, reply_state text DEFAULT 'pending', created_at timestamptz, expires_at timestamptz DEFAULT now()+30d case_messages_case(case_id, id)
case_notes id bigint IDENTITY case_id bigint FK->support_cases.id ON DELETE CASCADE, author_id uuid FK->members.id, body text, created_at timestamptz case_notes_case(case_id, created_at)
staff_replies id uuid case_id bigint FK->support_cases.id ON DELETE CASCADE, staff_id uuid FK->members.id (nullable), body text, state text DEFAULT 'queued', kind text DEFAULT 'staff', handoff_cycle timestamptz, whatsapp_message_id text, created_at timestamptz, sent_at timestamptz staff_replies_queue(created_at) WHERE state='queued', staff_replies_case(case_id, created_at), UNIQUE staff_replies_whatsapp_message(whatsapp_message_id), UNIQUE staff_replies_handoff_notice(case_id, handoff_cycle, kind) WHERE handoff_cycle IS NOT NULL
canned_responses id bigint IDENTITY shortcut text UNIQUE, title text, body text, category text DEFAULT 'general', created_by uuid FK->members.id, updated_at timestamptz UNIQUE(shortcut)
waha_sent_messages id text created_at timestamptz DEFAULT now() (API outbound echo suppression cache) PK

3.3 Ticketing & On-Duty Admin Notifications (7 Tables)

Section titled “3.3 Ticketing & On-Duty Admin Notifications (7 Tables)”
Table Primary Key Key Columns, Foreign Keys & Constraints Indexes
tickets id bigint IDENTITY owner_id uuid FK->members.id, subject text, description text, status text DEFAULT 'open' CHECK('open','in_progress','resolved','closed'), priority text DEFAULT 'normal' CHECK('low','normal','high','urgent'), category text DEFAULT 'general' CHECK('general','account','app','agenda','assignment','membership','other'), assigned_to uuid FK->members.id, due_at timestamptz, version integer DEFAULT 1, source_job_id text, case_id bigint FK->support_cases.id ON DELETE SET NULL, created_at timestamptz, updated_at timestamptz tickets_owner(owner_id), UNIQUE tickets_source_job(source_job_id), tickets_workload(assigned_to, status, due_at)
ticket_events id bigint IDENTITY ticket_id bigint FK->tickets.id, actor_id uuid FK->members.id, detail jsonb, created_at timestamptz ticket_events_ticket_time(ticket_id, created_at)
ticket_conversations member_id uuid member_id uuid FK->members.id, issue text, state text CHECK('offer','need_details'), expires_at timestamptz PK
admin_duty member_id uuid member_id uuid FK->members.id, phone text, on_duty boolean DEFAULT false, updated_at timestamptz PK
ticket_open_events id bigint IDENTITY ticket_id bigint FK->tickets.id, ticket_version integer, reopened boolean DEFAULT false, created_at timestamptz, UNIQUE(ticket_id, ticket_version) ticket_open_events_ticket(ticket_id, id DESC)
ticket_notifications id bigint IDENTITY event_id bigint FK->ticket_open_events.id, admin_id uuid FK->members.id, phone text, state text DEFAULT 'pending' CHECK('pending','sending','sent','unconfirmed','cancelled'), created_at timestamptz, attempted_at timestamptz, sent_at timestamptz, reason text, UNIQUE(event_id, admin_id) ticket_notifications_queue(state, id)
ticket_notification_gate id integer CHECK(id=1) next_at timestamptz (singleton 5-second shared send gate) PK

3.4 Knowledge Base, PDFs, Intake, Relations & Topic Graph (12 Tables)

Section titled “3.4 Knowledge Base, PDFs, Intake, Relations & Topic Graph (12 Tables)”
Table Primary Key Key Columns, Foreign Keys & Constraints Indexes
faq id bigint IDENTITY question text, answer text, source text, aliases text DEFAULT '', approved boolean DEFAULT false, category text DEFAULT 'faq', expires_at timestamptz, document_id uuid FK->knowledge_documents.id ON DELETE CASCADE, page_start integer, page_end integer, source_case_id bigint FK->support_cases.id ON DELETE SET NULL, source_document_id uuid FK->knowledge_documents.id ON DELETE SET NULL, source_page integer, search_vector tsvector GENERATED, search_label text GENERATED, updated_at timestamptz faq_search_vector GIN(search_vector), faq_search_trigram GIN(search_label gin_trgm_ops), faq_document(document_id)
knowledge_documents id uuid title text, filename text, content bytea, sha256 text UNIQUE, category text DEFAULT 'faq', visibility text DEFAULT 'public' CHECK('public','internal'), auto_publish boolean DEFAULT false, status text DEFAULT 'queued' CHECK('queued','processing','review','published','failed','retired'), pages integer DEFAULT 0, processed_pages integer DEFAULT 0, warnings jsonb DEFAULT '[]', error text, version integer DEFAULT 1, replaces uuid FK->knowledge_documents.id ON DELETE SET NULL, created_by uuid FK->members.id, created_at timestamptz, updated_at timestamptz, expires_at timestamptz knowledge_documents_queue(status, created_at)
knowledge_relations (left_id, right_id) left_id bigint FK->faq.id, right_id bigint FK->faq.id (CHECK(left_id < right_id)), decision text CHECK('prefer','replaces','coexists','conflict'), preferred_id bigint FK->faq.id, left_revision text, right_revision text, left_scope text DEFAULT '', right_scope text DEFAULT '', reason text, updated_by uuid FK->members.id, updated_at timestamptz knowledge_relations_right(right_id)
knowledge_decision_events id bigint IDENTITY left_id bigint, right_id bigint, actor_id uuid FK->members.id, detail jsonb, created_at timestamptz PK
knowledge_checks id bigint IDENTITY question text, context text DEFAULT '', expected_document_id uuid FK->knowledge_documents.id ON DELETE SET NULL, expected_page integer, expected_faq_id bigint FK->faq.id ON DELETE SET NULL, expectation text CHECK('pdf','faq','none'), created_by uuid FK->members.id, updated_at timestamptz PK
knowledge_contributors phone text name text, enabled boolean DEFAULT true, auto_publish boolean DEFAULT true, updated_by uuid FK->members.id, updated_at timestamptz PK
knowledge_submissions id bigint IDENTITY source_job_id text UNIQUE, sender_id uuid FK->members.id, sender_phone text, sender_name text, kind text CHECK('new','addendum'), target_id bigint, target_revision text, title text, body text, forwarded boolean DEFAULT false, status text DEFAULT 'pending' CHECK('pending','approved','rejected'), result_id bigint FK->faq.id ON DELETE SET NULL, created_at timestamptz, reviewed_at timestamptz, reviewed_by uuid FK->members.id, review_note text UNIQUE(source_job_id)
knowledge_intake_events id bigint IDENTITY submission_id bigint FK->knowledge_submissions.id, actor_id uuid FK->members.id, action text, detail jsonb, created_at timestamptz PK
knowledge_intake_pending member_id uuid member_id uuid FK->members.id, phone text, origin_job_id text, data jsonb, expires_at timestamptz PK
knowledge_intake_receipts source_job_id text member_id uuid FK->members.id, response text, created_at timestamptz PK
knowledge_topics article_id bigint article_id bigint FK->faq.id ON DELETE CASCADE, revision text, topics jsonb, model text, updated_at timestamptz PK
knowledge_topic_runs id bigint IDENTITY started_at timestamptz, model text, state text DEFAULT 'attempted', estimated_tokens integer PK

3.5 AI Configuration, Quotas & Fallback Telemetry (8 Tables)

Section titled “3.5 AI Configuration, Quotas & Fallback Telemetry (8 Tables)”
Table Primary Key Key Columns, Foreign Keys & Constraints Indexes
ai_config_revisions id bigint IDENTITY config jsonb, created_by uuid FK->members.id, label text, created_at timestamptz PK
ai_configuration id integer CHECK(id=1) revision_id bigint FK->ai_config_revisions.id (singleton active pointer) PK
ai_config_tests id uuid member_id uuid FK->members.id, config jsonb, question text, state text DEFAULT 'queued' CHECK('queued','done','failed'), result jsonb, use_knowledge boolean DEFAULT false, knowledge_preview jsonb, created_at timestamptz, expires_at timestamptz DEFAULT now()+1h ai_config_tests_queue(state, created_at)
budgets bucket text used integer (minute:YYYYMMDDHHMM, day:YYYYMMDD Pacific) PK
provider_cooldowns model text until_at timestamptz, model_specific boolean DEFAULT false PK
ai_usage — model text, started_at timestamptz, status integer, input_tokens integer, fallback boolean ai_usage_started_at(started_at)
ai_quota_limits model text rpm integer, tpm integer, rpd integer, checked_at timestamptz PK
ai_provider_status provider text configured boolean DEFAULT false, checked_at timestamptz PK
Table Primary Key Key Columns, Foreign Keys & Constraints Indexes
broadcast_subscriptions member_id uuid member_id uuid FK->members.id, phone text, enabled boolean DEFAULT false, evidence text, changed_by uuid FK->members.id, updated_at timestamptz DEFAULT clock_timestamp() PK
broadcast_consent_events id bigint IDENTITY member_id uuid FK->members.id, enabled boolean, evidence text, actor_id uuid FK->members.id, source_event text UNIQUE, created_at timestamptz DEFAULT clock_timestamp() UNIQUE(source_event)
broadcast_campaigns id uuid title text, body text, schedule text CHECK('manual','once','daily','weekly'), starts_at timestamptz, ends_at timestamptz, next_at timestamptz, status text DEFAULT 'draft' CHECK('draft','active','paused','completed','cancelled'), pause_reason text, version integer DEFAULT 1, created_by uuid FK->members.id, created_at timestamptz, updated_at timestamptz PK
broadcast_audience (campaign_id, member_id) campaign_id uuid FK->broadcast_campaigns.id ON DELETE CASCADE, member_id uuid FK->members.id, phone text PK
broadcast_runs id uuid campaign_id uuid FK->broadcast_campaigns.id, scheduled_for timestamptz, expires_at timestamptz, status text DEFAULT 'active', created_at timestamptz, UNIQUE(campaign_id, scheduled_for) PK
broadcast_deliveries id bigint IDENTITY run_id uuid FK->broadcast_runs.id, member_id uuid FK->members.id, phone text, state text DEFAULT 'pending' CHECK('pending','sending','sent','unconfirmed','skipped'), attempted_at timestamptz, sent_at timestamptz, reason text, UNIQUE(run_id, member_id) broadcast_delivery_pending(run_id, id) WHERE state='pending', broadcast_delivery_phone(phone, sent_at)
broadcast_gate id integer CHECK(id=1) last_attempt timestamptz (singleton 30s interval gate) PK
Table Primary Key Key Columns, Foreign Keys & Constraints Indexes
analytics_configuration id integer CHECK(id=1) started_at timestamptz DEFAULT now() (marks outcome telemetry activation time) PK
analytics_messages job_hash text occurred_at timestamptz, finished_at timestamptz, channel text, has_media boolean DEFAULT false, outcome text, searched boolean DEFAULT false, hits integer DEFAULT 0, queue_ms double precision, processing_ms double precision, delivery text DEFAULT 'not_applicable' analytics_messages_time(occurred_at)

stateDiagram-v2
    [*] --> open : Create via /v1/tickets, /tiket, natural consent, or case escalation
    open --> in_progress : Staff update / Quick action ("Mulai tangani")
    in_progress --> resolved : Staff update / Quick action ("Tandai selesai")
    resolved --> closed : Staff update / Quick action ("Tutup tiket")
    closed --> open : Staff reopen ("Buka kembali") -> emits ticket_open_events (reopened=true)
    resolved --> open : Staff reopen -> emits ticket_open_events (reopened=true)
    open --> resolved : Direct PATCH / Kanban drop
    open --> closed : Direct PATCH / Kanban drop
  • Every update increments tickets.version (expected_version optimistic locking prevents lost concurrent edits, returning 409 Conflict).
  • Transitions into open from non-open states (and initial creation) insert an idempotent ticket_open_events record (ticket_id, ticket_version) to alert on-duty admins.

4.2 Human Handoff & Bot Mode (support_cases / app/handoff.py)

Section titled “4.2 Human Handoff & Bot Mode (support_cases / app/handoff.py)”
stateDiagram-v2
    [*] --> BotActive : Inbound message creates/reuses open case (handoff_active=false)
    BotActive --> HandoffPending : User sends "bicara dengan petugas" -> ack once, SLA clock starts (2 working hrs)
    BotActive --> HandoffOwned : Admin clicks "Ambil kasus" -> staff_owner set, takeover notice queued
    BotActive --> HandoffNative : Staff replies via WhatsApp app (fromMe=true, source=app) -> pause & takeover notice
    HandoffPending --> HandoffOwned : Admin clicks "Ambil kasus"
    HandoffOwned --> HandoffPending : Admin clicks "Lepas kasus" -> staff_owner=NULL, pending staff replies cancelled
    HandoffOwned --> BotActive : Admin clicks "Aktifkan kembali bot" -> bot_resumed_at set, resume notice queued
    HandoffPending --> Closed : Admin closes case -> resume notice queued if handoff_active
    HandoffOwned --> Closed : Admin closes case -> resume notice queued
    BotActive --> Closed : Idle >= 24 hours -> close_idle() sets status=closed

4.3 PDF Document Lifecycle (knowledge_documents.status)

Section titled “4.3 PDF Document Lifecycle (knowledge_documents.status)”
stateDiagram-v2
    [*] --> queued : POST /manage/api/documents (<=32 MB, <=250 pages)
    queued --> processing : pdf-worker claims under lock 902722
    processing --> failed : Timeout (1800s), encrypted PDF, resource cap, or empty text
    failed --> queued : POST /retry (version increment)
    processing --> review : Text/OCR extracted into faq sections (OCR always requires review)
    processing --> published : Trusted public non-OCR PDF with auto_publish=true
    review --> published : POST /publish (retires replaces target atomically)
    published --> review : POST /unpublish or PATCH metadata/section edit
    published --> retired : Replaced by newer published document

4.4 Broadcast Campaign & Delivery State (broadcast_campaigns / broadcast_deliveries)

Section titled “4.4 Broadcast Campaign & Delivery State (broadcast_campaigns / broadcast_deliveries)”
  • Campaigns: draft -> active (requires matching preview_digest, version, and acknowledge_waha_risk: true) -> paused (manual pause or automatic pause on unconfirmed delivery / offline WAHA session) / completed / cancelled.
  • Deliveries: pending -> sending (committed claim before WAHA call) -> sent (WAHA 200/201) or unconfirmed (ambiguous/timeout/crash >2m, pauses campaign, never auto-retried) or skipped (opt-out/inactive/cancelled/run expired).

5. PostgreSQL RAG Indexing & Authority Design

Section titled “5. PostgreSQL RAG Indexing & Authority Design”

Dirman Assist executes lexical, synonym, and trigram retrieval entirely inside PostgreSQL (app/retrieval.py & app/knowledge_authority.py):

  1. Generated Search Columns on faq:
    • search_vector tsvector GENERATED ALWAYS AS (setweight(to_tsvector('simple', question || ' ' || aliases), 'A') || setweight(to_tsvector('simple', answer), 'B')) STORED — indexed with GIN(search_vector).
    • search_label text GENERATED ALWAYS AS (lower(question || ' ' || aliases)) STORED — indexed with GIN(search_label gin_trgm_ops) using pg_trgm (SET LOCAL pg_trgm.word_similarity_threshold=0.45).
  2. Authoritative Eligibility Predicate (ELIGIBLE): Every retrieval query filters strictly by:
    • faq.approved = true
    • CURRENT_SOURCE: Unexpired (expires_at IS NULL OR expires_at > now()), any owning knowledge_documents row is status = 'published' AND visibility = 'public' and unexpired, and any linked source_document_id/source_page reference remains valid and public.
    • AUTHORITY (app/knowledge_authority.py): Excludes any passage involved in a knowledge_relations row marked conflict, superseded (prefer or replaces where preferred_id <> faq.id), or stale (where content/metadata MD5 revision() changed after the decision was recorded).
  3. Coexistence Scope Annotations (SCOPE): Non-stale coexists relations inject left_scope / right_scope into applicability metadata passed to the model.

6. Data Retention & Automated Cleanup Schedule

Section titled “6. Data Retention & Automated Cleanup Schedule”

The hourly cleanup sweep in app/worker.py (combined with inline/login sweeps in app/dashboard_auth.py and app/ticket_flow.py) enforces bounded storage and privacy minimization:

Table Retention / TTL Cleanup Mechanism
conversation_turns 24 hours (max 15 turns/member) Deleted hourly by worker.py and on every conversation.record()
ticket_conversations 15 minutes Deleted hourly by worker.py and on ticket_flow.pending()
knowledge_intake_pending 15 minutes Deleted hourly by worker.py and invalidated if case closes
ai_config_tests 1 hour Deleted hourly by worker.py; question overwritten with '[Dihapus setelah uji]' upon completion
password_resets 30 minutes Deleted hourly by cases.cleanup() and revoked on account/contact/session changes
dashboard_sessions 8 hours Purged on sign_in() (expires_at < now()), logout, or password/role change
dashboard_login_attempts 1 day Purged on sign_in()
request_log 1 day Purged hourly by worker.py
ai_usage 7 days Purged hourly by worker.py
support_cases & case_messages 30 days Purged hourly by cases.cleanup() (cascades case_notes, staff_replies, media bytes; sets tickets.case_id = NULL)
jobs (done / failed) 30 days Purged hourly by worker.py (note: question and media_url cleared immediately on job completion)
waha_sent_messages 30 days Purged hourly by worker.py
knowledge_intake_receipts 30 days Purged hourly by worker.py
analytics_messages 180 days Purged hourly by worker.py (contains no text, identity, or credentials)
tickets, ticket_events, faq, knowledge_documents Persistent Retained until explicitly deleted or replaced; expired KB/PDFs are excluded from bot retrieval without deleting records