Schema reference

What this page is

A flat tabular reference for every table in the Ratiba schema — one section per table, columns called out with their types, constraints, and the ADR / migration that introduced them. Lookup material, not narrative.

For the why behind the shape — the migration ladder (public.0001 … 0009, tenant.0001 … 0013), the orm-vs-migration drift fences, and the schema invariants — read Architecture → Schema evolution. That page covers the story; this page covers the columns.

For the boundary concept between the two pools, see How it works → Identity and tenancy.

For column-level detail beyond what's printed here, the source of truth is psql — see the psql cheat sheet at the bottom.

---

ER overview

The diagrams below give a structural overview before the per-table column lists. Two separate ER diagrams reflect the two-pool topology (see ADR-0002 and Schema evolution for the full account). public.* holds the shared registry; tenant_<slug>.* holds per-tenant domain rows — the two pools never share FK constraints at the migration level.

public.* registry pool

tenant_<slug>.* core booking domain

The most-queried cluster. All tables live in the same per-tenant schema with a shared transactional boundary; FK constraints are intra-schema.

tenant_<slug>.* knowledge and conversation

KNOWLEDGE_SNIPPETS is intentionally standalone — no FK to any other table. It is scoped to the tenant by search_path alone (ADR-0013). The category field drives intent-routing in app/services/knowledge.py::fetch_snippets(). NO embedding column in Phase 0; that is the single additive Phase-1 upgrade trigger.

---

public.* tables (shared registry)

Five tables plus two archive tables in the public schema. Every tenant in the platform is a row in public.tenants; every cross-tenant lookup (webhook routing, dead-letter queues, tenancy registry) lives here. Schema-per-tenant isolation (ADR-0002) means every per-tenant table lives outside this schema.

public.tenants — tenant registry

The canonical registry row. One row per tenant. Carries every channel credential, every per-tenant dial, every payment configuration knob, every cost ceiling.

