Data model

Tables by area#

Ids are text: random UUIDs for app rows, Better Auth's own ids for its four tables. Columns are snake_case in Postgres and camelCase in TypeScript (Drizzle's casing: "snake_case"). Status columns are text with a TypeScript union type, not Postgres enums, so adding a value needs no migration.

How they connect

user ─┬─ shop ─── listing ─┬─ listing_photo ── file
      │                     ├─ listing_copy, research_run, listing_message
      │                     ├─ offer ──→ orders ─┬─ review
      │                     │   (buyer = user)   ├─ dispute ── dispute_event
      │                     │                    └─ checkout (PayPal round trip)
      │                     └─ thread ── message (buyer = user)
      ├─ paypal_account, api_key, webhook_endpoint, price_check
      └─ follow (→ shop), favourite (→ listing), activity (→ shop, listing)

Deleting a user or shop cascades to most of what hangs off it. Orders are the exception: orders references its listing, shop and buyer with restrict, so a sale can't silently disappear.

Statuses#

listing.status

draft → live → sold. step records the furthest workspace step reached (research, details, photos, words, publish), and visibility is everyone or link. slug is set on publish and unique within the shop. Sold listings stay public and read-only.

offer.status

Each new offer, counter or acceptance gets 48 hours (OFFER_HOURS in commerce.ts). The offer sweep marks lapsed ones expired.

orders.status

paid → shipped → completed, or cancelled / refunded. An order completes when the buyer confirms it arrived, or when the order sweep releases it 13 days after shipping. delivered is part of the type (and the API's filter) but nothing sets it yet, since there's no carrier tracking; delivered_at is filled in when the order completes. The money is held until then; released_at is set when PayPal pays the seller. payment_provider is paypal, or mock for test checkouts. A partial unique index allows only one order per listing that isn't cancelled or refunded, so two buyers can't both win a race.

dispute.status

open → escalated (someone asked resell.store to step in) → refunded or closed. source is buyer or paypal (opened in PayPal, synced by the webhook). At most one open or escalated dispute per order, and while there is one the money stays held.

The rest

• research_run.status and price_check.status: running, done, failed.
• checkout.status: created, completed, failed.
• webhook_delivery.status: pending, delivered, failed.
• notice has no status: its primary key is (kind, ref_id), and inserting a row is how a sweep claims a reminder.

Money#

Every amount is an integer number of US cents in a column ending _cents: listing.price_cents, lowest_cents and shipping_cents; offer.amount_cents, counter_cents and deposit_cents; and on orders, item_cents, shipping_cents, total_cents, platform_fee_cents, paypal_fee_cents, seller_net_cents and refunded_cents. The app converts at the edges with lib/money.ts; the public API speaks dollars.

Search columns#

• listing.search is a generated tsvector: title and name weighted A, one-liner and category B, description C. GIN index.
• listing.embedding is vector(1024), a Jina embedding of the public words written on publish. It has an HNSW index with vector_cosine_ops. Migration 0000 turns pgvector on.

Search runs both and merges them with reciprocal rank fusion (k = 60), ignoring vector matches under 0.3 similarity. Rows without an embedding are simply found by text.

Changing the schema#

1. Edit packages/db/src/schema.ts.
2. Run pnpm db:generate. drizzle-kit writes the next SQL file to packages/db/drizzle; give it a readable name and check the SQL.
3. Run pnpm db:migrate locally. On Render it runs on its own before each deploy (preDeployCommand), and the tests migrate resell_test on every run.

drizzle-kit reads DATABASE_URL from apps/app/.env.local, or from the environment. pnpm db:studio opens a browser view of the data.

Seed data#

pnpm --filter app seed fills the database from apps/app/seed/stores.ts: ten stores, four buyers, about 70 listings with photos and embeddings, a few sales with reviews, questions in messages, follows, likes and a month of views. Everything it creates belongs to users whose id starts with seed_, so it only ever replaces its own rows. It refuses a database that isn't on your machine unless you pass --remote. See Run it locally and Deploy to Render.