nick-doc/02 - Data Models/Data Model Overview.md

---
title: Data Model Overview
tags: [data-model, postgres, drizzle, overview]
aliases: [Models Index, Schema Overview]
---

# Data Model Overview

This section documents every Drizzle/PostgreSQL table that backs the marketplace. PostgreSQL is the primary and sole data store as of v2.9.12 (2026-06-06). The Mongo dual-write layer has been retired; all reads and writes are served from Postgres. The Drizzle schema has 17 applied migrations (0000–0017).

> [!note] Scope
> Twenty-two domain entities are modelled. The "File" concept exists only at the service layer (`backend/src/services/file/`) and is not persisted as its own table, so it is not listed below.
>
> [!note] Documentation freshness
> As of 2026-06-06 (v2.9.12) the Postgres migration inventory reflects migrations 0000–0017. The table inventory at the bottom of this page is the authoritative schema-status reference. Individual model pages should be updated to note their PG table name and any notable constraints.

> [!info] PostgreSQL runtime status
> PostgreSQL is the sole data store for all domain tables. The Mongo dual-write layer has been fully retired. All reads and writes now go directly to Postgres. Infra/bridge tables (`id_map`, `pg_dualwrite_gaps`) and oracle quote rows (`payment_quotes`) remain PG-only as before.

## Index of Models

### Domain Models

