Database Schema

81 tables, 100% RLS coverage, DDD-organized. Migrated from KV store to fully relational. Every mutation routes through the NestJS backend (Drizzle over postgres.js), which owns validation and access control at the application layer.

DB Audit — 2026-05-14 (PS-434–PS-444)

Full migration-vs-code audit completed. 7 new migrations landed. Price-drop notification bug fixed (stale table name). Two dead insert calls removed. Schema gaps and orphan tables documented. All tables now have RLS.

Schema at a glance

Metric	Value
Total tables	81
Functional areas	8
RLS coverage	100%
Ephemeral KV keys remaining	3 (health-check, error, durable_rl)
Migration source	KV store (fully retired)
Last audit	2026-05-14 (PS-434–PS-444)

Tables by functional area

Core marketplace tables (5)

The heart of the platform.

user_profiles

Every authenticated user has a row. Stores role, KTP status, account state, profile data.

Key columns:

• id (uuid, FK to auth.users)
• email, username, name, avatar_url
• role (buyer, seller, ops_admin, finance_admin, superadmin)
• account_status (active, suspended, banned)
• ktp_status (none, pending, approved, rejected)
• phone (PII — RLS restricts to own row)
• verified_seller (boolean — gets badge)

listings

Every seller-created item.

Key columns:

• id, seller_id (FK to user_profiles)
• title, description, price, negotiable
• category, sub_category, brand, condition
• images (text array, up to 8 URLs)
• vibe_tags (text array)
• status CHECK constraint: active, sold, draft, reserved, hidden, inactive, removed, archived — archived is the soft-delete state
• moderation_status CHECK constraint: pending_review, approved, rejected, flagged. Default changed to 'approved' for pre-launch auto-approve. New listings start as approved — ops can retroactively flag or reject.
• version (optimistic locking)
• deleted_at (soft delete — always accompanied by status='archived')
• boost_active, boost_type, boost_expires_at

orders

The transactional spine. State machine lives here.

Key columns:

• id, buyer_id, seller_id, listing_id
• order_status (14 states, DB CHECK constrained)
• version (optimistic locking)
• item_price, buyer_protection_fee, shipping_fee, insurance_fee, payment_fee
• total_paid (sum of all)
• xendit_invoice_id, xendit_paid_at
• tracking_number, shipped_at, delivered_at
• dispute_window_start_at, return_window_end_at
• auto_confirm_at (delivered_at + 48h)
• consumed_at, consumed_by_order_id (atomic claim from chat offers)
• voucher_id TEXT DEFAULT NULL, voucher_discount NUMERIC(12,2) DEFAULT 0 CHECK >= 0 — discount recalculated server-side, capped to prevent zero/negative totals

cart_items

Buyer cart — want-to-buy intent only, no reservation, no quantity (one listing = one item). Sold items stay in the cart (greyed out client-side).

Key columns:

• id, user_id (FK to auth.users, cascade), listing_id (FK to listings, cascade), added_at
• UNIQUE(user_id, listing_id); RLS auth.uid() = user_id

checkout_groups

One per seller multi-item checkout — unifies payment + shipping (one Xendit invoice per group).

Key columns:

• id, buyer_id, seller_id, shipping_fee
• status CHECK: pending_payment, paid, expired, cancelled
• xendit_invoice_id, xendit_invoice_url, xendit_expires_at, paid_at
• shipping_fee_released_at — exactly-once shipping-fee-release marker

Social & messaging tables (4)

chat_threads, chat_messages

1:1 chat between buyer and seller, scoped per listing.

reviews

Post-completion reviews. Anti-gaming columns: is_suspicious, is_visible, flagged_reason. review_type TEXT NOT NULL DEFAULT 'listing' added (CHECK IN ('seller', 'listing')) — backs the ops admin seller-vs-listing moderation split.

seller_scores

Aggregate quality metrics per seller. Computed weekly.

User data tables (3)

addresses

