Skip to main content

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​

MetricValue
Total tables81
Functional areas8
RLS coverage100%
Ephemeral KV keys remaining3 (health-check, error, durable_rl)
Migration sourceKV store (fully retired)
Last audit2026-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.

PatternUsed by
Public read, owner writelistings, reviews, user_profiles (limited fields)
Owner onlyaddresses, bank_accounts, chat_messages, notifications
Backend-onlyAll fin_*, audit_log, moderation_override_log, all webhook tables
Role-basedsupport_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​

DateMigrationNotes
2026-04-15KV → tables migration53 KV patterns → 60 tables
2026-04-23Order fee column renamebpf → buyer_protection_fee etc.
2026-04-24Order state machineAdded 4 states, DB CHECK constraint
2026-06-0820260608000002_ps943_review_type.sqlPS-943 — adds review_type TEXT NOT NULL DEFAULT 'listing' to reviews
2026-06-0820260608000003_ps964_voucher_engine.sqlPS-964 — adds voucher columns to orders; adds analytics columns to vouchers
2026-06-0820260608000004_ps958_webhook_log.sqlPS-958 — creates webhook_log table

Open questions​

QuestionOwner
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 wantWhere it lives
Migration filessupabase/migrations/ — applied via deploy-time replay onto external Postgres
Schema TypeScript typesbackend/src/infrastructure/persistence/row-types.ts
Repository implementationsbackend/src/infrastructure/repositories/ (Drizzle over postgres.js)
RLS policy SQLsupabase/migrations/ (defense-in-depth; application layer is the live gate)

Next: Ops Portal →