- [[User]] — Core identity. Stores credentials, profile, preferences, referral data, points, and WebAuthn passkeys. Every other model that records "who did what" points back at a `User.id` (UUID). Buyers, sellers, admins, resolvers, and guards all live in this table, differentiated by a `role` enum. PG table: `users`.
- [[PurchaseRequest]] — The buyer-side record at the heart of the marketplace. Captures what a buyer wants, the budget, urgency, delivery preferences, and the full lifecycle status (`pending_payment` → `seller_paid`). Aggregates [[SellerOffer]] references and tracks delivery codes. PG table: `purchase_requests` + 6 child tables.
- [[SellerOffer]] — A seller's bid against a [[PurchaseRequest]]. Holds price, delivery ETA, attachments, and a small status machine (`pending` / `accepted` / `rejected` / `withdrawn`). PG table: `seller_offers`.
- [[Payment]] — Records monetary movement intent and state: buyer pay-in, seller release, and refund. The current primary provider path is Request Network plus in-house checkout, derived destinations, funds ledger entries, and Transaction Safety Provider metadata. PG table: `payments`.
- [[Chat]] — Conversation container with embedded messages, participants, unread counters, and reactions. Used for direct buyer-seller chats, group chats, and support tickets. Can be linked to a [[PurchaseRequest]] or [[SellerOffer]]. PG table: `chats` (JSONB shim; Chat normalization is an open follow-up).
- [[Notification]] — Per-user notification with category, type, and 90-day TTL for automatic cleanup. References any related entity by stringified id. PG table: `notifications` (`user_id` stored as `text`, no hard FK).
- [[RequestTemplate]] — A seller-authored, sharable template that pre-fills a [[PurchaseRequest]]. Carries a public shareable link, usage counter, and an optional default proposal. PG table: `request_templates`.
- [[Dispute]] — Buyer-raised complaint tied to a [[PurchaseRequest]]. Captures evidence uploads, a timeline of admin actions, deadlines, and a structured resolution. PG table: `disputes` (all IDs as `text` for legacy coexistence).
- [[BlogPost]] — Editorial content: title, slug, rich content, media, SEO metadata, view/like counters, and a draft/published/archived workflow. PG table: `blog_posts`.
- [[Address]] — User shipping address book entry. Enforces a single primary address per user via a partial-unique index. PG table: `addresses` (migration 0016; `addressStore.ts` reads PG directly).
- [[Category]] — Hierarchical product/service taxonomy referenced by [[PurchaseRequest]] and [[RequestTemplate]]. Supports parent/child via `parent_id` and bilingual `name` / `name_en`. PG table: `categories`.
- [[Review]] — Polymorphic 1-5 star review against either a seller or a [[RequestTemplate]] (`subject_type` discriminator). One review per reviewer per subject (compound unique index). PG table: `reviews` (schema scaffolded, no write repo yet).
- [[PointTransaction]] — Ledger of point grants and spends per user. Sources include purchase, referral, bonus, admin grant, and redemption. PG table: `point_transactions`.
- [[LevelConfig]] — Static configuration of loyalty tiers (level number, point thresholds, benefits, icon, color). Driven by admins; consumed by the `users.points_level` field. No PG table (read-only config; not yet migrated).
- [[ShopSettings]] — One-to-one storefront branding for a seller: name, description, avatar, cover image, review toggles, and social links. PG table: `shop_settings` (schema scaffolded, no write repo yet).
- [[TempVerification]] — Short-lived signup record that holds candidate user data and a verification code. Auto-purges via scheduled job when `email_verification_code_expires` passes. No PG table (TTL-only; not yet migrated).
- [[TelegramLink]] — Permanent auditable association between a Telegram user ID and an Amanat [[User]]. Stores Telegram profile metadata, link source (`miniapp` / `bot` / `login_widget`), status (`active` / `blocked`), and last-seen timestamp. One per Telegram user (unique on both `user_id` and `telegram_user_id`). PG table: `telegram_links` (schema scaffolded, no write repo yet).
- [[TelegramSession]] — Short-lived Telegram Mini App session token issued when `initData` is verified. Carries the `init_data_fingerprint` for replay protection and auto-expires via scheduled cleanup on `expires_at`. PG table: `telegram_sessions` (schema scaffolded, no write repo yet).
- [[ConfigSetting]] — Runtime configuration persisted in Postgres for operational knobs that need an admin surface rather than a deploy. PG table: `config_settings` (schema scaffolded, no write repo yet).
- [[DerivedDestination]] — Per-payment derived wallet destination records used to reduce address reuse and reconcile on-chain pay-ins. PG table: `derived_destinations` + `derived_destination_sweeps`.
- [[FundsLedgerEntry]] — Immutable accounting ledger rows for pay-in, hold, release, refund, fee, adjustment, and reversal events. PG table: `funds_ledger_entries` (immutability enforced by DB trigger since migration 0015).
- [[TrezorAccount]] — Hardware-wallet/safekeeping account metadata for custody operations and staged signer hardening. PG table: `trezor_accounts` + `trezor_derived_addresses`.
- [[ConfigSettingHistory]] — Immutable audit trail of numeric runtime-config changes. Currently used for per-chain confirmation threshold change events, keyed as `confirmation_threshold:<chainId>`. Added in commit `27fb15a`. PG table: `config_setting_history` (PG-only; no legacy equivalent).

### PG-Only Tables (infrastructure / bridge)

- `id_map` — Legacy ObjectId → UUID bridge. Retained for reference during any remaining data reconciliation. Composite PK on `(collection, legacy_object_id)`, unique on `new_id`.
- `pg_dualwrite_gaps` — Append-only reconciliation gap log from the dual-write era. Tracks collection, op, payload, severity, and resolution metadata.
- `payment_quotes` — Oracle pricing quotes per payment (oracle depeg-protection feature). Stores `fx_rate`, `token_price_usd`, `depeg_adjustment_bps`, `settle_amount`, chain/token, and expiry. Requires `ORACLE_QUOTING_ENABLED=true`. 1:1 to `payments`.
- `user_passkeys` — WebAuthn credential store (child of `users`). Columns: credential id (text PK), `user_id FK→users CASCADE`, `public_key`, `counter`, `device_type`, `device_name`.
- `user_refresh_tokens` — Refresh token store (child of `users`). Columns: `token text PK`, `user_id FK→users CASCADE`.

## Relationship Diagram