Buyer shipping addresses. Multiple per user; one is flagged is_default.

PS-883 (2026-06-02): any primary-address mutation back-fills the primary address's phone into user_profiles.phone — fill-in only, never overwriting a value the user set themselves.

bank_accounts

Seller payout destinations. Multiple per user but only one active.

ktp_verifications

KTP submission history with OCR results, ops decisions, audit trail.

Promotions tables (3)

promotions

Unified table for boosts and promo campaigns. promo_type column distinguishes.

notifications, notification_preferences

In-app + push notifications + per-user channel preferences.

Finance tables (15)

All fin_* tables live in the public schema with fin_ prefix. Locked decision (2026-04-28) — the fin_ prefix provides sufficient logical separation pre-launch. A dedicated finances schema may be introduced when cuan team separation triggers (post-launch, post finance-lead hire). See Cuan Portal future-state plan.

The full finance module:

• fin_orders — order-level financial records
• fin_disbursements — payouts to sellers (with maker-checker)
• fin_seller_balances — escrow holdings per seller
• fin_audit_log — every financial change logged with before/after JSONB
• fin_xero_syncs — Xero sync state (🟡 boolean only, no API call yet)
• fin_commission_rules — platform-side commission config (zero for sellers, BPF for buyers)
• fin_expenses — operational expenses tracked per category
• fin_admin_roles — finance admin role assignments
• fin_boost_purchases — Boost product transactions. PS-448 (2026-05-15): expires_at TIMESTAMPTZ and listing_title TEXT added — both missing from original schema, causing 216 production errors on every boost upsert.
• fin_premium_purchases — premium feature transactions
• fin_config — finance module configuration
• fin_reconciliations — daily/monthly reconciliation runs
• fin_reports — generated financial reports
• fin_report_schedules — scheduled report generation
• fin_payouts — payout batch tracking

Ops & admin tables (13)

Operational data for staff workflows:

• support_tickets — user-submitted tickets (categories: account, payment, shipping, refund, seller, buyer, ktp, fraud, technical, other)
• support_macros — CS response macros used by the ops agent. is_active BOOLEAN NOT NULL DEFAULT TRUE; composite index (category, is_active). Added PS-443 (2026-05-14).
• vouchers — promo codes
• campaigns — marketing campaigns. seller_id UUID FK (nullable) added PS-442; indexes on (seller_id) and (status, created_at DESC).
• broadcasts — push/email broadcasts. Index on (status, created_at DESC) added PS-441.
• returns — Dormant. Return flow uses RETURN_* states on orders. This table has no app writes. Pending clean-up. PS-437 (2026-05-14).
• payouts — manual payout records
• fraud_alerts — flagged suspicious activity
• ledger_entries — financial ledger
• shipments — Biteship shipment tracking
• dispute_messages — dispute communication thread
• fee_waivers — exceptional fee waivers
• reservations — atomic listing reservation (prevents double-purchase race)

Infrastructure tables (16)

System-level concerns:

