TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import { pgTable, text, integer, boolean, real, index, uniqueIndex, jsonb, date, primaryKey } from 'drizzle-orm/pg-core';2import { createdAt, updatedAt, ts, jsonObject, textArray, ratio, money } from './_common.js';34/** A data source (marketplace, auction house, price guide…) — §107, §143. */5export const sources = pgTable('sources', {6 id: text('id').primaryKey(), // slug-like, e.g. "scryfall", "psa"7 name: text('name').notNull(),8 sourceType: text('source_type').notNull(),9 url: text('url'),10 countries: textArray('countries'),11 languages: textArray('languages'),12 currencies: textArray('currencies'),13 /** 0–1 (§143) */14 trustScore: ratio('trust_score').notNull().default(0.5),15 trustFactors: jsonObject<Record<string, number>>('trust_factors'),16 attributionRequired: boolean('attribution_required').notNull().default(true),17 termsUrl: text('terms_url'),18 active: boolean('active').notNull().default(true),19 createdAt: createdAt(),20 updatedAt: updatedAt(),21});2223/** Connector registry mirror (connectors/registry.json is the source of truth for metadata; DB holds state). */24export const connectors = pgTable('connectors', {25 id: text('id').primaryKey(),26 sourceId: text('source_id').notNull(),27 displayName: text('display_name').notNull(),28 enginePriority: textArray('engine_priority'),29 categories: textArray('categories'),30 regions: textArray('regions'),31 languages: textArray('languages'),32 currency: textArray('currency'),33 supportsListings: boolean('supports_listings').notNull().default(false),34 supportsSold: boolean('supports_sold').notNull().default(false),35 supportsAuctions: boolean('supports_auctions').notNull().default(false),36 supportsImages: boolean('supports_images').notNull().default(true),37 supportsCatalog: boolean('supports_catalog').notNull().default(false),38 supportsPopulation: boolean('supports_population').notNull().default(false),39 refreshFrequencyMinutes: integer('refresh_frequency_minutes').notNull().default(1440),40 priority: text('priority').notNull().default('medium'), // high | medium | low (§141)41 status: text('status').notNull().default('active'), // active | paused | disabled42 schemaVersion: text('schema_version').notNull().default('1.0'),43 connectorVersion: text('connector_version').notNull().default('1.0.0'),44 config: jsonObject<Record<string, unknown>>('config'),45 /** full registry entry (capabilities, domain, country, acquisitionMethod, requires, refreshClass…) mirrored by the seed */46 meta: jsonObject<Record<string, unknown>>('meta'),47 lastRunAt: ts('last_run_at'),48 lastSuccessAt: ts('last_success_at'),49 nextRunAt: ts('next_run_at'),50 createdAt: createdAt(),51 updatedAt: updatedAt(),52});5354/**55 * Resumable historical backfills (SPEC §9). One row per backfill campaign; the crawler resumes from56 * last_cursor after any interruption instead of restarting from zero. `percent` is only set when the57 * connector knows the total (pages or date range).58 */59export const connectorBackfills = pgTable(60 'connector_backfills',61 {62 id: text('id').primaryKey(),63 connectorId: text('connector_id').notNull(),64 status: text('status').notNull().default('running'), // running | paused | completed | failed65 startedAt: ts('started_at').notNull(),66 updatedAt: updatedAt(),67 finishedAt: ts('finished_at'),68 /** date range the campaign targets (source-side dates) */69 backfillStartDate: date('backfill_start_date'),70 backfillEndDate: date('backfill_end_date'),71 /** oldest source date reached so far */72 reachedDate: date('reached_date'),73 pagesProcessed: integer('pages_processed').notNull().default(0),74 totalPages: integer('total_pages'),75 itemsProcessed: integer('items_processed').notNull().default(0),76 lastCursor: jsonObject<Record<string, unknown>>('last_cursor'),77 lastSuccessfulPage: integer('last_successful_page'),78 runs: integer('runs').notNull().default(0),79 errors: integer('errors').notNull().default(0),80 retryCount: integer('retry_count').notNull().default(0),81 lastError: text('last_error'),82 percent: real('percent'),83 },84 (t) => [index('connector_backfills_connector_idx').on(t.connectorId, t.status)],85);8687/** Daily field-presence counters per connector for schema-drift detection (SPEC §13). */88export const connectorFieldStats = pgTable(89 'connector_field_stats',90 {91 connectorId: text('connector_id').notNull(),92 day: date('day').notNull(),93 field: text('field').notNull(),94 total: integer('total').notNull().default(0),95 nulls: integer('nulls').notNull().default(0),96 },97 (t) => [primaryKey({ columns: [t.connectorId, t.day, t.field] })],98);99100/**101 * Certification numbers seen across sources (SPEC §22): one row per (grader, cert); every sale or102 * listing that carries the cert becomes a sighting → provenance graph over time.103 */104export const certificates = pgTable(105 'certificates',106 {107 id: text('id').primaryKey(),108 grader: text('grader').notNull(),109 certNumber: text('cert_number').notNull(),110 assetId: text('asset_id'),111 variantId: text('variant_id'),112 grade: text('grade'),113 qualifier: text('qualifier'),114 verifyUrl: text('verify_url'),115 firstSeenAt: ts('first_seen_at').notNull(),116 lastSeenAt: ts('last_seen_at').notNull(),117 sightings: integer('sightings').notNull().default(0),118 sourceIds: textArray('source_ids'),119 lastSourceUrl: text('last_source_url'),120 lastPriceUsd: money('last_price_usd'),121 /** verified against the grader's public lookup (cert_lookup connectors) */122 verifiedAt: ts('verified_at'),123 verification: jsonObject<Record<string, unknown>>('verification'),124 createdAt: createdAt(),125 },126 (t) => [uniqueIndex('certificates_grader_cert_uq').on(t.grader, t.certNumber), index('certificates_asset_idx').on(t.assetId), index('certificates_last_seen_idx').on(t.lastSeenAt)],127);128129export const certificateSightings = pgTable(130 'certificate_sightings',131 {132 id: text('id').primaryKey(),133 certificateId: text('certificate_id').notNull(),134 kind: text('kind').notNull(), // sale | listing | auction_lot | lookup135 targetId: text('target_id'),136 sourceId: text('source_id').notNull(),137 connectorId: text('connector_id'),138 sourceUrl: text('source_url'),139 priceUsd: money('price_usd'),140 currency: text('currency'),141 price: money('price'),142 observedAt: ts('observed_at').notNull(),143 createdAt: createdAt(),144 },145 (t) => [index('certificate_sightings_cert_idx').on(t.certificateId, t.observedAt), uniqueIndex('certificate_sightings_uq').on(t.certificateId, t.kind, t.targetId)],146);147148export const connectorRuns = pgTable(149 'connector_runs',150 {151 id: text('id').primaryKey(),152 connectorId: text('connector_id').notNull(),153 trigger: text('trigger').notNull().default('schedule'), // schedule | manual | backfill | repair154 startedAt: ts('started_at').notNull(),155 finishedAt: ts('finished_at'),156 status: text('status').notNull().default('running'), // running | success | partial | failed157 pagesAttempted: integer('pages_attempted').notNull().default(0),158 pagesSuccess: integer('pages_success').notNull().default(0),159 recordsRaw: integer('records_raw').notNull().default(0),160 recordsNormalized: integer('records_normalized').notNull().default(0),161 recordsDuplicate: integer('records_duplicate').notNull().default(0),162 recordsRejected: integer('records_rejected').notNull().default(0),163 /** per engine: {firecrawl:{attempts,success,credits}, scrapfly:{…}} */164 engineStats: jsonObject<Record<string, { attempts: number; success: number; credits: number; ms: number }>>('engine_stats'),165 anomalies: jsonb('anomalies').$type<string[]>().notNull().default([]),166 error: text('error'),167 costCredits: real('cost_credits').notNull().default(0),168 costUsdEst: real('cost_usd_est').notNull().default(0),169 cursor: jsonObject<Record<string, unknown>>('cursor'),170 },171 (t) => [index('connector_runs_connector_started_idx').on(t.connectorId, t.startedAt)],172);173174/** Latest computed health snapshot per connector (§105). */175export const connectorHealth = pgTable('connector_health', {176 connectorId: text('connector_id').primaryKey(),177 computedAt: ts('computed_at').notNull(),178 status: text('status').notNull(),179 health: jsonObject<Record<string, unknown>>('health'),180});181182/** Per-URL crawl budget state (§170). */183export const crawlState = pgTable(184 'crawl_state',185 {186 urlHash: text('url_hash').primaryKey(),187 connectorId: text('connector_id').notNull(),188 url: text('url').notNull(),189 etag: text('etag'),190 lastModified: text('last_modified'),191 contentHash: text('content_hash'),192 lastFetchedAt: ts('last_fetched_at'),193 lastChangedAt: ts('last_changed_at'),194 fetchCount: integer('fetch_count').notNull().default(0),195 changeCount: integer('change_count').notNull().default(0),196 /** estimated hours between changes */197 changeIntervalHours: real('change_interval_hours'),198 nextFetchAt: ts('next_fetch_at'),199 lastStatus: integer('last_status'),200 failures: integer('failures').notNull().default(0),201 },202 (t) => [index('crawl_state_next_idx').on(t.connectorId, t.nextFetchAt)],203);204205/** Cost ledger for crawlers and AI (§169). */206export const costs = pgTable(207 'costs',208 {209 id: text('id').primaryKey(),210 occurredAt: ts('occurred_at').notNull(),211 kind: text('kind').notNull(), // firecrawl | scrapfly | ai | storage212 provider: text('provider'),213 connectorId: text('connector_id'),214 categorySlug: text('category_slug'),215 endpoint: text('endpoint'),216 userId: text('user_id'),217 units: real('units').notNull().default(1),218 credits: real('credits').notNull().default(0),219 usdEst: real('usd_est').notNull().default(0),220 metadata: jsonObject<Record<string, unknown>>('metadata'),221 },222 (t) => [index('costs_occurred_idx').on(t.occurredAt), index('costs_connector_idx').on(t.connectorId)],223);224