Column	Type	Notes
id	UUID PK	gen_random_uuid() server default
name	VARCHAR(255) NOT NULL	Display name
slug	VARCHAR(100) UNIQUE	URL-safe tenant identifier
schema_name	VARCHAR(100)	Partial UNIQUE WHERE NOT NULL — tenant_<slug> until ADR-0002 D6 finalize-deletion sets NULL
vertical	VARCHAR(20) NOT NULL CHECK	'spa' | 'salon' | 'barbershop' | 'dental' | 'physio' | 'medical' | 'tutoring' | 'legal'
whatsapp_number	VARCHAR(20)	Partial UNIQUE WHERE NOT NULL — inbound DID for WhatsApp
voice_number	VARCHAR(20)	Partial UNIQUE WHERE NOT NULL — inbound DID for voice
timezone	VARCHAR(50)	'Africa/Nairobi' server default
currency	VARCHAR(3)	'KES' server default
locale	VARCHAR(10)	'en' server default
mpesa_enabled	BOOLEAN	FALSE server default — opt-in payment rail (M-Pesa direct)
mpesa_shortcode	VARCHAR(20)	Daraja shortcode
mpesa_passkey	TEXT	Daraja passkey (legacy field; M6+ uses daraja_credentials)
status	VARCHAR(20) CHECK	'active' | 'suspended' | 'trial' | 'deleted'
trial_ends_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ	ADR-0002 D6 soft-delete marker
pesapal_consumer_key	TEXT (encrypted)	Encrypted at rest via EncryptedText
pesapal_consumer_secret	TEXT (encrypted)	Encrypted at rest via EncryptedText
pesapal_ipn_id	UUID	PesaPal IPN registration handle
cost_ceiling_soft_usd	NUMERIC(6,4) NOT NULL	0.05 default — ADR-0005 D3 per-booking soft cap
cost_ceiling_hard_usd	NUMERIC(6,4) NOT NULL	0.20 default — ADR-0005 D3 per-booking hard cap
llm_role_overrides	JSONB NOT NULL	'{}' default — ADR-0005 D4 per-tenant LLM router overrides
handoff_admin_notice_seconds	INT NOT NULL	120 default — ADR-0006 D2
handoff_admin_reminder_seconds	INT NOT NULL	600 default — ADR-0006 D2
handoff_escalation_seconds	INT NOT NULL	3600 default — ADR-0006 D2
handoff_thresholds	JSONB NOT NULL	'{}' default — ADR-0006 D5 per-tenant trigger thresholds
pesapal_nudge_seconds	INT NOT NULL	480 (8min) default — ADR-0007 D1
pesapal_abandon_seconds	INT NOT NULL	1800 (30min) default — ADR-0007 D1
voice_stk_max_hold_seconds	INT NOT NULL	90 default — ADR-0007 D3 voice STK hold cap
whatsapp_phone_number_id	VARCHAR(50)	Partial UNIQUE WHERE NOT NULL — Cloud API channel-boundary key (ADR-0008)
whatsapp_access_token	TEXT (encrypted)	Encrypted at rest via EncryptedText
whatsapp_business_account_id	VARCHAR(50)	Public WABA identifier (ADR-0008)
daraja_credentials	BYTEA (encrypted)	Fernet-ciphered JSON blob — Daraja credential quartet (M6)
pesapal_credentials	BYTEA (encrypted)	Fernet-ciphered JSON blob — PesaPal credential pair (M6)
payment_environment_override	TEXT CHECK	NULL or 'sandbox' | 'production' — overrides Settings.payment_environment
payment_enabled	BOOLEAN NOT NULL	FALSE default — opt-in payment rail at all (M6)
preferred_payment_provider	TEXT NOT NULL CHECK	'mpesa' | 'pesapal' — 'mpesa' default (ADR-0007 D6)
voice_enabled	BOOLEAN NOT NULL	FALSE default — opt-in voice channel (M7)
voice_greeting_language	TEXT CHECK	NULL or 'en' | 'sw'
voice_default_speaker	TEXT	ElevenLabs voice id override
voice_eot_silence_ms	INT	End-of-turn silence override (ms); NULL = use project default
voice_full_duplex_enabled	BOOLEAN NOT NULL	FALSE default — master switch for full-duplex turn-handling (voice-duplex W0)
voice_backchannel_filter	BOOLEAN NOT NULL	FALSE default — suppress false barge-in on filler words ("mm", "uh-huh")
voice_hard_interrupt	BOOLEAN NOT NULL	FALSE default — repeated interrupts cancel TTS and return floor to caller
voice_streaming	BOOLEAN NOT NULL	FALSE default — sentence-stream seam to ElevenLabs (reduces first-audio latency)
voice_listening_ack	BOOLEAN NOT NULL	FALSE default — mid-utterance presence signals ("ndiyo", "okay")
voice_adaptive_speed	BOOLEAN NOT NULL	FALSE default — WPM adjusted to measured caller speech rate
voice_endpointing_min_delay_ms	INT	NULL = use project default; floor of silence endpointing window
voice_endpointing_max_delay_ms	INT	NULL = use project default; ceiling of silence endpointing window
admin_whatsapp_briefing_enabled	BOOLEAN NOT NULL	FALSE default — opt-in WhatsApp briefing-card rail (M9)
instagram_business_account_id	TEXT	Instagram channel id (ADR-0009 / M10)
instagram_access_token	TEXT	Encrypted at app layer
instagram_enabled	BOOLEAN NOT NULL	FALSE default
facebook_page_id	TEXT	Messenger DM channel id (M10)
facebook_page_access_token	TEXT	Encrypted at app layer
messenger_enabled	BOOLEAN NOT NULL	FALSE default
widget_primary_color	TEXT NOT NULL	'#0F766E' default — web widget brand colour
widget_logo_url	TEXT	Web widget logo
widget_display_name	TEXT	Web widget header label
channel_steering_enabled	BOOLEAN NOT NULL	FALSE default — opt-in channel-switch chip (ADR-0009 D11)
phone_otp_required	BOOLEAN NOT NULL	FALSE default — STK-as-oracle when FALSE; SMS OTP when TRUE
reminder_lead_seconds	INT NOT NULL	86400 (24h) default — ADR-0009 reminder lead time
created_at / updated_at	TIMESTAMPTZ	NOW() server defaults

Indexes: idx_tenants_status, plus the four partial-UNIQUE indexes on whatsapp_number, voice_number, schema_name, whatsapp_phone_number_id (each WHERE <col> IS NOT NULL).

public.tenant_admins