```mermaid
erDiagram
    USER ||--o{ PURCHASE_REQUEST : "creates as buyer"
    USER ||--o{ SELLER_OFFER : "submits as seller"
    USER ||--o{ ADDRESS : "owns"
    USER ||--o{ NOTIFICATION : "receives"
    USER ||--o{ POINT_TRANSACTION : "earns/spends"
    USER ||--o{ REQUEST_TEMPLATE : "authors as seller"
    USER ||--o| SHOP_SETTINGS : "configures"
    USER ||--o{ BLOG_POST : "publishes"
    USER ||--o{ REVIEW : "writes as reviewer"
    USER ||--o{ DISPUTE : "raises as buyer"
    USER ||--o{ USER : "referred by"
    USER ||--o{ TREZOR_ACCOUNT : "controls custody account"
    USER ||--o{ USER_PASSKEY : "authenticates with"
    USER ||--o{ USER_REFRESH_TOKEN : "sessions via"
    USER ||--o| TELEGRAM_LINK : "links identity"
    USER ||--o{ TELEGRAM_SESSION : "session for"

    PURCHASE_REQUEST }o--|| CATEGORY : "belongs to"
    PURCHASE_REQUEST ||--o{ SELLER_OFFER : "receives"
    PURCHASE_REQUEST ||--o{ PAYMENT : "settled by"
    PURCHASE_REQUEST ||--o| CHAT : "discussed in"
    PURCHASE_REQUEST ||--o{ DISPUTE : "may trigger"
    PURCHASE_REQUEST ||--o| REVIEW : "rated by buyer"

    SELLER_OFFER ||--o| PAYMENT : "funds"
    SELLER_OFFER }o--|| PURCHASE_REQUEST : "responds to"

    PAYMENT }o--|| USER : "buyer"
    PAYMENT }o--|| USER : "seller"
    PAYMENT ||--o{ FUNDS_LEDGER_ENTRY : "accounted by"
    PAYMENT ||--o| DERIVED_DESTINATION : "collects into"
    PAYMENT ||--o| PAYMENT_QUOTE : "oracle-priced by"

    CHAT }o--o{ USER : "participants"
    CHAT ||--o{ DISPUTE : "support channel"

    REQUEST_TEMPLATE }o--|| CATEGORY : "belongs to"
    REQUEST_TEMPLATE ||--o{ REVIEW : "rated as subject"

    CATEGORY ||--o{ CATEGORY : "parent of"

    POINT_TRANSACTION }o--|| USER : "owner"
    LEVEL_CONFIG ||..|| USER : "level lookup"

    TEMP_VERIFICATION ||..|| USER : "promoted to"

    TELEGRAM_LINK }o--|| USER : "links identity"
    TELEGRAM_SESSION }o--o| USER : "session for"
    TELEGRAM_SESSION }o--|| TELEGRAM_LINK : "matches"

    TREZOR_ACCOUNT ||--o{ TREZOR_DERIVED_ADDRESS : "issues"
    DERIVED_DESTINATION ||--o{ DERIVED_DESTINATION_SWEEP : "swept by"

    ID_MAP ||..|| USER : "bridges legacy id"
```

## Conventions Across All Models

### Drizzle/PostgreSQL Conventions

> [!note] Shared schema patterns
> - **Timestamps**: every table declares `created_at` and `updated_at timestamptz` with `withTimezone: true`.
> - **Primary keys**: all tables use `id uuid` (generated via `gen_random_uuid()` or application-side UUID v4). There are no integer sequences for domain tables.
> - **UUID references**: foreign keys reference the `id uuid` column of the target table (e.g. `user_id uuid REFERENCES users(id)`). The two exceptions are [[Notification]] and [[Payment]] which use `text`-typed identifiers in places to support template-flow payments.
> - **Soft delete**: deletion is modelled as a `status` flag (e.g. `users.status = 'deleted'`, `blog_posts.status = 'archived'`) rather than physical removal. `addresses` uses `deleted_at timestamptz` (nullable) with partial-unique indexes scoped to `WHERE deleted_at IS NULL`.
> - **TTL cleanup**: short-lived tables ([[TempVerification]], [[TelegramSession]]) rely on scheduled cleanup jobs rather than database-level TTL.
> - **JSON sanitisation**: [[User]] service layer strips credentials, refresh tokens, and verification codes before serialisation.

