Database
Seken's data layer, how data is structured, how to read and write it safely, and the key patterns every developer must know.
Architecture: Relational First​
All application data lives in 78 PostgreSQL tables on an external PostgreSQL instance (self-managed, connected via Supavisor session pooler). The original KV store (kv_store_e10b1b67) was fully retired in Phase 14 (April 2026). Do not reference or write to it.
Every mutation routes through the NestJS backend using Drizzle ORM. Clients never touch the database directly.
When you need to store new data, add a column to an existing table or create a new table. Never use the KV store for new features.
Access Pattern​
Client → NestJS controller → Repository → Drizzle ORM → external PostgreSQL
NestJS controllers validate the request (auth headers, Zod/class-validator schema), call a repository method, and return the result. Clients receive typed JSON; they never query the database directly.
All writes must go through the NestJS backend:
// In a repository (correct)
await this.db
.update(listings)
.set({ status: "sold" })
.where(eq(listings.id, listingId));
// Never from the frontend (wrong)
// Direct DB access bypasses business logic and auth
Core Marketplace Tables​
user_profiles​
Every authenticated user has a row here (FK to auth.users).
Key columns: id, email, username, display_name, avatar_url, role (buyer/seller/ops_admin/finance_admin/superadmin), account_status (active/suspended/banned), ktp_status (none/pending/approved/rejected), verified_seller, is_seller, phone.
phoneauto-fill (PS-883, 2026-06-02): the user's primaryaddressesrow back-fillsphonehere on any primary-address change, fill-in only, so a phone the user set directly (e.g. via the add-phone prompt on the shipping gate) is never overwritten. The value is normalized to+62form. This is the field required for Biteship contact details (see Database Schema → addresses).
listings​
Every seller-created item. The central entity.
Key columns:
| Column | Type | Notes |
|---|---|---|
id | uuid | Primary key |
seller_id | uuid | FK to user_profiles |
title, description | text | |
price | integer | IDR, no decimals |
category, sub_category, brand, condition | text | |
image_urls | text[] | Up to 8 DigitalOcean Spaces URLs (served via imgproxy) |
vibe_tags | text[] | |
status | text | active, sold, draft, reserved, archived |
moderation_status | text | pending_review, approved, rejected, flagged. Default: pending_review. Public listings gate on approved. |
weight_grams | integer | For Biteship shipping rate calculation. Default 500. |
likes | integer | Direct column. Authoritative like count, maintained by bump_listing_like_count trigger (see below). |
metadata | jsonb | Overflow fields, see pattern below. |
deleted_at | timestamptz | Soft delete. |
Metadata JSONB Overflow Pattern​
listings.metadata is a JSONB catch-all for fields that were not promoted to direct columns. These include: original_price, product_category, gender, sub_category, is_live, ai_confidence, is_promoted, boost_type, _storage_paths, _urls_refreshed_at.
SupabaseListingRepository.listingRowToKV flattens these to the top level of the returned object so callers receive a flat shape without needing to dig into metadata.
Critical rule: Some fields exist in both places, likes and views have a direct column (authoritative) AND a JSONB copy (stale, written only at listing creation time). When the mapper reads these fields, the direct column must take priority:
// Correct
likes: (row.likes as number | undefined) ?? meta.likes ?? 0,
// Wrong — reads stale JSONB, ignores trigger-maintained column
likes: meta.likes ?? 0,
PS-237 (2026-05-08) was caused by the mapper reading meta.likes and discarding the trigger-maintained row.likes. Love counts displayed as 0 on all listing cards despite the DB having correct values.
orders​
Transactional spine. 14 states enforced by a DB CHECK constraint (SCREAMING_SNAKE_CASE).
State flow:
AWAITING_PAYMENT → PAYMENT_REVIEW → READY_TO_SHIP → SHIPPED → DELIVERED → COMPLETED
↓
RETURN_REQUESTED → RETURN_APPROVED → RETURN_RECEIVED → REFUNDED
↓
DISPUTE_OPEN → DISPUTE_RESOLVED
Key columns: buyer_id, seller_id, listing_id, order_status, version (optimistic locking), xendit_invoice_id, total_paid, tracking_number, dispute_window_start_at.
Social & Messaging Tables​
| Table | Purpose |
|---|---|
chat_threads | Conversation container per (buyer, seller, listing) triple |
chat_messages | Individual messages. consumed_at + consumed_by_order_id enable atomic offer-to-order claim (C-1). |
reviews | Post-completion buyer reviews. Anti-gaming: is_suspicious, is_visible, flagged_reason. |
seller_scores | Computed seller reputation. Affects search ranking and boost eligibility. |
listing_likes | One row per (user, listing) pair. The bump_listing_like_count SECURITY DEFINER trigger fires AFTER INSERT and AFTER DELETE to atomically update listings.likes. Never update listings.likes directly, use the toggle endpoint. Always use listing_likes, the name favorites is a stale alias that no longer exists (PS-434, 2026-05-14). |
Ops & Admin Tables​
| Table | Purpose |
|---|---|
support_tickets | User-submitted help tickets |
support_macros | CS response macros used by the ops agent. is_active BOOLEAN NOT NULL DEFAULT TRUE column. Index on (category, is_active). |
campaigns | Marketing campaigns. seller_id UUID FK (nullable) column. Indexes on (seller_id) and (status, created_at DESC). |
broadcasts | Push/email broadcast sends. Index on (status, created_at DESC). |
returns | Dormant. Return flow uses RETURN_* states on orders. Never written by app code. |
vouchers | Promo code definitions |
reservations | Atomic listing lock at checkout (listing_id is PK, prevents double-purchase) |
Infrastructure Tables​
| Table | Purpose |
|---|---|
push_subscriptions | VAPID web push subscriptions |
webhook_events | Incoming webhook log (Xendit, Biteship). Tech debt: data lives in TypedKV, table is empty in production. |
email_templates | Resend email templates. Tech debt: data lives in TypedKV, table is empty in production. |
ab_tests | A/B test config. Tech debt: data lives in TypedKV, table is empty in production. |
outbox_events | Transactional outbox (15-minute processor, DDD pattern) |
idempotency_keys | Duplicate request prevention for Xendit webhooks |
auth_verification_tokens | Email verification and password reset tokens |
moderation_override_log | Ops moderation action audit trail |
search_terms | Trending search term tracking. RLS: SELECT open; INSERT/UPDATE service_role only. |
referrals | Seller referral tracking. Indexes on (referrer_id) and (buyer_id). Note: referred-user column is buyer_id. |
live_streams | Live shopping stream records. id TEXT PK (format: LS + 12 hex chars, not a UUID). status CHECK (live/upcoming/ended), pinned_listing_ids TEXT[], viewers INT, seller_id UUID FK. Created PS-447 (2026-05-15). |
live_chat_messages | Messages within a live stream. stream_id TEXT FK → live_streams, user_id TEXT (allows the "system" sender, never cast to UUID), type CHECK (message/offer/system). Created PS-447. |
hiring_jobs | Job postings. Bilingual _id/_en column pairs for all display fields. TEXT[] for responsibilities, requirements, nice-to-have, perks. is_active BOOLEAN DEFAULT true. Created PS-446 (2026-05-15). |
hiring_applications | Job applications. job_id UUID FK → hiring_jobs, status CHECK (new/reviewed/shortlisted/rejected/hired), attachments JSONB DEFAULT '[]'. RLS: ops_admin/superadmin read; service_role write. Created PS-445 (2026-05-15). |
Finance Tables (fin_ prefix)​
15 tables prefixed fin_. All service-role-only RLS. Key tables:
| Table | Purpose |
|---|---|
fin_orders | Finance-side record per marketplace order |
fin_seller_balances | Running escrow balance per seller |
fin_disbursements | Payout records with maker-checker approval |
fin_audit_log | Before/after JSONB for every financial mutation, source of truth for reconciliation |
fin_commission_rules | Platform fee config. Seller commission is always 0. |
fin_boost_purchases | Boost purchase records. PS-448 (2026-05-15): expires_at TIMESTAMPTZ and listing_title TEXT added, both were absent from the original schema, causing every boost upsert to fail (216 production errors). |
RLS Pattern​
Every table has Row-Level Security enabled. Clients never bypass this because they never hit the database directly. The NestJS backend connects with a privileged database role and must still enforce business rules in application code before writing.
| Pattern | Used by |
|---|---|
| Public read, owner write | listings, reviews, user_profiles (limited fields) |
| Owner only | addresses, bank_accounts, chat_messages, notifications |
| Service role only | All fin_*, audit_log, moderation_override_log, webhook tables |
| Role-based | support_tickets (admin sees all, user sees own) |
The backend database credential bypasses RLS at the driver level. Business-logic access control must be enforced in application code before any write. Never leak connection credentials to client-side code.
Soft Delete​
Hard delete is not used for application data. All deleteable tables have deleted_at TIMESTAMPTZ:
listings,chat_messages,reviews,addresses,bank_accounts
Queries must filter WHERE deleted_at IS NULL unless intentionally reading deleted records (ops only).
Optimistic Locking​
Tables with concurrent write risk use a version INTEGER column:
| Table | Why |
|---|---|
orders | State machine transitions, two webhooks must not apply simultaneously |
listings | Concurrent seller edits |
fin_seller_balances | Concurrent escrow operations |
Update pattern:
UPDATE orders
SET order_status = $1, version = version + 1
WHERE id = $2 AND version = $3
-- 0 rows updated → version conflict → retry
Database Environments​
| Environment | Branch | Notes |
|---|---|---|
| Production | main | External PostgreSQL, connected via Supavisor session pooler. Migrations apply automatically at deploy time. |
| Staging | develop | Separate external PostgreSQL instance. Migrations apply on CI green. |
Never mix DATABASE_URL targets between environments. Always confirm with the Engineer before running a migration against production.
Storage​
File storage has moved from Supabase Storage to DigitalOcean Spaces (S3-compatible). Images are served through imgproxy for on-the-fly transforms (resize, format conversion). Bucket names follow the same logical grouping as before; get current bucket names from the team's secrets manager.
| Logical bucket | Access | Contents |
|---|---|---|
| listing-photos | Public (via imgproxy CDN) | Listing photos |
| ktp-docs | Private | KTP photos and selfies (admin access only) |
| dispute-evidence | Private | Dispute evidence photos |
| chat-photos | Private | Buyer-seller chat images |
| hiring-docs | Private | Hiring document uploads |
| backups | Private | Automated backups |
Where to Look in the Codebase​
| What you need | Where it is |
|---|---|
| Migration files | supabase/migrations/ (path name is historical; migrations still live here) |
| Drizzle schema definitions | backend/src/infrastructure/database/schema/ |
| Repository implementations | backend/src/infrastructure/repositories/ |
| RLS policy SQL | supabase/migrations/*_rls_*.sql and *_enable_rls_*.sql |
Useful Queries​
-- Count active approved listings
SELECT count(*) FROM listings
WHERE status = 'active'
AND moderation_status = 'approved'
AND deleted_at IS NULL;
-- Orders by status
SELECT order_status, count(*)
FROM orders
GROUP BY order_status
ORDER BY count(*) DESC;
-- Listings with the most likes
SELECT id, title, likes
FROM listings
WHERE deleted_at IS NULL
ORDER BY likes DESC
LIMIT 20;
-- A seller's active listings
SELECT id, title, status, likes
FROM listings
WHERE seller_id = 'SELLER_UUID'
AND deleted_at IS NULL
ORDER BY created_at DESC;