Phone-OTP login map for the dashboard rail. Composite UNIQUE on (tenant_id, phone_number) — same phone can administer multiple tenants but cannot duplicate within one.

Column	Type	Notes
id	UUID PK	gen_random_uuid() server default
tenant_id	UUID FK → tenants.id
phone_number	VARCHAR(20) NOT NULL	Composite UNIQUE with tenant_id
keycloak_user_id	UUID
role	VARCHAR(20)	'owner' server default
is_active	BOOLEAN	TRUE server default
created_at	TIMESTAMPTZ	NOW() server default

Indexes: idx_tenant_admins_phone.

public.payment_routing — STK correlation bridge (ADR-0002 D1 / ADR-0007)

Tenant-less webhook routing. Daraja / PesaPal callbacks arrive carrying only merchant_reference — this table maps that opaque ID back to its owning tenant + thread. Daily 3 AM EAT consolidated reaper trims rows by expires_at (ADR-0007 D5).

Column	Type	Notes
merchant_reference	VARCHAR(40) PK	Provider-echoed correlation id
tenant_id	UUID NOT NULL FK → tenants.id
schema_name	VARCHAR(100) NOT NULL	tenant_<slug> for the routing handler
thread_id	VARCHAR(100) NOT NULL	LangGraph conversation thread
provider	VARCHAR(20) NOT NULL CHECK	'mpesa' | 'airtel' | 'equitel' | 'pesapal'
expires_at	TIMESTAMPTZ NOT NULL	24h TTL — reaper sweeps past this
created_at	TIMESTAMPTZ NOT NULL	NOW() server default

Indexes: idx_payment_routing_tenant, idx_payment_routing_expires.

public.payment_callbacks_unrouted — dead-letter (ADR-0007 D6)

Late-arriving callbacks (past the 24h payment_routing TTL). Rare path; provider-outage scenarios only.

Column	Type	Notes
id	UUID PK	gen_random_uuid()
provider	VARCHAR(20) NOT NULL CHECK	Same enum as payment_routing.provider
raw_payload	JSONB NOT NULL	Provider callback body verbatim
received_at	TIMESTAMPTZ NOT NULL	NOW() server default
attempted_merchant_reference	VARCHAR(40)	What merchant_reference we tried to match against
reviewed_at	TIMESTAMPTZ	Ops triage timestamp
reviewed_by	UUID	Reviewing admin user id
resolution_note	TEXT	Free-text triage note

Partial index: idx_payment_callbacks_unrouted_unreviewed (received_at WHERE reviewed_at IS NULL) — keeps the daily ops dashboard query bounded as the queue grows.

public.inbound_quarantine — WhatsApp dead-letter (ADR-0008 / M4)

Inbound webhooks whose phone_number_id doesn't match any tenant. Channel-boundary handler writes here and returns 200 silently — no outbound reply, no information leak about which numbers Ratiba routes.

Column	Type	Notes
id	UUID PK	gen_random_uuid()
received_at	TIMESTAMPTZ NOT NULL	NOW() server default
to_phone_number_id	VARCHAR(50) NOT NULL	The unmatched Cloud API id
from_phone	VARCHAR(20) NOT NULL	Inbound sender
raw_payload	JSONB NOT NULL	Webhook body verbatim
reviewed_at / reviewed_by / resolution_note	(same shape as payment_callbacks_unrouted)

Partial index: idx_inbound_quarantine_unreviewed (mirrors the payment-side DLQ shape so ops queries against the two queues share a mental model).

---

tenant_<slug>.* tables (per-tenant)

Twenty-three tables per tenant (M3 baseline through Phase 0). Migration chain owned by backend/alembic/versions/tenant/ and replayed from baseline on every new tenant onboarding (ADR-0002). The two-pool boundary is explained in How it works → Identity and tenancy.

services (M3 baseline)

Catalog row. Edited by M11 dashboard + slash + NL routing. The description field is surfaced in Phase 0 knowledge answers via fetch_snippets() — see ADR-0013.