> [!warning] Index discipline
> Several tables carry both a `UNIQUE` constraint and would otherwise duplicate an index — check for existing unique constraints before adding explicit `CREATE INDEX` statements to avoid duplicate-index warnings at startup.

> [!note] PG schema patterns
> - **Legacy bridge**: `id_map` records the old ObjectId → UUID mapping for any reconciliation needs. The `legacy_object_id text` column with a partial-unique index `WHERE legacy_object_id IS NOT NULL` is retained on migrated tables for idempotent reconciliation upserts.
> - **Money columns**: `numeric(38,18)` for fiat/crypto amounts throughout, except `seller_offers` which uses `numeric(18,8)` per the Migration Guide. Blockchain balance columns use `numeric(78,0)` to hold uint256 without overflow.
> - **Polymorphic triples**: the `ref_kind` enum (`entity` | `template`) discriminator is expanded into three columns (`_ref_kind`, `_id`, `_external_ref`) with a CHECK constraint to enforce discriminator integrity. Used by `payments`, `funds_ledger_entries`, and `derived_destinations`.
> - **Immutability**: `funds_ledger_entries` has both an UPDATE-blocking and a DELETE-blocking trigger installed at the DB level (migrations 0004, 0015). A TRUNCATE trigger was added in migration 0013.
> - **user_role enum**: values are `admin`, `buyer`, `seller`, `resolver`, `guard`. The `guard` value was added in migration 0017.

## Postgres Migration Inventory

Schema entry point: `backend/src/db/schema/index.ts`

| Migration | File | Summary |
|---|---|---|
| 0000 | `0000_slimy_veda.sql` | Initial: core enums + `id_map` + `categories` |
| 0001 | `0001_wild_cargill.sql` | `trezor_accounts` + `trezor_derived_addresses` (later reset) |
| 0002 | `0002_motionless_grey_gargoyle.sql` | Schema reset: drops 0000/0001 tables to be rebuilt in 0003; adds `categories.parent_id` self-FK |
| 0003 | `0003_remarkable_retro_girl.sql` | Comprehensive rebuild: all enums + full core domain (`users`, `payments`, `funds_ledger_entries`, `derived_destinations`, `purchase_requests` + 6 children, `seller_offers`, `point_transactions`, `trezor_*`) |
| 0004a | `0004_funds_ledger_entries.sql` | UPDATE-blocking immutability trigger on `funds_ledger_entries` |
| 0004b | `0004_seller_offer.sql` | Physical FKs on `seller_offers` → `users` and `purchase_requests` (CASCADE) |
| 0005 | `0005_simple_champions.sql` | `pg_dualwrite_gaps`; FKs on `payments`; `legacy_object_id` unique indexes; refined pending-RN payment unique index |
| 0006 | `0006_normal_madame_hydra.sql` | CHECK: `purchase_requests.budget_currency` restricted to crypto (USDT, USDC) |
| 0007 | `0007_woozy_shaman.sql` | Drops 0006 constraint; sets `budget_currency` default to `'USDT'` |
| 0008 | `0008_giant_winter_soldier.sql` | Adds `'TRY'` to `offer_currency` enum; creates `payment_quotes` table |
| 0009 | `0009_unique_active_categories.sql` | Category deduplication; partial unique index on normalized active category name |
| 0010 | `0010_request_templates.sql` | Creates `request_templates`; deduplicates `purchase_request_specifications`; adds unique key constraint |
| 0011 | `0011_chats.sql` | Creates `chats` with JSONB participant/message storage + chat-related enums |
| 0012 | `0012_disputes.sql` | Creates `disputes` (text IDs, JSONB evidence/timeline/resolution) |
| 0013 | `0013_money_constraints.sql` | Money-integrity CHECKs on `payments`, `payment_quotes`, `point_transactions`, `users`; TRUNCATE trigger on `funds_ledger_entries`; composite PK + unique on `id_map` |
| 0014 | `0014_physical_fks.sql` | NOT VALID FKs across all major tables (validated immediately); composite indexes on `payments`, `purchase_requests`, `seller_offers` |
| 0015 | `0015_funds_ledger_immutable_trigger.sql` | Replaces/extends ledger triggers: UPDATE-block + new DELETE-block on `funds_ledger_entries` |
| 0016 | `0016_addresses_table.sql` | `address_type` enum + `addresses` table; partial-unique primary-address-per-user index |
| 0017 | `0017_user_role_guard.sql` | Adds `'guard'` to `user_role` enum (idempotent `ADD VALUE IF NOT EXISTS`) |

