rstillwell/deckhearth
1
0
Fork 0
Code Issues 1 Pull requests Projects Releases Packages Wiki Activity Actions
deckhearth/docs/SCHEMA_MAP.md
Randall Stillwell ec9bb2b93e feat(catalog): unified multi-game bulk sync + schema map update 
Ship Pokemon and Lorcana bulk import libs/scripts, unified weekly cron
sync across MTG/Pokemon/Lorcana with per-game error isolation and
catalog_sync_log telemetry. Admin UI adds Unified/Incremental/Bulk MTG modes.

- Rename reconcile migrations to 1781442330* timestamps so they apply
  after bulk-data migrations without node-pg-migrate ordering conflicts
- Add Lorcana set-code normalization + orphan cleanup migrations
- Drop stricter user_cards_user_card_unique (keep 3-column foil unique)
- Update docs/SCHEMA_MAP.md for tags, card_tags, catalog_sync_log, bulk columns

Co-authored-by: Cursor <cursoragent@cursor.com>
2026-06-14 08:22:19 -05:00

308 lines
14 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

# SCHEMA_MAP.md
> Hand-curated reference for the Neon Postgres schema. As of the
> `migration-tool` convoy (2026-05-26), the formal source of truth is
> the migration history under `migrations/` at the repo root (managed by
> `node-pg-migrate`). The initial backfill migration
> `migrations/1779853647564_initial-schema.js` reproduces
> `scripts/setup-neon-db.js`'s 7-table bootstrap DDL verbatim. The
> historical `scripts/add-*.js` / `scripts/fix-*.js` jobs are preserved
> as append-only history; their effects are baked into the production
> schema but are NOT replayed by `npm run migrate up` on a fresh env —
> the initial backfill captures only the post-`setup-neon-db.js`
> shape. If a fresh env needs the full historical column set, a
> follow-up convoy (`reconcile-historical-add-scripts`) will fold the
> historical effects into the migration history; until then this file
> remains the curated reference for the full prod shape.
>
> **Last reviewed:** 2026-06-14 against `migrations/` HEAD (through
> `1781442340000_cleanup-lorcana-orphans.js`).
## Quick model groups
| Group | Tables | Purpose |
| --- | --- | --- |
| **Identity** | `users`, `user_settings`, `user_avatars` | Accounts, profile, preferences |
| **Catalog** | `cards`, `tags`, `card_tags` | Master card list + Scryfall Tagger community tags |
| **Sync telemetry** | `catalog_sync_log` | Per-game bulk/incremental catalog sync history |
| **Ownership** | `user_cards`, `user_favorites` | What a user owns / has favorited (UI: **My Collection**) |
| **Collections** | `collections`, `collection_cards`, `collection_permissions`, `collection_activity` | Curated card lists with sharing (UI: **Lists**) |
| **Decks** | `decks`, `deck_cards` | Playable deck definitions |
| **Scanning** | `card_submissions`, `scan_attempts` | Unknown-card review queue + scan telemetry |
| **Invitations** | `invitations` (referenced; verify) | Pending share requests |
## Tables
### users
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `email` | `VARCHAR(255) UNIQUE NOT NULL` | Lowercase before query/insert (no CI collation set) |
| `password` | `VARCHAR(255) NOT NULL` | bcrypt hash, cost 12 |
| `role` | `VARCHAR(50)` default `'user'` | `'user' \| 'admin'` |
| `created_at`, `updated_at` | `TIMESTAMP` default now | |
| `first_name`, `last_name` | `VARCHAR(255)` | From `add-user-profile-columns.js` |
| `username` | `VARCHAR(255) UNIQUE` | |
| `profile_image_url`, `avatar_url` | `TEXT` | Two redundant columns; verify which is canonical |
| `bio` | `TEXT` | |
| `favorite_games` | `JSONB` default `'["MTG"]'` | Per-user game preference array |
| `collection_visibility` | `VARCHAR(20)` default `'private'` | |
| `preferred_currency` | `VARCHAR(3)` default `'USD'` | |
| `cards_per_page` | `INTEGER` default `50` | |
| `default_view` | `VARCHAR(10)` default `'grid'` | |
| `notifications_email` | `BOOLEAN` default `true` | |
| `notifications_marketing` | `BOOLEAN` default `false` | |
| `two_factor_enabled` | `BOOLEAN` default `false` | Not implemented yet |
| `theme` | `VARCHAR(10)` default `'system'` | `'light' \| 'dark' \| 'system'` |
| `language` | `VARCHAR(5)` default `'en'` | |
| `is_pending` | `BOOLEAN` default `false` | Set by invitation flow before signup completes |
### cards
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `name` | `VARCHAR(255) NOT NULL` | |
| `set_name`, `set_code`, `card_number` | `VARCHAR` | |
| `rarity` | `VARCHAR(50)` | |
| `game` | `VARCHAR(50) NOT NULL` | `'mtg' \| 'pokemon' \| 'lorcana'` |
| `mana_cost` | `VARCHAR(50)` | MTG only |
| `cmc` | `INTEGER` | MTG only |
| `card_type` | `VARCHAR(255)` | |
| `colors` | `JSONB` | MTG color array |
| `oracle_text` | `TEXT` | LARGE — never `SELECT *` |
| `power`, `toughness` | `VARCHAR(10)` | MTG creatures |
| `image_url`, `stock_image_url` | `TEXT` | |
| `current_price`, `market_price` | `DECIMAL(10,2)` | |
| `scryfall_id` | `VARCHAR(255) UNIQUE` | External dedupe key (Scryfall UUID, Pokémon TCG id, Lorcana `Unique_ID`) |
| `verified` | `BOOLEAN` default `false` | Admin-edited cards |
| `quantity` | `INTEGER` default `0` | **Unused; consider dropping — quantity lives in `user_cards`** (`1781442330001_reconcile-cards-columns`) |
| `favorited` | `BOOLEAN` default `false` | **Unused; favorites live in `user_favorites`** (`1781442330001_reconcile-cards-columns`) |
| `oracle_id` | `VARCHAR(36)` | Stable across MTG printings; joins oracle tags (`1781440700404`) |
| `illustration_id` | `VARCHAR(36)` | Stable per artwork; joins art tags (`1781440700404`) |
| `color_identity` | `JSONB` | MTG Commander identity; Lorcana ink colors (`1781440700404`) |
| `keywords` | `JSONB` | MTG mechanics / Lorcana classifications (`1781440700404`) |
| `legalities` | `JSONB` | Format legality map (`1781440700404`) |
| `flavor_text` | `TEXT` | (`1781440700404`) |
| `artist` | `VARCHAR(255)` | (`1781440700404`) |
| `released_at` | `DATE` | (`1781440700404`) |
| `layout` | `VARCHAR(50)` | MTG card layout (`1781440700404`) |
| `edhrec_rank` | `INTEGER` | MTG Commander popularity; Lorcana lore value repurposed (`1781440700404`) |
| `reserved` | `BOOLEAN` default `false` | MTG Reserved List (`1781440700404`) |
| `reprint` | `BOOLEAN` default `false` | (`1781440700404`) |
| `finishes` | `JSONB` | `["nonfoil","foil","etched"]` (`1781440700404`) |
| `created_at`, `updated_at` | `TIMESTAMP` default now | |
**Indexes (post `1779853647565_add-pg-trgm-card-name-index` + `1781440700404`):**
- `idx_cards_name_trgm` — GIN on `name` using `gin_trgm_ops` for Layer-1 OCR fuzzy match (`similarity()` / `pg_trgm`).
- `cards_oracle_id_index`, `cards_illustration_id_index`, `cards_artist_index`, `cards_edhrec_rank_index`
- GIN on `color_identity`, `keywords`, `legalities`
**Extensions used by scan pipeline:**
- `pg_trgm` — enabled by `1779853647565_add-pg-trgm-card-name-index.js` for trigram similarity on `cards.name`.
### user_cards
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `user_id` | `INTEGER FK users(id) ON DELETE CASCADE` | |
| `card_id` | `INTEGER FK cards(id) ON DELETE CASCADE` | |
| `quantity` | `INTEGER` default `1` | |
| `condition` | `VARCHAR(50)` default `'NM'` | NM / LP / MP / HP / DMG |
| `is_foil` | `BOOLEAN` default `false` | |
| `notes` | `TEXT` | |
| `scan_image_url` | `TEXT` | Vercel Blob URL of scanner capture (`1779908094455_add-user-cards-scan-image-url`) |
| | | **UNIQUE(user_id, card_id, is_foil)** — canonical 3-column constraint per `1781442330006_reconcile-user-cards-unique` (foil and non-foil are separate rows). The historical 2-column `user_cards_user_card_unique` constraint is dropped if present. |
### tags
Scryfall Tagger community tags (`1781440721350_add-tagger-tables`).
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `UUID PK` | Stable tag id from Scryfall bulk |
| `slug` | `VARCHAR(255) NOT NULL` | URL-safe identifier |
| `label` | `VARCHAR(255) NOT NULL` | Human-readable name |
| `type` | `VARCHAR(20) NOT NULL` | `'oracle'` (functional) or `'illustration'` (art) |
| `description` | `TEXT` | Optional |
| `parent_ids`, `child_ids`, `aliases` | `JSONB` | Tag hierarchy |
| `created_at`, `updated_at` | `TIMESTAMP` | |
### card_tags
Joins tags to cards via `oracle_id` (oracle tags) or `illustration_id` (art tags).
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `tag_id` | `UUID FK tags(id) ON DELETE CASCADE` | |
| `card_id` | `INTEGER FK cards(id) ON DELETE CASCADE` | Optional resolved link |
| `oracle_id` | `VARCHAR(36)` | For oracle tags |
| `illustration_id` | `VARCHAR(36)` | For art tags |
| `weight` | `VARCHAR(20)` default `'median'` | `very_strong` / `strong` / `median` / `weak` |
| `annotation` | `TEXT` | Optional per-tagging note |
| `created_at` | `TIMESTAMP` | |
| | | **UNIQUE(tag_id, oracle_id)** where `oracle_id IS NOT NULL` |
| | | **UNIQUE(tag_id, illustration_id)** where `illustration_id IS NOT NULL` |
### catalog_sync_log
Per-game catalog sync telemetry (`1781442329511_add-catalog-sync-log`).
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `game` | `VARCHAR(20) NOT NULL` | `'mtg'`, `'pokemon'`, `'lorcana'` |
| `mode` | `VARCHAR(20) NOT NULL` | `'unified'`, `'bulk'`, `'incremental'` |
| `upserted` | `INTEGER` default `0` | Cards written this run |
| `errors` | `INTEGER` default `0` | Failed rows |
| `source_updated_at` | `TIMESTAMPTZ` | Upstream bulk file timestamp when available |
| `ran_at` | `TIMESTAMPTZ` default now | |
| `details` | `JSONB` | Per-run summary payload |
### user_favorites
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `user_id`, `card_id` | FKs cascade | |
| `created_at` | `TIMESTAMP` | |
| | | **UNIQUE(user_id, card_id)** (verify constraint exists) |
### collections
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `user_id` | `INTEGER FK users(id) ON DELETE CASCADE` | |
| `name` | `VARCHAR(255) NOT NULL` | |
| `description` | `TEXT` | |
| `is_public` | `BOOLEAN` default `false` | Used by `withCollectionPermission('viewer')` |
| `slug` | `VARCHAR(100) UNIQUE` | Format constraint: lowercase kebab, ≤50 chars |
| `image` | `TEXT` | Cover image |
| `visibility` | `VARCHAR(20)` default `'private'` | **Coexists with `is_public`; verify single source of truth** |
| `tcg` | `VARCHAR(50)` default `'MTG'` | |
| `tags` | `TEXT` | Comma-separated; consider migrating to JSONB array |
| `is_system_collection` | `BOOLEAN` default `false` | Auto-sync list; DB name `'All My Cards'` (internal — UI: **Synced binder** via `collectionDisplayName()`) |
| `created_at`, `updated_at` | `TIMESTAMP` | |
### collection_cards
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `collection_id`, `card_id` | FKs cascade | |
| `quantity` | `INTEGER` default `1` | |
| | | **UNIQUE(collection_id, card_id)** |
### collection_permissions
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `collection_id`, `user_id` | FKs cascade | |
| `role` | `VARCHAR` | `'viewer' \| 'editor' \| 'owner'` |
| `status` | `VARCHAR` | `'pending' \| 'active' \| 'declined'` — used by invite flow |
| `created_at` | `TIMESTAMP` | |
### collection_activity
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `collection_id`, `user_id` | FKs | |
| `action` | `VARCHAR` | e.g. `'card_added'`, `'permission_granted'` |
| `details` | `JSONB` | |
| `created_at` | `TIMESTAMP` | |
### decks
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `user_id` | FK | |
| `name`, `description` | text | |
| `game` | `VARCHAR(50)` | |
| `is_public` | `BOOLEAN` default `false` | |
| `created_at`, `updated_at` | `TIMESTAMP` | |
### deck_cards
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `deck_id`, `card_id` | FKs cascade | |
| `quantity` | `INTEGER` default `1` | |
| | | **UNIQUE(deck_id, card_id)** |
### card_submissions
Added by `migrations/1779853647566_add-scan-tables.js` (server-side scan pipeline). Unknown high-confidence scans queue here for admin review instead of polluting `cards`.
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `user_id` | FK → `users` cascade | Submitter |
| `ocr_text` | `TEXT` | Raw OCR / vision text |
| `ocr_confidence` | `INTEGER` | 0–100 from scan layer |
| `scan_image_url` | `TEXT` | Optional blob URL of capture |
| `candidate_card_ids` | `JSONB` default `'[]'` | Near-miss catalog IDs |
| `ocr_payload` | `JSONB` | Structured fields for admin promote |
| `status` | `VARCHAR(32)` default `'pending'` | `'pending' \| 'approved' \| 'rejected'` |
| `reviewed_by` | FK → `users` SET NULL | Admin reviewer |
| `promoted_card_id` | FK → `cards` SET NULL | Set on approve |
| `created_at`, `updated_at` | `TIMESTAMP` default now | |
Index: `idx_card_submissions_status (status, created_at DESC)`.
### scan_attempts
Per-scan telemetry for the identify pipeline (layer 2 = Gemini today).
| Column | Type | Notes |
| --- | --- | --- |
| `id` | `SERIAL PK` | |
| `user_id` | FK → `users` SET NULL | |
| `ocr_text` | `TEXT` | |
| `ocr_confidence` | `INTEGER` | |
| `layer` | `INTEGER` default `2` | OCR layer (1 = Tesseract future) |
| `matched_card_id` | FK → `cards` SET NULL | |
| `result_kind` | `VARCHAR(32)` | e.g. `'matched'`, `'disambiguation'`, `'submitted'`, `'not_a_card'` |
| `latency_ms` | `INTEGER` | End-to-end identify latency |
| `created_at` | `TIMESTAMP` default now | |
Index: `idx_scan_attempts_user_created (user_id, created_at DESC)`.
### user_settings (split from users.*; verify which is canonical)
Defined in `add-user-profile-fields.js`. Mirrors several `users.*` columns — there's redundancy that needs to be reconciled.
### user_avatars
Tracks uploaded avatar history. Older avatars are typically deleted from blob storage; verify the cleanup job runs.
## Known schema smells
1. **Two `users` avatar columns** — `profile_image_url` and `avatar_url`. Pick one.
2. **Two collection-visibility flags** — `collections.is_public` (BOOLEAN) and `collections.visibility` (VARCHAR). Pick one.
3. **`cards.quantity` + `cards.favorited`** — these belong on `user_cards` / `user_favorites`, not the catalog. Drop them.
4. **`user_settings` ↔ `users.*`** — split-brain. Reconcile.
5. **No formal constraints on enums** — `role`, `condition`, `theme`, `language`, `game`, `visibility` are all `VARCHAR`. Consider CHECK constraints or proper ENUMs.
6. **`collections.tags` is `TEXT`** — should be `JSONB` or a join table.
## Regeneration
For new schema changes (post-`migration-tool`), look at
`migrations/<timestamp>_<slug>.js` files and update the matching table
section here in the same PR. For the historical state captured before
the migration tool landed:
```bash
rg "ALTER TABLE|CREATE TABLE|ADD COLUMN" scripts/ migrations/
```
…then update this file by hand. A `npm run schema:map` script regenerated from the migration history is in `.convoys/`.
Reference in a new issue View git blame Copy permalink