Column	Type	Notes
id	UUID PK	gen_random_uuid()
name	VARCHAR(255) NOT NULL
name_sw	VARCHAR(255)	Swahili service name
description	TEXT	Long-form description; surfaced in Phase 0 KB answers
duration_min	INT NOT NULL	Appointment duration in minutes
price	NUMERIC(10,2) NOT NULL
currency	VARCHAR(3)	'KES' default
buffer_min	INT	15 default — post-appointment gap before next booking
is_active	BOOL	TRUE default
sort_order	INT	0 default
created_at / updated_at	TIMESTAMPTZ

staff (M3 baseline + M13 employment attrs)

Core staff record. M13 adds Workpay-authoritative employment fields (cached read-only mirror; M17 will sync from Workpay monthly).

Column	Type	Notes
id	UUID PK
name	VARCHAR(255) NOT NULL
phone_number	VARCHAR(20)
is_active	BOOL	TRUE default
created_at / updated_at	TIMESTAMPTZ
kra_pin	TEXT	M13; CHECK regex ^[AP][0-9]{9}[A-Z]$
nssf_number	TEXT	M13
shif_number	TEXT	M13 (formerly NHIF)
hire_date	DATE	M13
employment_type	VARCHAR(20)	M13; CHECK 'permanent' | 'casual' | 'commission_only' | 'apprentice'; default 'permanent'
base_pay_kes_minor	INT	M13; gross monthly base pay in KES minor units (divide by 100 for KES)

staff_services (M3 baseline)

Many-to-many staff↔service. Composite UNIQUE on (staff_id, service_id).

Column	Type	Notes
id	UUID PK
staff_id	UUID FK → staff.id	CASCADE delete
service_id	UUID FK → services.id	CASCADE delete

staff_schedules (M3 baseline)

Recurring weekly availability per staff member. Composite UNIQUE on (staff_id, day_of_week).

Column	Type	Notes
id	UUID PK
staff_id	UUID FK	CASCADE delete
day_of_week	SMALLINT NOT NULL CHECK	0 (Mon) – 6 (Sun)
start_time	TIME NOT NULL
end_time	TIME NOT NULL
is_available	BOOL	TRUE default

staff_blocks (M3 baseline)

Ad-hoc unavailability windows (holiday, sick leave, personal time).

Column	Type	Notes
id	UUID PK
staff_id	UUID FK	CASCADE delete
start_at	TIMESTAMPTZ NOT NULL
end_at	TIMESTAMPTZ NOT NULL
reason	VARCHAR(255)
created_at	TIMESTAMPTZ

contacts (M3 baseline)

Pre-M10 customer record. Co-exists with M10's customers table — contacts is the M3-era identity table for appointment booking; M10's customers carries the channel-agnostic identity layer. The two are bridged at the DAO layer (no FK between them).

Column	Type	Notes
id	UUID PK
phone_number	VARCHAR(20) NOT NULL UNIQUE	Auto-indexed
name	TEXT
email	TEXT
language_pref	TEXT	'en' default
tags	TEXT[]	'{}' default
notes	TEXT
total_bookings	INT	Running count
total_spent	NUMERIC	Running sum
last_visit_at	TIMESTAMPTZ
created_at / updated_at	TIMESTAMPTZ

appointments (M3 baseline + M13 rendering actuals)

The booking row — the workhorse table. M13 adds four columns for commission rendering actuals (actual clock-in/out, tip, staff notes).

Column	Type	Notes
id	UUID PK
contact_id	UUID FK → contacts.id
service_id	UUID FK → services.id
staff_id	UUID FK → staff.id
start_at	TIMESTAMPTZ NOT NULL	Scheduled start
end_at	TIMESTAMPTZ NOT NULL	Scheduled end
status	VARCHAR(20) CHECK	'pending' | 'confirmed' | 'cancelled' | 'completed' | 'no_show'
payment_status	VARCHAR(20) CHECK	'unpaid' | 'pending' | 'paid' | 'refunded'
payment_ref	TEXT
amount	NUMERIC
booking_channel	TEXT CHECK	'whatsapp' | 'voice' | 'dashboard' | 'web' | 'instagram' | 'messenger'
cancelled_by	TEXT CHECK	'customer' | 'admin' | 'system'
cancel_reason	TEXT
reminder_sent	BOOL	FALSE default
actual_start_at	TIMESTAMPTZ	M13 — clock-in (may differ from start_at)
actual_end_at	TIMESTAMPTZ	M13 — clock-out; CommissionEngine falls back to end_at if NULL
tip_amount	NUMERIC(10,2)	M13 — tip in KES; NULL = no tip
staff_notes	TEXT	M13 — freeform notes added by staff post-service
created_at / updated_at	TIMESTAMPTZ