• email_templates — Resend email templates. Tech debt: data lives in TypedKV (email-tpl:{id}), table is empty in production. PS-444.
• ab_tests — A/B test configuration. Tech debt: data lives in TypedKV (ab-test:{id}), table is empty. PS-444.
• webhook_events — incoming webhook log (Xendit, Biteship). Tech debt: data lives in TypedKV (webhook-event:{id}), table is empty. PS-444.
• email_index — Dormant. Never written or read by app code. Suspected email dedup outbox never wired. PS-438.
• live_streams — Live shopping stream records. id TEXT PK (LS + 12 hex), seller FK, status (live/upcoming/ended), pinned_listing_ids TEXT[], viewer counts. Created PS-447 (2026-05-15) — was in batch but never applied to production.
• live_chat_messages — Chat messages in a live stream. stream_id FK → live_streams, user_id TEXT (allows "system" sender), type (message/offer/system). Created PS-447.
• hiring_jobs — SEKEN job postings. 32 columns, bilingual _id/_en fields, TEXT[] arrays for requirements/responsibilities/perks, is_active BOOLEAN. Created PS-446 (2026-05-15).
• hiring_applications — Job applications. job_id FK → hiring_jobs, status (new/reviewed/shortlisted/rejected/hired), attachments JSONB. Created PS-445 (2026-05-15).
• push_subscriptions — device push tokens
• listing_likes — buyer wishlists. One row per (user, listing). bump_listing_like_count trigger maintains listings.likes atomically. Always use listing_likes — never favorites (stale name, fixed PS-434).
• referrals — seller referral tracking. Indexes on (referrer_id) and (buyer_id) added PS-439. Note: referred-user column is buyer_id in schema.
• audit_log — application-wide audit trail
• brand_auth_requests — luxury brand authentication
• duplicate_groups — duplicate listing detection
• auth_verification_tokens — email verification + password reset
• outbox_events — transactional outbox pattern. Drained by the in-process OutboxScheduler (@nestjs/schedule, @Cron every 15 min) in the NestJS backend — no longer an external cron.
• moderation_override_log — ops moderation actions audit
• shipping_rates_cache — Biteship rate cache
• idempotency_keys — duplicate request prevention
• webhook_log — durable DB-backed webhook audit log replacing the stale webhook_events KV. Columns: source, event_type, payload JSONB, status, error_message, idempotency_key, received_at, processed_at. All four production webhook handlers instrument this table fail-open.

RLS pattern

Every table carries a documented access model. Since the NestJS + Drizzle migration, access control is enforced at the application layer — the marketplace's own SEKEN JWT plus the capability matrix. RLS policies still ship in the migrations as defense-in-depth.

Pattern	Used by
Public read, owner write	listings, reviews, user_profiles (limited fields)
Owner only	addresses, bank_accounts, chat_messages, notifications
Backend-only	All fin_*, audit_log, moderation_override_log, all webhook tables
Role-based	support_tickets (admin sees all, user sees own)

Key principle: Clients never mutate data directly. All writes go through NestJS controllers, which validate and apply capability checks before Drizzle persists the change.

Soft delete pattern

Tables that support soft delete have deleted_at (timestamptz, nullable):

• listings, chat_messages, reviews, addresses, bank_accounts

Hard delete is reserved for:

• Failed verification artifacts
• Expired ephemeral tokens
• GDPR/PDP requests

Optimistic locking

Tables that use version column for concurrent update protection:

• orders (state machine transitions)
• listings (concurrent edits)
• fin_seller_balances (concurrent escrow operations)

Update pattern:

UPDATE orders SET status = $1, version = version + 1
WHERE id = $2 AND version = $3

Migration history

Date	Migration	Notes
2026-04-15	KV → tables migration	53 KV patterns → 60 tables
2026-04-23	Order fee column rename	bpf → buyer_protection_fee etc.
2026-04-24	Order state machine	Added 4 states, DB CHECK constraint
2026-06-08	20260608000002_ps943_review_type.sql	PS-943 — adds review_type TEXT NOT NULL DEFAULT 'listing' to reviews
2026-06-08	20260608000003_ps964_voucher_engine.sql	PS-964 — adds voucher columns to orders; adds analytics columns to vouchers
2026-06-08	20260608000004_ps958_webhook_log.sql	PS-958 — creates webhook_log table

Open questions

Question	Owner
Should audit_log be split per domain area?	Engineer

Resolved 2026-04-28: finances schema migration — finance tables stay in public with fin_ prefix; revisit at cuan separation.

Where to look in the codebase

What you want	Where it lives
Migration files	supabase/migrations/ — applied via deploy-time replay onto external Postgres
Schema TypeScript types	backend/src/infrastructure/persistence/row-types.ts
Repository implementations	backend/src/infrastructure/repositories/ (Drizzle over postgres.js)
RLS policy SQL	supabase/migrations/ (defense-in-depth; application layer is the live gate)

---

Next: Ops Portal →