spb/rareindex

1import { pgTable, text, integer, boolean, index, uniqueIndex } from 'drizzle-orm/pg-core';2import { createdAt, updatedAt, jsonObject, textArray } from './_common.js';34/**5 * Extensible taxonomy (CLAUDE.md §4, §101). Rows are seeded from data/taxonomy/*.json and can be6 * extended at runtime through approved taxonomy_proposals. Never hardcode categories in app code.7 */8export const categories = pgTable(9  'categories',10  {11    slug: text('slug').primaryKey(),12    parentSlug: text('parent_slug'),13    /** top-level family slug (e.g. trading_cards) for fast grouping */14    familySlug: text('family_slug').notNull(),15    name: text('name').notNull(),16    shortName: text('short_name'),17    description: text('description'),18    level: integer('level').notNull().default(0),19    /** 1 = launch wedge, 2, 3 = later phases (§180–182) */20    phase: integer('phase').notNull().default(3),21    active: boolean('active').notNull().default(true),22    /** slug of the condition scale in data/taxonomy/conditions.json */23    conditionScale: text('condition_scale'),24    /** grader slugs relevant to this category */25    graders: textArray('graders'),26    /** subindex ticker this category rolls into (e.g. RARE-TCG) */27    indexTicker: text('index_ticker'),28    /** JSON schema fragment describing category-specific attributes */29    attributeSchema: jsonObject<Record<string, unknown>>('attribute_schema'),30    sortOrder: integer('sort_order').notNull().default(0),31    /** compliance flags: e.g. ["jurisdiction_check", "neutral_presentation"] */32    complianceFlags: textArray('compliance_flags'),33    icon: text('icon'),34    createdAt: createdAt(),35    updatedAt: updatedAt(),36  },37  (t) => [index('categories_parent_idx').on(t.parentSlug), index('categories_family_idx').on(t.familySlug)],38);3940export const brands = pgTable(41  'brands',42  {43    slug: text('slug').primaryKey(),44    name: text('name').notNull(),45    categorySlugs: textArray('category_slugs'),46    country: text('country'),47    foundedYear: integer('founded_year'),48    metadata: jsonObject<Record<string, unknown>>('metadata'),49    createdAt: createdAt(),50  },51  (t) => [index('brands_name_idx').on(t.name)],52);5354export const franchises = pgTable('franchises', {55  slug: text('slug').primaryKey(),56  name: text('name').notNull(),57  owner: text('owner'),58  categorySlugs: textArray('category_slugs'),59  metadata: jsonObject<Record<string, unknown>>('metadata'),60  createdAt: createdAt(),61});6263/** Sets / series / releases (Base Set, Alpha, Series 1 Prizm, LEGO theme year…). */64export const sets = pgTable(65  'sets',66  {67    slug: text('slug').primaryKey(),68    name: text('name').notNull(),69    code: text('code'),70    categorySlug: text('category_slug').notNull(),71    franchiseSlug: text('franchise_slug'),72    brandSlug: text('brand_slug'),73    releaseYear: integer('release_year'),74    releaseDate: text('release_date'),75    language: text('language'),76    totalItems: integer('total_items'),77    identifiers: jsonObject<Record<string, string>>('identifiers'),78    metadata: jsonObject<Record<string, unknown>>('metadata'),79    createdAt: createdAt(),80  },81  (t) => [index('sets_category_idx').on(t.categorySlug), uniqueIndex('sets_category_code_uq').on(t.categorySlug, t.code)],82);8384export const graders = pgTable('graders', {85  slug: text('slug').primaryKey(), // psa, bgs, cgc, sgc, tag, wata, vga, pcgs, ngc, cbcs, anacs, iccs86  name: text('name').notNull(),87  categorySlugs: textArray('category_slugs'),88  /** ordered scale values e.g. ["1","1.5",…,"10"] */89  scale: jsonObject<{ values: string[]; top: string; type: 'numeric' | 'sheldon' | 'descriptive' }>('scale'),90  populationUrl: text('population_url'),91  verifyUrl: text('verify_url'),92  active: boolean('active').notNull().default(true),93});9495export const taxonomyProposals = pgTable('taxonomy_proposals', {96  id: text('id').primaryKey(),97  proposedSlug: text('proposed_slug').notNull(),98  name: text('name').notNull(),99  parentSlug: text('parent_slug'),100  evidence: jsonObject<Record<string, unknown>>('evidence'),101  volumeEstimate: integer('volume_estimate'),102  status: text('status').notNull().default('pending'), // pending | approved | rejected103  decidedBy: text('decided_by'),104  decidedAt: text('decided_at'),105  createdAt: createdAt(),106});107