Indexes: idx_appointments_staff_date (partial WHERE status != 'cancelled'), idx_appointments_contact, idx_appointments_status_date.

interactions (M3 baseline)

Per-message audit log. Every inbound and outbound message across all channels creates a row here.

Column	Type	Notes
id	UUID PK
contact_id	UUID FK
channel	VARCHAR(20) NOT NULL CHECK	'whatsapp' | 'voice' | 'dashboard'
direction	VARCHAR(10) NOT NULL CHECK	'inbound' | 'outbound'
message_type	TEXT
content	TEXT
intent	TEXT	Classifier output
language	TEXT	'en' | 'sw'
duration_ms	INT	Voice message duration
metadata	JSONB	Channel-specific extras
created_at	TIMESTAMPTZ

Index: idx_interactions_contact (contact_id, created_at DESC).

payments (M3 baseline + ADR-0007 D9)

Payment attempt row. UNIQUE DEFERRABLE INITIALLY DEFERRED on mpesa_receipt (ADR-0007 §62 — triple-layer idempotency layer 1).

Column	Type	Notes
id	UUID PK
appointment_id	UUID FK
contact_id	UUID FK
amount	NUMERIC(10,2) NOT NULL
currency	VARCHAR(3)	'KES' default
provider	VARCHAR(20) CHECK	'mpesa' | 'airtel' | 'equitel' | 'pesapal'
mpesa_receipt	VARCHAR(50)	UNIQUE DEFERRABLE INITIALLY DEFERRED
mpesa_checkout_id	VARCHAR(100)	STK checkout request id
phone_number	TEXT	Paying phone
status	VARCHAR(30) CHECK	'initiated' | 'pending' | 'completed' | 'failed' | 'timeout' | 'cancelled_by_customer' (widened to 30 chars, ADR-0007 D9)
initiated_at	TIMESTAMPTZ
completed_at	TIMESTAMPTZ
callback_payload	JSONB	Raw provider callback
created_at	TIMESTAMPTZ

Indexes: idx_payments_checkout, idx_payments_appointment.

conversation_threads (M3 baseline + ADR-0003 D4 + ADR-0005 D5)

LangGraph thread pointer table. Composite PK (customer_phone, thread_id).

Column	Type	Notes
customer_phone	VARCHAR(20) NOT NULL	Part of composite PK
thread_id	VARCHAR(100) NOT NULL	ULID-based; part of composite PK
created_at	TIMESTAMPTZ
closed_at	TIMESTAMPTZ
closed_reason	TEXT CHECK	'done' | 'abandon' | 'closed_by_human'
channel_started	TEXT NOT NULL CHECK	'whatsapp' | 'voice' | 'web' | 'instagram' | 'messenger'
actor_type	TEXT NOT NULL CHECK	'customer' | 'admin'

Partial indexes: idx_threads_active_customer, idx_threads_active_admin (each WHERE closed_at IS NULL AND actor_type = '<…>').

handoff_log (M3 baseline + M9 admin-claim columns)

Verbatim transcript fold per ADR-0006 D7. M9 added four claim columns (mirrored on handoff_log_archive).

Column	Type	Notes
id	UUID PK
thread_id	TEXT NOT NULL
actor	TEXT NOT NULL CHECK	'customer' | 'admin'
actor_phone	TEXT NOT NULL
content	TEXT NOT NULL	Verbatim message text
language	TEXT	'en' | 'sw'
sent_at	TIMESTAMPTZ	NOW() default
message_metadata	JSONB	Meta read-receipts (360dialog / Cloud API)
claimed_by_admin_id	UUID	M9; nullable
claimed_at	TIMESTAMPTZ	M9; nullable
admin_replied_at	TIMESTAMPTZ	M9; nullable
claimed_via	TEXT CHECK	M9; 'dashboard' | 'whatsapp'

Index: idx_handoff_log_thread (thread_id, sent_at).

handoff_log_archive (M3 baseline)

