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 lock902720(pg_advisory_xact_lock(902720)) and executesapp/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.sqlso 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. Complete Data Dictionary (53 Tables)
Section titled “3. Complete Data Dictionary (53 Tables)”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 |
3.6 Consent-Based Broadcasts (7 Tables)
Section titled “3.6 Consent-Based Broadcasts (7 Tables)”| 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 |
3.7 Operational Analytics (2 Tables)
Section titled “3.7 Operational Analytics (2 Tables)”| 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) |
4. Core State Machines
Section titled “4. Core State Machines”4.1 Ticket Lifecycle (tickets.status)
Section titled “4.1 Ticket Lifecycle (tickets.status)”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_versionoptimistic locking prevents lost concurrent edits, returning409 Conflict). - Transitions into
openfrom non-open states (and initial creation) insert an idempotentticket_open_eventsrecord(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 matchingpreview_digest,version, andacknowledge_waha_risk: true) ->paused(manual pause or automatic pause onunconfirmeddelivery / offline WAHA session) /completed/cancelled. - Deliveries:
pending->sending(committed claim before WAHA call) ->sent(WAHA200/201) orunconfirmed(ambiguous/timeout/crash>2m, pauses campaign, never auto-retried) orskipped(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):
- 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 withGIN(search_vector).search_label text GENERATED ALWAYS AS (lower(question || ' ' || aliases)) STORED— indexed withGIN(search_label gin_trgm_ops)usingpg_trgm(SET LOCAL pg_trgm.word_similarity_threshold=0.45).
- Authoritative Eligibility Predicate (
ELIGIBLE): Every retrieval query filters strictly by:faq.approved = trueCURRENT_SOURCE: Unexpired (expires_at IS NULL OR expires_at > now()), any owningknowledge_documentsrow isstatus = 'published' AND visibility = 'public'and unexpired, and any linkedsource_document_id/source_pagereference remains valid and public.AUTHORITY(app/knowledge_authority.py): Excludes any passage involved in aknowledge_relationsrow markedconflict, superseded (preferorreplaceswherepreferred_id <> faq.id), or stale (where content/metadata MD5revision()changed after the decision was recorded).
- Coexistence Scope Annotations (
SCOPE): Non-stalecoexistsrelations injectleft_scope/right_scopeintoapplicabilitymetadata 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 |
7. Related Documentation
Section titled “7. Related Documentation”- ARCHITECTURE.md — Container topology and advisory lock allocation.
- SECURITY_AND_PRIVACY.md — Encryption, hashing, PII sanitization, and UU PDP compliance.
- API_AND_INTERFACES.md — API payloads mapping to these tables.
- Existing feature guides: accounts-and-cases.md, knowledge-retrieval.md, pdf-knowledge.md, content-topic-categorization.md, operational-analytics.md, broadcasts.md, ticket-management.md.