1import { pgTable, text, integer, boolean, real, index, jsonb } from 'drizzle-orm/pg-core';2import { createdAt, updatedAt, ts, jsonObject, textArray, ratio } 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 lastRunAt: ts('last_run_at'),46 lastSuccessAt: ts('last_success_at'),47 nextRunAt: ts('next_run_at'),48 createdAt: createdAt(),49 updatedAt: updatedAt(),50});5152export const connectorRuns = pgTable(53 'connector_runs',54 {55 id: text('id').primaryKey(),56 connectorId: text('connector_id').notNull(),57 trigger: text('trigger').notNull().default('schedule'), // schedule | manual | backfill | repair58 startedAt: ts('started_at').notNull(),59 finishedAt: ts('finished_at'),60 status: text('status').notNull().default('running'), // running | success | partial | failed61 pagesAttempted: integer('pages_attempted').notNull().default(0),62 pagesSuccess: integer('pages_success').notNull().default(0),63 recordsRaw: integer('records_raw').notNull().default(0),64 recordsNormalized: integer('records_normalized').notNull().default(0),65 recordsDuplicate: integer('records_duplicate').notNull().default(0),66 recordsRejected: integer('records_rejected').notNull().default(0),67 /** per engine: {firecrawl:{attempts,success,credits}, scrapfly:{…}} */68 engineStats: jsonObject<Record<string, { attempts: number; success: number; credits: number; ms: number }>>('engine_stats'),69 anomalies: jsonb('anomalies').$type<string[]>().notNull().default([]),70 error: text('error'),71 costCredits: real('cost_credits').notNull().default(0),72 costUsdEst: real('cost_usd_est').notNull().default(0),73 cursor: jsonObject<Record<string, unknown>>('cursor'),74 },75 (t) => [index('connector_runs_connector_started_idx').on(t.connectorId, t.startedAt)],76);7778/** Latest computed health snapshot per connector (§105). */79export const connectorHealth = pgTable('connector_health', {80 connectorId: text('connector_id').primaryKey(),81 computedAt: ts('computed_at').notNull(),82 status: text('status').notNull(),83 health: jsonObject<Record<string, unknown>>('health'),84});8586/** Per-URL crawl budget state (§170). */87export const crawlState = pgTable(88 'crawl_state',89 {90 urlHash: text('url_hash').primaryKey(),91 connectorId: text('connector_id').notNull(),92 url: text('url').notNull(),93 etag: text('etag'),94 lastModified: text('last_modified'),95 contentHash: text('content_hash'),96 lastFetchedAt: ts('last_fetched_at'),97 lastChangedAt: ts('last_changed_at'),98 fetchCount: integer('fetch_count').notNull().default(0),99 changeCount: integer('change_count').notNull().default(0),100 /** estimated hours between changes */101 changeIntervalHours: real('change_interval_hours'),102 nextFetchAt: ts('next_fetch_at'),103 lastStatus: integer('last_status'),104 failures: integer('failures').notNull().default(0),105 },106 (t) => [index('crawl_state_next_idx').on(t.connectorId, t.nextFetchAt)],107);108109/** Cost ledger for crawlers and AI (§169). */110export const costs = pgTable(111 'costs',112 {113 id: text('id').primaryKey(),114 occurredAt: ts('occurred_at').notNull(),115 kind: text('kind').notNull(), // firecrawl | scrapfly | ai | storage116 provider: text('provider'),117 connectorId: text('connector_id'),118 categorySlug: text('category_slug'),119 endpoint: text('endpoint'),120 userId: text('user_id'),121 units: real('units').notNull().default(1),122 credits: real('credits').notNull().default(0),123 usdEst: real('usd_est').notNull().default(0),124 metadata: jsonObject<Record<string, unknown>>('metadata'),125 },126 (t) => [index('costs_occurred_idx').on(t.occurredAt), index('costs_connector_idx').on(t.connectorId)],127);128