CREATE TABLE … (LIKE handoff_log INCLUDING ALL) clone — the daily 3 AM EAT reaper INSERTs past-90-day rows here then DELETEs from the live table. Column-set parity with handoff_log is enforced by the test_handoff_log_archive_clones_via_like test (M9 added four claim columns to BOTH tables in the same migration to maintain parity).

customers (M10 — channel-agnostic identity)

Per ADR-0009 D5. phone_e164 is nullable — Tier-2 sessions start phoneless and get progressively phone-captured.

Column	Type	Notes
customer_id	UUID PK
phone_e164	TEXT	Partial UNIQUE WHERE NOT NULL (uq_customers_phone_e164)
name	TEXT
dob	DATE
language_pref	TEXT
created_at	TIMESTAMPTZ

customer_sessions (M10)

One row per active conversation thread. Tier-2 sessions start with customer_id = NULL + unverified = TRUE; the COLLECT_PHONE FSM node back-fills customer_id and stamps promoted_at.

Column	Type	Notes
id	UUID PK
customer_id	UUID FK → customers.customer_id	SET NULL on delete — preserves audit trail
name	TEXT
language_pref	TEXT
unverified	BOOL NOT NULL	FALSE default
handoff_token	TEXT	Partial UNIQUE WHERE NOT NULL (uq_customer_sessions_handoff_token) — channel-switch token
handoff_token_expires_at	TIMESTAMPTZ	24h TTL (ADR-0009 D11)
created_at	TIMESTAMPTZ
promoted_at	TIMESTAMPTZ	Set when phone collected by COLLECT_PHONE node

customer_identities (M10)

Phone-only deterministic cross-channel identity per ADR-0009 D5. Composite PK on (customer_id, provider, external_id).

Column	Type	Notes
customer_id	UUID NOT NULL FK → customers.customer_id	CASCADE delete
provider	TEXT NOT NULL CHECK	'whatsapp' | 'voice' | 'web_cookie' | 'instagram' | 'messenger'
external_id	TEXT NOT NULL	Channel-native identifier
created_at	TIMESTAMPTZ

Index: ix_customer_identities_alias (provider, external_id) — hot-path reverse lookup for every Tier-2 inbound turn.

service_relations (M11 / ADR-0010 D5)

LLM-inferred relation graph populated at catalog onboarding. Composite PK (service_a_id, service_b_id, relation_type).

Column	Type	Notes
service_a_id	UUID NOT NULL	Source service (no FK — app-enforced)
service_b_id	UUID NOT NULL	Target service (no FK — app-enforced)
relation_type	TEXT NOT NULL CHECK	'complementary' | 'alternative' | 'sequential'
source	TEXT NOT NULL CHECK	'llm_inferred' | 'tenant_pinned' | 'association_rule'
confidence	DOUBLE PRECISION	Nullable for admin pins (tenant_pinned)
reason	TEXT	LLM explanation
created_at	TIMESTAMPTZ