## Drizzle Table Inventory

### Infrastructure / Bridge

| PG Table | Schema File | Status | Notes |
|---|---|---|---|
| `id_map` | `idMap.ts` | PG-only | Legacy ObjectId → UUID bridge; composite PK + unique on `new_id` |
| `pg_dualwrite_gaps` | `pgDualwriteGaps.ts` | PG-only | Append-only reconciliation gap log from dual-write era |

### Core Domain

| PG Table | Schema File | Status | Notes |
|---|---|---|---|
| `users` | `users.ts` | Active | `DrizzleUserRepo` |
| `user_passkeys` | `users.ts` | Active (child of users) | — |
| `user_refresh_tokens` | `users.ts` | Active (child of users) | — |
| `categories` | `category.ts` | Active | `DrizzleMarketplaceRepo` |
| `purchase_requests` | `purchaseRequest.ts` | Active | `DrizzleMarketplaceRepo` |
| `purchase_request_delivery_info` | `purchaseRequest.ts` | Active (1:1 child) | — |
| `purchase_request_delivery_address` | `purchaseRequest.ts` | Active (1:1 child) | — |
| `purchase_request_seller_delivery_info` | `purchaseRequest.ts` | Active (1:1 child) | — |
| `delivery_attempts` | `purchaseRequest.ts` | Active (1:N child) | — |
| `purchase_request_service_info` | `purchaseRequest.ts` | Active (1:1 child) | — |
| `purchase_request_specifications` | `purchaseRequest.ts` | Active (1:N child) | — |
| `purchase_request_preferred_sellers` | `purchaseRequest.ts` | Active (N:M junction) | — |
| `seller_offers` | `sellerOffer.ts` | Active | `DrizzleMarketplaceRepo` |
| `payments` | `payment.ts` | Active | `DrizzlePaymentRepo` |
| `payment_quotes` | `paymentQuote.ts` | PG-only | No legacy equivalent; oracle depeg-protection feature |
| `funds_ledger_entries` | `fundsLedgerEntry.ts` | Active | `DrizzlePaymentRepo` |
| `derived_destinations` | `derivedDestination.ts` | Active | `DrizzleDerivedDestinationRepo` |
| `derived_destination_sweeps` | `derivedDestination.ts` | Active (append-only child) | — |
| `trezor_accounts` | `trezorAccount.ts` | Active | `DrizzleTrezorAccountRepo` |
| `trezor_derived_addresses` | `trezorAccount.ts` | Active (child of trezor_accounts) | — |
| `point_transactions` | `pointTransaction.ts` | Active | `DrizzlePointsRepo` |
| `request_templates` | `requestTemplate.ts` | Active | `DrizzleMarketplaceRepo` |
| `chats` | `chat.ts` | Active | `DrizzleChatRepo` |
| `blog_posts` | `blogPost.ts` | Active | `DrizzleBlogRepo` |
| `notifications` | `notification.ts` | Active | `DrizzleNotificationRepo` |
| `disputes` | `dispute.ts` | Active | `DrizzleDisputeRepo` |
| `addresses` | `address.ts` | Schema scaffolded | No write repo; `addressStore.ts` reads PG directly (migration 0016) |
| `shop_settings` | `shopSettings.ts` | Schema scaffolded | No write repo |
| `config_settings` | `configSetting.ts` | Schema scaffolded | No write repo |
| `config_setting_history` | `configSetting.ts` | PG-only | No legacy equivalent; child of `config_settings` |
| `telegram_links` | `telegramLink.ts` | Schema scaffolded | No write repo |
| `telegram_sessions` | `telegramSession.ts` | Schema scaffolded | No write repo |
| `reviews` | `review.ts` | Schema scaffolded | No write repo |

