Supabase PostgreSQL Database Schema Reference

This document is the authoritative engineering specification and entity reference for the Debelu PostgreSQL database managed via Supabase. Grounded directly in the base migration [20260315103649_remote_schema.sql](file:///c:/Users/frank/OneDrive/Desktop/Chisom/Debelu/New%20Debelu%20Marketplace/supabase/migrations/20260315103649_remote_schema.sql), the 28-phase launch hardening suite, and subsequent enterprise governance migrations up to 20261011, this specification documents table schemas, foreign key relationships, check constraints, indexes, and database triggers.

1. Schema Conventions & System Design Invariants

mermaid

erDiagram
    users ||--o{ profiles : "identifies (1:1)"
    profiles ||--o{ vendor_profiles : "owns (0:1)"
    profiles ||--o{ orders : "places (buyer)"
    vendor_profiles ||--o{ orders : "fulfills (vendor)"
    vendor_profiles ||--o{ products : "lists"
    products ||--o{ order_items : "contains"
    orders ||--o{ order_items : "comprises"
    orders ||--o{ payments : "settles"
    orders ||--o{ order_fee_snapshots : "freezes"
    orders ||--o{ order_events : "tracks"
    orders ||--o{ disputes : "escalates"
    profiles ||--o{ wallets : "owns"
    wallets ||--o{ wallet_transactions : "records"
    orders ||--o{ atomic_return_cases : "disputes"

1.1 Structural Invariants

Naming Conventions: All schemas, tables, columns, indexes, and constraints use snake_case.
Primary Keys: Every mutable and entity table uses a UUID v4 primary key: id uuid PRIMARY KEY DEFAULT gen_random_uuid().
Temporal Invariants: All domain entities include created_at timestamptz DEFAULT now() NOT NULL and updated_at timestamptz DEFAULT now() NOT NULL. A global trigger trigger_set_updated_at automatically maintains timestamp accuracy.
Monetary Representation: All financial amounts (orders, line items, wallet balances, platform fees, refunds) are stored as 64-bit non-negative integers (bigint) representing Nigerian Kobo ($100 \text{ Kobo} = 1.00 \text{ NGN}$). Floating-point values (numeric/float) are strictly forbidden for balance computations.
Campus Scoping: Campus isolation is enforced through operation_campus text NOT NULL with check constraints (orders_operation_campus_check) preventing cross-campus state mutations.

2. Core Commerce Tables

2.1 Profiles & Identity (`public.profiles`)

Represents the extended member profile synchronized with Supabase Auth (auth.users).

sql

CREATE TABLE public.profiles (
    id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    full_name text NOT NULL,
    email text UNIQUE NOT NULL,
    phone_number text,
    avatar_url text,
    role text DEFAULT 'buyer'::text CHECK (role IN ('buyer', 'vendor', 'moderator', 'admin')),
    status text DEFAULT 'active'::text CHECK (status IN ('active', 'suspended', 'pending_deletion', 'banned')),
    campus text NOT NULL,
    campus_scope text[] DEFAULT '{}'::text[],
    is_verified boolean DEFAULT false,
    bvn_verified boolean DEFAULT false,
    rating numeric(3,2) DEFAULT 0.00,
    review_count integer DEFAULT 0,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX idx_profiles_campus ON public.profiles(campus);
CREATE INDEX idx_profiles_role ON public.profiles(role);

2.2 Vendor Profiles (`public.vendor_profiles`)

Contains merchant store settings, business metadata, and payout configuration.

sql

CREATE TABLE public.vendor_profiles (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid UNIQUE NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
    store_name text NOT NULL,
    slug text UNIQUE NOT NULL CHECK (slug ~ '^[a-z0-9-]+$'),
    description text,
    phone text,
    pickup_location text,
    campus text NOT NULL,
    logo_url text,
    banner_url text,
    bank_account jsonb DEFAULT '{}'::jsonb, -- {bank_name, bank_code, account_number, account_name}
    bank_updated_at timestamptz,
    vacation_mode boolean DEFAULT false,
    whatsapp_notifications boolean DEFAULT false,
    total_sales bigint DEFAULT 0,
    reputation_score numeric(3,2) DEFAULT 5.00,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);
CREATE UNIQUE INDEX idx_vendor_slug ON public.vendor_profiles(slug);
CREATE INDEX idx_vendor_campus ON public.vendor_profiles(campus);

2.3 Products & Inventory (`public.products`)

Stores campus product listings with category links, price snapshots, and inventory counts.

sql

CREATE TABLE public.products (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    vendor_id uuid NOT NULL REFERENCES public.vendor_profiles(id) ON DELETE CASCADE,
    title text NOT NULL,
    description text NOT NULL,
    price bigint NOT NULL CHECK (price > 0),
    original_price bigint CHECK (original_price >= price),
    category_id uuid NOT NULL REFERENCES public.categories(id),
    condition text DEFAULT 'new'::text CHECK (condition IN ('new', 'like_new', 'good', 'fair')),
    images text[] NOT NULL DEFAULT '{}'::text[],
    inventory_count integer NOT NULL DEFAULT 1 CHECK (inventory_count >= 0),
    is_active boolean DEFAULT true,
    is_exclusive boolean DEFAULT false,
    campus text NOT NULL,
    views_count integer DEFAULT 0,
    favorites_count integer DEFAULT 0,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX idx_products_vendor ON public.products(vendor_id);
CREATE INDEX idx_products_category ON public.products(category_id);
CREATE INDEX idx_products_campus_active ON public.products(campus, is_active);
CREATE INDEX idx_products_price ON public.products(price);

2.4 Orders & Escrow Ledger (`public.orders`)

The central entity for order orchestration and escrow financial holding.

sql

CREATE TABLE public.orders (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    buyer_id uuid NOT NULL REFERENCES public.profiles(id),
    vendor_id uuid NOT NULL REFERENCES public.vendor_profiles(id),
    total_amount bigint NOT NULL CHECK (total_amount > 0),
    subtotal_amount bigint NOT NULL,
    delivery_fee bigint NOT NULL DEFAULT 0,
    platform_fee bigint NOT NULL DEFAULT 0,
    status text NOT NULL DEFAULT 'pending_payment'::text CHECK (
        status IN (
            'pending_payment',
            'paid_escrow',
            'processing',
            'shipped',
            'delivered_pending_verification',
            'completed',
            'cancelled',
            'disputed',
            'refunded'
        )
    ),
    delivery_type text NOT NULL CHECK (delivery_type IN ('pickup_hub', 'hostel_delivery', 'direct_handoff')),
    delivery_address jsonb,
    delivery_pin text CHECK (delivery_pin ~ '^\d{6}$'), -- 6-digit confirmation code
    escrow_released_at timestamptz,
    operation_campus text NOT NULL,
    revision integer DEFAULT 1 NOT NULL, -- Optimistic concurrency counter
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX idx_orders_buyer ON public.orders(buyer_id);
CREATE INDEX idx_orders_vendor ON public.orders(vendor_id);
CREATE INDEX idx_orders_status ON public.orders(status);
CREATE INDEX idx_orders_campus_status ON public.orders(operation_campus, status);

2.5 Order Fee Snapshots (`public.order_fee_snapshots`)

Guarantees absolute immutability of fees calculated at the moment of payment intent creation (20261011000400_order_fee_snapshots.sql).

sql

CREATE TABLE public.order_fee_snapshots (
    order_id uuid PRIMARY KEY REFERENCES public.orders(id) ON DELETE CASCADE,
    subtotal_kobo bigint NOT NULL,
    platform_fee_kobo bigint NOT NULL,
    delivery_fee_kobo bigint NOT NULL,
    vendor_payout_kobo bigint NOT NULL,
    commission_rate_bps integer NOT NULL, -- Basis points (e.g., 500 = 5.0%)
    applied_rules jsonb NOT NULL DEFAULT '{}'::jsonb,
    frozen_at timestamptz DEFAULT now() NOT NULL
);

3. Financial, Payout & Ledger Tables

3.1 Double-Entry General Ledger (`public.transactions`)

Every financial movement creates immutable credit/debit records balancing assets and liabilities.

sql

CREATE TABLE public.transactions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    reference text UNIQUE NOT NULL,
    account_id uuid NOT NULL, -- User wallet or Platform Reserve account
    order_id uuid REFERENCES public.orders(id),
    amount bigint NOT NULL, -- Positive for credits, negative for debits
    entry_type text NOT NULL CHECK (entry_type IN ('escrow_deposit', 'escrow_release', 'payout_transfer', 'refund', 'fee_deduction', 'adjustment')),
    description text NOT NULL,
    balance_after bigint NOT NULL,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX idx_transactions_account ON public.transactions(account_id);
CREATE INDEX idx_transactions_order ON public.transactions(order_id);
CREATE INDEX idx_transactions_created_at ON public.transactions(created_at);

3.2 Payout Transfer Intents (`public.payout_transfers`)

Records bank transfers initiated to student vendors via Paystack (20261011001100_payout_transfer_intents.sql).

sql

CREATE TABLE public.payout_transfers (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id uuid REFERENCES public.payout_batches(id),
    vendor_id uuid NOT NULL REFERENCES public.vendor_profiles(id),
    amount_kobo bigint NOT NULL CHECK (amount_kobo > 0),
    recipient_code text NOT NULL, -- Paystack transfer recipient code
    transfer_reference text UNIQUE NOT NULL,
    status text NOT NULL DEFAULT 'staged'::text CHECK (status IN ('staged', 'approved', 'dispatched', 'success', 'failed', 'reversed')),
    bank_account_snapshot jsonb NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX idx_payout_transfers_vendor ON public.payout_transfers(vendor_id);
CREATE INDEX idx_payout_transfers_status ON public.payout_transfers(status);

3.3 Maker-Checker Reconciliation (`public.payout_transfer_reconciliation`)

Audits dual-authorization interventions and reversal restatements (20261011001400_payout_transfer_reconciliation.sql).

sql

CREATE TABLE public.payout_transfer_reconciliation (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    transfer_id uuid NOT NULL REFERENCES public.payout_transfers(id),
    requested_by uuid NOT NULL REFERENCES public.profiles(id),
    approved_by uuid REFERENCES public.profiles(id),
    action_type text NOT NULL CHECK (action_type IN ('manual_reversal', 'retry_dispatch', 'force_settle')),
    reason text NOT NULL,
    status text NOT NULL DEFAULT 'pending'::text CHECK (status IN ('pending', 'approved', 'rejected', 'executed')),
    executed_at timestamptz,
    created_at timestamptz DEFAULT now() NOT NULL,
    CONSTRAINT check_maker_checker_distinct CHECK (requested_by <> approved_by)
);

4. Trust, Safety & Moderation Tables

4.1 Content Flags (`public.content_flags`)

Intake queue for peer-reported violations across listings, messages, and member profiles.

sql

CREATE TABLE public.content_flags (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    reporter_id uuid NOT NULL REFERENCES public.profiles(id),
    target_type text NOT NULL CHECK (target_type IN ('product', 'review', 'user', 'message')),
    target_id uuid NOT NULL,
    reason text NOT NULL,
    notes text,
    status text NOT NULL DEFAULT 'pending'::text CHECK (status IN ('pending', 'investigating', 'resolved', 'dismissed')),
    reviewer_id uuid REFERENCES public.profiles(id),
    action_taken text,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX idx_content_flags_status ON public.content_flags(status);
CREATE INDEX idx_content_flags_target ON public.content_flags(target_type, target_id);

4.2 Moderation Cases & Strike Escalation (`public.moderation_cases`)

Tracks disciplinary enforcement and the 3-strike vendor deplatforming ladder (20261005_002_strike_system.sql).

sql

CREATE TABLE public.moderation_cases (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    subject_id uuid NOT NULL REFERENCES public.profiles(id),
    assigned_moderator_id uuid REFERENCES public.profiles(id),
    severity text NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    strike_count integer DEFAULT 0 CHECK (strike_count BETWEEN 0 AND 3),
    enforcement_action text CHECK (enforcement_action IN ('warning', 'listing_quarantine', 'temporary_suspension', 'permanent_ban')),
    evidence_urls text[] DEFAULT '{}'::text[],
    is_closed boolean DEFAULT false,
    closed_at timestamptz,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX idx_moderation_cases_subject ON public.moderation_cases(subject_id);

5. Support, Disputes & Messaging Tables

5.1 Escrow Disputes (`public.disputes`)

Formally disputes an order, freezing escrow payout until mediation concludes.

sql

CREATE TABLE public.disputes (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id uuid UNIQUE NOT NULL REFERENCES public.orders(id),
    opened_by uuid NOT NULL REFERENCES public.profiles(id),
    reason text NOT NULL,
    description text NOT NULL,
    evidence_urls text[] DEFAULT '{}'::text[],
    status text NOT NULL DEFAULT 'open'::text CHECK (status IN ('open', 'under_review', 'resolved_buyer_refund', 'resolved_vendor_payout', 'cancelled')),
    resolution_notes text,
    resolved_by uuid REFERENCES public.profiles(id),
    resolved_at timestamptz,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX idx_disputes_order ON public.disputes(order_id);
CREATE INDEX idx_disputes_status ON public.disputes(status);

5.2 Direct Conversations & Messages (`public.conversations`, `public.messages`)

Encapsulates real-time buyer-vendor chat with ChatGuard protection.

sql

CREATE TABLE public.conversations (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    buyer_id uuid NOT NULL REFERENCES public.profiles(id),
    vendor_id uuid NOT NULL REFERENCES public.vendor_profiles(id),
    product_id uuid REFERENCES public.products(id),
    last_message_at timestamptz DEFAULT now() NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL,
    CONSTRAINT unique_buyer_vendor_product UNIQUE (buyer_id, vendor_id, product_id)
);

CREATE TABLE public.messages (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id uuid NOT NULL REFERENCES public.conversations(id) ON DELETE CASCADE,
    sender_id uuid NOT NULL REFERENCES public.profiles(id),
    body text NOT NULL,
    is_flagged boolean DEFAULT false,
    flag_reason text,
    is_read boolean DEFAULT false,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX idx_messages_conversation ON public.messages(conversation_id, created_at);

6. Privacy, Statutory Compliance & Audit Tables

6.1 Audit Log Immutability (`public.audit_log`)

Append-only tamper-evident compliance audit trail (20261008_002_audit_immutability.sql).

sql

CREATE TABLE public.audit_log (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    actor_id uuid NOT NULL,
    actor_role text NOT NULL,
    action text NOT NULL,
    resource_type text NOT NULL,
    resource_id text NOT NULL,
    old_state jsonb,
    new_state jsonb,
    ip_address text,
    user_agent text,
    created_at timestamptz DEFAULT now() NOT NULL
);
-- Strictly revocable table permissions: NO UPDATE, NO DELETE
REVOKE UPDATE, DELETE ON public.audit_log FROM PUBLIC, authenticated, anon;
CREATE INDEX idx_audit_log_actor ON public.audit_log(actor_id);
CREATE INDEX idx_audit_log_resource ON public.audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_log_created_at ON public.audit_log(created_at);

6.2 Data Subject Privacy Exports (`public.subject_privacy_exports`)

Governs statutory NDPA/GDPR personal data export archives (20261011002500_subject_privacy_export_delivery.sql).

sql

CREATE TABLE public.subject_privacy_exports (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
    artifact_storage_path text NOT NULL,
    artifact_sha256 text NOT NULL, -- SHA-256 integrity digest
    artifact_size_bytes bigint NOT NULL CHECK (artifact_size_bytes <= 10485760), -- Max 10 MB bound
    download_nonce_hash text NOT NULL, -- Single-use handshake hash
    expires_at timestamptz NOT NULL, -- 7-day statutory TTL
    downloaded_at timestamptz,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX idx_privacy_exports_user ON public.subject_privacy_exports(user_id);

7. Row-Level Security (RLS) Policy Summary

Table	Policy Name	Permitted Roles	SQL Evaluation Expression (`USING` / `WITH CHECK`)
`profiles`	`profiles_read_public`	`anon`, `authenticated`	`true` (Public user directory)
`profiles`	`profiles_update_self`	`authenticated`	`auth.uid() = id`
`vendor_profiles`	`vendor_read_public`	`anon`, `authenticated`	`true`
`vendor_profiles`	`vendor_update_owner`	`authenticated`	`user_id = auth.uid()`
`products`	`products_read_active`	`anon`, `authenticated`	`is_active = true OR vendor_id IN (SELECT id FROM vendor_profiles WHERE user_id = auth.uid())`
`products`	`products_write_vendor`	`authenticated`	`vendor_id IN (SELECT id FROM vendor_profiles WHERE user_id = auth.uid())`
`orders`	`orders_participant_read`	`authenticated`	`buyer_id = auth.uid() OR vendor_id IN (SELECT id FROM vendor_profiles WHERE user_id = auth.uid()) OR auth.jwt() ->> 'role' IN ('admin', 'moderator')`
`transactions`	`transactions_owner_read`	`authenticated`	`account_id = auth.uid()`
`audit_log`	`audit_log_staff_read`	`authenticated` (Staff)	`auth.jwt() ->> 'role' IN ('admin', 'moderator')`

8. Database Functions & Triggers

8.1 Automated `updated_at` Refresh

sql

CREATE OR REPLACE FUNCTION public.handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Applied as a BEFORE UPDATE trigger across all domain tables.

8.2 Inventory Decrement Protection

sql

CREATE OR REPLACE FUNCTION public.decrement_product_inventory(p_product_id uuid, p_quantity integer)
RETURNS void AS $$
BEGIN
    UPDATE public.products
    SET inventory_count = inventory_count - p_quantity
    WHERE id = p_product_id AND inventory_count >= p_quantity;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient stock for product %' USING ERRCODE = '23514';
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

9. Document Revision History

Revision	Date	Lead Author	Scope of Changes	Status
`1.0.0`	2026-10-05	Principal Data Architect	Comprehensive PostgreSQL schema specification detailing core commerce, financial ledger, moderation, RLS policies, check constraints, and triggers.	Active Living Standard

Supabase PostgreSQL Database Schema Reference ​

1. Schema Conventions & System Design Invariants ​

1.1 Structural Invariants ​

2. Core Commerce Tables ​

2.1 Profiles & Identity (public.profiles) ​

2.2 Vendor Profiles (public.vendor_profiles) ​

2.3 Products & Inventory (public.products) ​

2.4 Orders & Escrow Ledger (public.orders) ​

2.5 Order Fee Snapshots (public.order_fee_snapshots) ​

3. Financial, Payout & Ledger Tables ​

3.1 Double-Entry General Ledger (public.transactions) ​

3.2 Payout Transfer Intents (public.payout_transfers) ​

3.3 Maker-Checker Reconciliation (public.payout_transfer_reconciliation) ​

4. Trust, Safety & Moderation Tables ​

4.1 Content Flags (public.content_flags) ​

4.2 Moderation Cases & Strike Escalation (public.moderation_cases) ​

5. Support, Disputes & Messaging Tables ​

5.1 Escrow Disputes (public.disputes) ​

5.2 Direct Conversations & Messages (public.conversations, public.messages) ​

6. Privacy, Statutory Compliance & Audit Tables ​

6.1 Audit Log Immutability (public.audit_log) ​

6.2 Data Subject Privacy Exports (public.subject_privacy_exports) ​

7. Row-Level Security (RLS) Policy Summary ​

8. Database Functions & Triggers ​

8.1 Automated updated_at Refresh ​

8.2 Inventory Decrement Protection ​

9. Document Revision History ​