Partial index: idx_service_relations_complementary (service_a_id, confidence DESC WHERE relation_type = 'complementary' AND confidence >= 0.7) — hot-path v1 cross-sell lookup. No FK to services — referential integrity enforced at app layer (M5's services PK shape predates this design).

catalog_imports (M11 / ADR-0010 D8 + D12)

One row per upload (image / multi_image / pdf / text / csv). The snapshot_jsonb enables a single-snapshot rollback within a 7-day window.

Column	Type	Notes
import_id	UUID PK
modality	TEXT NOT NULL CHECK	'image' | 'multi_image' | 'pdf' | 'text' | 'csv'
type	TEXT NOT NULL CHECK	'initial' | 're-import' | 'manual'
snapshot_jsonb	JSONB NOT NULL	Pre-import state — D12 7-day rollback window
extracted_jsonb	JSONB NOT NULL	Raw extraction output
committed_jsonb	JSONB	Nullable; populated on admin commit
actor_admin_id	UUID	Nullable
confidence_summary	JSONB	Per-field confidence scores
created_at	TIMESTAMPTZ
committed_at	TIMESTAMPTZ

Index: idx_catalog_imports_actor (actor_admin_id, created_at).

catalog_audits (M11 / ADR-0010 D11)

Per-row edit trail (one row per service-level mutation).

Column	Type	Notes
audit_id	UUID PK
action	TEXT NOT NULL CHECK	'insert' | 'update' | 'delete' | 'toggle'
service_id	UUID NOT NULL	No FK — app-enforced referential integrity
before	JSONB	Nullable — absent on inserts
after	JSONB	Nullable — absent on deletes
actor_admin_id	UUID	Nullable (system-initiated mutations)
source	TEXT NOT NULL CHECK	'slash' | 'nl' | 'import' | 'dashboard'
created_at	TIMESTAMPTZ

Index: idx_catalog_audits_service_time (service_id, created_at).

tenant_personality_config (M11 / ADR-0010 D2 + D4 + D10)

Per-tenant override row for the six personality dials. Singleton — partial UNIQUE on is_singleton WHERE is_singleton = TRUE. NULL on a dial column = inherit per-vertical YAML default (D4). Enum validation is enforced by PersonalityConfig in the app layer, not at the DB level.

Column	Type	Notes
tenant_personality_id	UUID PK
is_singleton	BOOL NOT NULL	TRUE default — partial UNIQUE uq_tenant_personality_singleton
tone	TEXT	Nullable — NULL inherits YAML default
greeting_mode	TEXT	Nullable
greeting_custom	TEXT	Nullable; max 140 chars (app-enforced)
upsell	TEXT	Nullable
cancellation_tone	TEXT	Nullable
honorific	TEXT	Nullable
cross_sell	TEXT	Nullable
updated_at	TIMESTAMPTZ
updated_by_admin_id	UUID	Nullable

dial_audits (M11 / ADR-0010 D11)

Dial-mutation audit row. 90-day retention parity with handoff_log (daily 3 AM EAT reaper).

Column	Type	Notes
audit_id	UUID PK
dial	TEXT NOT NULL CHECK	'tone' | 'greeting_mode' | 'greeting_custom' | 'upsell' | 'cancellation_tone' | 'honorific' | 'cross_sell'
before	JSONB
after	JSONB
actor_admin_id	UUID
created_at	TIMESTAMPTZ

Index: idx_dial_audits_time (created_at).

commission_rules (M13 / ADR-0012)

Per-staff or per-service-category commission rules. The staff_id XOR service_category constraint (ck_commission_rules_staff_xor_category) ensures every rule applies to exactly one target axis.

Column	Type	Notes
id	UUID PK
staff_id	UUID FK → staff.id	CASCADE; XOR with service_category
service_category	VARCHAR(50)	XOR with staff_id
rule_type	VARCHAR(10) NOT NULL CHECK	'pct' | 'fixed' | 'tiered'
value	JSONB NOT NULL	pct: 0–100; fixed: KES minor units; tiered: [{threshold, rate}] array
valid_from	TIMESTAMPTZ NOT NULL	NOW() default
valid_to	TIMESTAMPTZ	NULL = currently active; CHECK valid_to > valid_from
created_at	TIMESTAMPTZ NOT NULL

Index: idx_commission_rules_staff_active (staff_id WHERE valid_to IS NULL) — hot-path for active-rule lookup.

commission_period (M13 / ADR-0012)

Frozen monthly commission snapshot per staff member. One row per (staff_id, period_start, period_end, type) — idempotency key uq_commission_period_idempotency makes re-running CommissionEngine for the same period a no-op. Amendments create a new row with type='amendment' pointing back to the original close row.

Column	Type	Notes
id	UUID PK
staff_id	UUID NOT NULL FK → staff.id	CASCADE
period_start	DATE NOT NULL	Inclusive start of period
period_end	DATE NOT NULL	Inclusive end; CHECK >= period_start
commission_kes_minor	INT NOT NULL	Computed commission in KES minor units; 0 default
tip_kes_minor	INT NOT NULL	Total tips in KES minor units; 0 default
hours_worked	NUMERIC(8,2) NOT NULL	Derived from actual_start_at/actual_end_at; 0 default
services_rendered_count	INT NOT NULL	Count of completed appointments in period; 0 default
type	VARCHAR(15) NOT NULL CHECK	'close' | 'amendment'; 'close' default
amendment_of_id	UUID FK → commission_period.id	NULL for close rows; required for amendment rows
frozen_at	TIMESTAMPTZ NOT NULL	NOW() default
vendor_period_id	VARCHAR(100)	Workpay period id for future M17 sync

Unique: uq_commission_period_idempotency (staff_id, period_start, period_end, type). Index: idx_commission_period_staff_period (staff_id, period_start).

knowledge_snippets (Phase 0 — ADR-0013)

Per-tenant curated knowledge for free-form inquiry answers. Phase 0 "no-RAG RAG" approach — snippets are injected directly into the answer_shaper prompt via raw_data["knowledge"]. No embedding column — that is the single additive Phase-1 upgrade trigger. Scoped to the tenant by search_path alone (no tenant-id column needed). Hand-seeded via scripts/seed_knowledge.py; no conversational or dashboard authoring in Phase 0.

Column	Type	Notes
id	UUID PK	gen_random_uuid()
category	TEXT NOT NULL	Intent-routing key: services, hours, other, general
title	TEXT NOT NULL	Short heading for the snippet
body	TEXT NOT NULL	Answer text (Swahili or English)
language	TEXT NOT NULL	'en' default — EN snippets render in SW answers (not filtered)
is_active	BOOLEAN NOT NULL	TRUE default — FALSE = soft-delete without data loss
created_at	TIMESTAMPTZ NOT NULL
updated_at	TIMESTAMPTZ NOT NULL

Index: idx_knowledge_snippets_category (category WHERE is_active) — covers the hot-path query in fetch_snippets(intent).

Cap: ~20 snippets / ~1,500 tokens per tenant. Exceeding the cap emits a knowledge_overflow WARN log — the observable Phase-0 → Phase-1 graduation signal (see ADR-0013).

LangGraph checkpoints family (M3 — owned by PostgresSaver)

Three tables — checkpoints, checkpoint_blobs, checkpoint_writes — created by PostgresSaver.setup() at onboarding-time, NOT by an Alembic migration (ADR-0003 D2). Their archive twins (checkpoints_archive, etc.) are the destination of the daily reaper sweep at ADR-0003's 90-day retention window. Schema is owned by the LangGraph library — see LangGraph PostgresSaver docs rather than enumerating columns here.

---

Cross-links

• Architecture → Schema evolution — migration ladder, orm-vs-migration drift fences, schema invariants.
• How it works → Identity and tenancy — the two-pool boundary concept, runtime onboarding orchestrator, active-schema contextvar.
• How it works → Catalog onboarding — M11 catalog importer flow against services + service_relations
  • catalog_imports + catalog_audits.
• How it works → Personality dials — M11 dial-resolution against tenant_personality_config + YAML.
• How it works → Knowledge answers — Phase 0 knowledge_snippets injection flow.
• ADR-0002 — schema-per-tenant isolation; two pools, two migration chains.
• ADR-0010 — M11 schema contributions (service_relations, catalog_imports, catalog_audits, tenant_personality_config, dial_audits).
• ADR-0012 — M13 schema contributions (staff employment attrs, appointment rendering actuals, commission_rules, commission_period).
• ADR-0013 — Phase 0 knowledge answers; knowledge_snippets table rationale, cap, and Phase-1 upgrade trigger.

---

psql cheat sheet

For column-level questions beyond what's listed above, the source of truth is psql against the live database. Connect to the development Postgres on :5434 (per project port assignments):

# Connect (development)
psql -h localhost -p 5434 -U ratiba ratiba

# List all schemas (one per tenant + public)
\dn

# List all tables in a tenant schema
\dt tenant_<slug>.*

# Full column-level detail for a single table
\d tenant_<slug>.appointments
\d public.tenants

# Migration head per chain
SELECT version_num FROM public.alembic_version;
SELECT version_num FROM tenant_<slug>.alembic_version;

# Quick row-count snapshot for a tenant's hot tables
SELECT
  (SELECT count(*) FROM tenant_<slug>.appointments) AS appointments,
  (SELECT count(*) FROM tenant_<slug>.payments) AS payments,
  (SELECT count(*) FROM tenant_<slug>.knowledge_snippets
   WHERE is_active) AS active_snippets,
  (SELECT count(*) FROM tenant_<slug>.conversation_threads
   WHERE closed_at IS NULL) AS active_threads;

When in doubt, \d <table> always wins — it prints every column, every constraint, every index, every default in one go.