> [!note] Status key
> **Active** means reads and writes are served from Postgres. **Schema scaffolded** means the Drizzle table exists but no repo wires it into the service layer yet. **PG-only** means there is no legacy model for that data.

## Shared Enum Reference

Enums live in `backend/src/db/schema/_enums.ts` (shared) and individual schema files. Key enums:

| Enum | Values |
|---|---|
| `user_role` | admin, buyer, seller, resolver, guard |
| `auth_provider` | email, google, telegram |
| `user_status` | active, suspended, deleted |
| `purchase_request_status` | pending_payment, pending, received_offers, in_negotiation, payment_pending, payment_confirmed, in_progress, delivery, delivered, completed, disputed, refunded, seller_paid |
| `offer_status` | pending, accepted, rejected, withdrawn, active |
| `offer_currency` | USD, EUR, IRR, USDT, USDC, TRY |
| `payment_provider` | request.network, amn.scanner, shkeeper, other |
| `payment_status` | pending, processing, confirmed, completed, failed, cancelled, refunded |
| `escrow_state` | funded, releasable, released, refunded, releasing, failed, cancelled, partial |
| `funds_ledger_entry_type` | payment_detected, provider_fee, platform_fee, hold, release, refund, dispute_hold, adjustment |
| `derived_destination_status` | active, swept, sweeping, quarantined |
| `ref_kind` | entity, template |
| `chat_type` | direct, group, support |
| `review_subject_kind` | seller, template |
| `address_type` | Home, Office, Other |
| `telegram_link_source` | miniapp, bot, login_widget |
| `telegram_link_status` | active, blocked |

## Lifecycle View

The dominant happy-path flow exercises five tables in order:

1. A buyer (`users`) creates a `purchase_requests` row with `status: 'pending'`.
2. Sellers (other `users` rows) attach `seller_offers` rows; the request transitions through `received_offers` → `in_negotiation` as the parties chat in a `chats` row.
3. The buyer accepts an offer; a `payments` row is opened against the Request Network provider and, once verified by webhook/reconciliation and safety checks, advances to a funded escrow state. If `ORACLE_QUOTING_ENABLED=true`, a `payment_quotes` row is written to PG at this point.
4. The seller marks the request `delivery` → `delivered`; the buyer confirms with the 6-digit `delivery_code` and the request becomes `completed`.
5. The escrow `payments` row flips to `released` after a ledger-gated custody transfer instruction. Each ledger event appends an immutable `funds_ledger_entries` row. Optionally the buyer writes a `reviews` row and earns a `point_transactions` row.

If anything goes sideways, the buyer can open a `disputes` row, which freezes release until an admin resolves it (refund, replacement, compensation, or no-action).

## How to Navigate

Each model has its own note in this folder. Cross-references use `[[wikilinks]]` so backlinks work in Obsidian's graph view. Schemas are documented at field-level granularity — every field is listed with its type, default, validation, and indexing decisions. Where a model carries a meaningful state machine, a Mermaid `stateDiagram-v2` accompanies the schema table.

> [!note] Source of truth
> The information below is mirrored from the TypeScript schema definitions. If a field listed here disagrees with the code, the code wins — please update the note. All source citations use the form `backend/src/db/schema/<File>.ts:<line>` for Drizzle/PG.
>
> Last updated: v2.9.12 / 2026-06-06