TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1CREATE TABLE "brands" (2 "slug" text PRIMARY KEY NOT NULL,3 "name" text NOT NULL,4 "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL,5 "country" text,6 "founded_year" integer,7 "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,8 "created_at" timestamp with time zone DEFAULT now() NOT NULL9);10--> statement-breakpoint11CREATE TABLE "categories" (12 "slug" text PRIMARY KEY NOT NULL,13 "parent_slug" text,14 "family_slug" text NOT NULL,15 "name" text NOT NULL,16 "short_name" text,17 "description" text,18 "level" integer DEFAULT 0 NOT NULL,19 "phase" integer DEFAULT 3 NOT NULL,20 "active" boolean DEFAULT true NOT NULL,21 "condition_scale" text,22 "graders" text[] DEFAULT '{}'::text[] NOT NULL,23 "index_ticker" text,24 "attribute_schema" jsonb DEFAULT '{}'::jsonb NOT NULL,25 "sort_order" integer DEFAULT 0 NOT NULL,26 "compliance_flags" text[] DEFAULT '{}'::text[] NOT NULL,27 "icon" text,28 "created_at" timestamp with time zone DEFAULT now() NOT NULL,29 "updated_at" timestamp with time zone DEFAULT now() NOT NULL30);31--> statement-breakpoint32CREATE TABLE "franchises" (33 "slug" text PRIMARY KEY NOT NULL,34 "name" text NOT NULL,35 "owner" text,36 "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL,37 "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,38 "created_at" timestamp with time zone DEFAULT now() NOT NULL39);40--> statement-breakpoint41CREATE TABLE "graders" (42 "slug" text PRIMARY KEY NOT NULL,43 "name" text NOT NULL,44 "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL,45 "scale" jsonb DEFAULT '{}'::jsonb NOT NULL,46 "population_url" text,47 "verify_url" text,48 "active" boolean DEFAULT true NOT NULL49);50--> statement-breakpoint51CREATE TABLE "sets" (52 "slug" text PRIMARY KEY NOT NULL,53 "name" text NOT NULL,54 "code" text,55 "category_slug" text NOT NULL,56 "franchise_slug" text,57 "brand_slug" text,58 "release_year" integer,59 "release_date" text,60 "language" text,61 "total_items" integer,62 "identifiers" jsonb DEFAULT '{}'::jsonb NOT NULL,63 "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,64 "created_at" timestamp with time zone DEFAULT now() NOT NULL65);66--> statement-breakpoint67CREATE TABLE "taxonomy_proposals" (68 "id" text PRIMARY KEY NOT NULL,69 "proposed_slug" text NOT NULL,70 "name" text NOT NULL,71 "parent_slug" text,72 "evidence" jsonb DEFAULT '{}'::jsonb NOT NULL,73 "volume_estimate" integer,74 "status" text DEFAULT 'pending' NOT NULL,75 "decided_by" text,76 "decided_at" text,77 "created_at" timestamp with time zone DEFAULT now() NOT NULL78);79--> statement-breakpoint80CREATE TABLE "connector_health" (81 "connector_id" text PRIMARY KEY NOT NULL,82 "computed_at" timestamp with time zone NOT NULL,83 "status" text NOT NULL,84 "health" jsonb DEFAULT '{}'::jsonb NOT NULL85);86--> statement-breakpoint87CREATE TABLE "connector_runs" (88 "id" text PRIMARY KEY NOT NULL,89 "connector_id" text NOT NULL,90 "trigger" text DEFAULT 'schedule' NOT NULL,91 "started_at" timestamp with time zone NOT NULL,92 "finished_at" timestamp with time zone,93 "status" text DEFAULT 'running' NOT NULL,94 "pages_attempted" integer DEFAULT 0 NOT NULL,95 "pages_success" integer DEFAULT 0 NOT NULL,96 "records_raw" integer DEFAULT 0 NOT NULL,97 "records_normalized" integer DEFAULT 0 NOT NULL,98 "records_duplicate" integer DEFAULT 0 NOT NULL,99 "records_rejected" integer DEFAULT 0 NOT NULL,100 "engine_stats" jsonb DEFAULT '{}'::jsonb NOT NULL,101 "anomalies" jsonb DEFAULT '[]'::jsonb NOT NULL,102 "error" text,103 "cost_credits" real DEFAULT 0 NOT NULL,104 "cost_usd_est" real DEFAULT 0 NOT NULL,105 "cursor" jsonb DEFAULT '{}'::jsonb NOT NULL106);107--> statement-breakpoint108CREATE TABLE "connectors" (109 "id" text PRIMARY KEY NOT NULL,110 "source_id" text NOT NULL,111 "display_name" text NOT NULL,112 "engine_priority" text[] DEFAULT '{}'::text[] NOT NULL,113 "categories" text[] DEFAULT '{}'::text[] NOT NULL,114 "regions" text[] DEFAULT '{}'::text[] NOT NULL,115 "languages" text[] DEFAULT '{}'::text[] NOT NULL,116 "currency" text[] DEFAULT '{}'::text[] NOT NULL,117 "supports_listings" boolean DEFAULT false NOT NULL,118 "supports_sold" boolean DEFAULT false NOT NULL,119 "supports_auctions" boolean DEFAULT false NOT NULL,120 "supports_images" boolean DEFAULT true NOT NULL,121 "supports_catalog" boolean DEFAULT false NOT NULL,122 "supports_population" boolean DEFAULT false NOT NULL,123 "refresh_frequency_minutes" integer DEFAULT 1440 NOT NULL,124 "priority" text DEFAULT 'medium' NOT NULL,125 "status" text DEFAULT 'active' NOT NULL,126 "schema_version" text DEFAULT '1.0' NOT NULL,127 "connector_version" text DEFAULT '1.0.0' NOT NULL,128 "config" jsonb DEFAULT '{}'::jsonb NOT NULL,129 "last_run_at" timestamp with time zone,130 "last_success_at" timestamp with time zone,131 "next_run_at" timestamp with time zone,132 "created_at" timestamp with time zone DEFAULT now() NOT NULL,133 "updated_at" timestamp with time zone DEFAULT now() NOT NULL134);135--> statement-breakpoint136CREATE TABLE "costs" (137 "id" text PRIMARY KEY NOT NULL,138 "occurred_at" timestamp with time zone NOT NULL,139 "kind" text NOT NULL,140 "provider" text,141 "connector_id" text,142 "category_slug" text,143 "endpoint" text,144 "user_id" text,145 "units" real DEFAULT 1 NOT NULL,146 "credits" real DEFAULT 0 NOT NULL,147 "usd_est" real DEFAULT 0 NOT NULL,148 "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL149);150--> statement-breakpoint151CREATE TABLE "crawl_state" (152 "url_hash" text PRIMARY KEY NOT NULL,153 "connector_id" text NOT NULL,154 "url" text NOT NULL,155 "etag" text,156 "last_modified" text,157 "content_hash" text,158 "last_fetched_at" timestamp with time zone,159 "last_changed_at" timestamp with time zone,160 "fetch_count" integer DEFAULT 0 NOT NULL,161 "change_count" integer DEFAULT 0 NOT NULL,162 "change_interval_hours" real,163 "next_fetch_at" timestamp with time zone,164 "last_status" integer,165 "failures" integer DEFAULT 0 NOT NULL166);167--> statement-breakpoint168CREATE TABLE "sources" (169 "id" text PRIMARY KEY NOT NULL,170 "name" text NOT NULL,171 "source_type" text NOT NULL,172 "url" text,173 "countries" text[] DEFAULT '{}'::text[] NOT NULL,174 "languages" text[] DEFAULT '{}'::text[] NOT NULL,175 "currencies" text[] DEFAULT '{}'::text[] NOT NULL,176 "trust_score" numeric(8, 6) DEFAULT 0.5 NOT NULL,177 "trust_factors" jsonb DEFAULT '{}'::jsonb NOT NULL,178 "attribution_required" boolean DEFAULT true NOT NULL,179 "terms_url" text,180 "active" boolean DEFAULT true NOT NULL,181 "created_at" timestamp with time zone DEFAULT now() NOT NULL,182 "updated_at" timestamp with time zone DEFAULT now() NOT NULL183);184--> statement-breakpoint185CREATE TABLE "audit_log" (186 "id" text PRIMARY KEY NOT NULL,187 "entity_type" text NOT NULL,188 "entity_id" text NOT NULL,189 "action" text NOT NULL,190 "reason" text NOT NULL,191 "actor" text DEFAULT 'system' NOT NULL,192 "details" jsonb DEFAULT '{}'::jsonb NOT NULL,193 "created_at" timestamp with time zone DEFAULT now() NOT NULL194);195--> statement-breakpoint196CREATE TABLE "events" (197 "id" text PRIMARY KEY NOT NULL,198 "type" text NOT NULL,199 "entity_type" text,200 "entity_id" text,201 "payload" jsonb DEFAULT '{}'::jsonb NOT NULL,202 "created_at" timestamp with time zone DEFAULT now() NOT NULL203);204--> statement-breakpoint205CREATE TABLE "normalized_records" (206 "id" text PRIMARY KEY NOT NULL,207 "raw_record_id" text NOT NULL,208 "connector_id" text NOT NULL,209 "source_id" text NOT NULL,210 "kind" text NOT NULL,211 "payload" jsonb NOT NULL,212 "asset_id" text,213 "variant_id" text,214 "match_method" text,215 "match_confidence" numeric(8, 6),216 "status" text DEFAULT 'pending' NOT NULL,217 "reject_reason" text,218 "target_id" text,219 "created_at" timestamp with time zone DEFAULT now() NOT NULL,220 "processed_at" timestamp with time zone221);222--> statement-breakpoint223CREATE TABLE "raw_records" (224 "id" text PRIMARY KEY NOT NULL,225 "connector_id" text NOT NULL,226 "source_id" text NOT NULL,227 "run_id" text,228 "engine" text NOT NULL,229 "url" text NOT NULL,230 "external_id" text,231 "kind" text NOT NULL,232 "fetched_at" timestamp with time zone NOT NULL,233 "content_hash" text NOT NULL,234 "http_status" integer,235 "payload" jsonb NOT NULL,236 "snapshot_ref" text,237 "parser_version" text NOT NULL,238 "connector_version" text NOT NULL,239 "processed_at" timestamp with time zone,240 "process_error" text,241 "created_at" timestamp with time zone DEFAULT now() NOT NULL242);243--> statement-breakpoint244CREATE TABLE "asset_embeddings" (245 "asset_id" text PRIMARY KEY NOT NULL,246 "model" text NOT NULL,247 "embedding" vector(1536) NOT NULL,248 "updated_at" timestamp with time zone DEFAULT now() NOT NULL249);250--> statement-breakpoint251CREATE TABLE "asset_stats" (252 "asset_id" text PRIMARY KEY NOT NULL,253 "riv_usd" numeric(18, 4),254 "riv_low_usd" numeric(18, 4),255 "riv_high_usd" numeric(18, 4),256 "riv_confidence" numeric(8, 6),257 "riv_sample_size" integer DEFAULT 0 NOT NULL,258 "riv_variant_id" text,259 "latest_sale_usd" numeric(18, 4),260 "latest_sale_at" timestamp with time zone,261 "change_1d" numeric(8, 6),262 "change_7d" numeric(8, 6),263 "change_30d" numeric(8, 6),264 "change_90d" numeric(8, 6),265 "change_1y" numeric(8, 6),266 "ath_usd" numeric(18, 4),267 "ath_at" timestamp with time zone,268 "atl_usd" numeric(18, 4),269 "atl_at" timestamp with time zone,270 "sales_count" integer DEFAULT 0 NOT NULL,271 "sales_30d" integer DEFAULT 0 NOT NULL,272 "sales_1y" integer DEFAULT 0 NOT NULL,273 "volume_30d_usd" numeric(18, 4),274 "active_listings" integer DEFAULT 0 NOT NULL,275 "min_ask_usd" numeric(18, 4),276 "observations_count" integer DEFAULT 0 NOT NULL,277 "sources_count" integer DEFAULT 0 NOT NULL,278 "liquidity_score" real,279 "rarity_score" real,280 "momentum_7d" real,281 "momentum_30d" real,282 "momentum_90d" real,283 "momentum_1y" real,284 "trending_score" real,285 "value_opportunity" real,286 "data_quality" real,287 "watchers" integer DEFAULT 0 NOT NULL,288 "views_30d" integer DEFAULT 0 NOT NULL,289 "updated_at" timestamp with time zone DEFAULT now() NOT NULL290);291--> statement-breakpoint292CREATE TABLE "asset_variants" (293 "id" text PRIMARY KEY NOT NULL,294 "asset_id" text NOT NULL,295 "variant_key" text NOT NULL,296 "grader" text,297 "grade" text,298 "qualifier" text,299 "condition" text,300 "completeness" text,301 "size_label" text,302 "label" text NOT NULL,303 "is_default" boolean DEFAULT false NOT NULL,304 "created_at" timestamp with time zone DEFAULT now() NOT NULL305);306--> statement-breakpoint307CREATE TABLE "assets" (308 "id" text PRIMARY KEY NOT NULL,309 "slug" text NOT NULL,310 "canonical_key" text NOT NULL,311 "category_slug" text NOT NULL,312 "subcategory_slug" text,313 "family_slug" text NOT NULL,314 "franchise" text,315 "brand" text,316 "series" text,317 "set_slug" text,318 "set_name" text,319 "set_code" text,320 "name" text NOT NULL,321 "title" text NOT NULL,322 "model" text,323 "reference" text,324 "number" text,325 "year" integer,326 "edition" text,327 "variant" text,328 "language" text,329 "region" text,330 "country" text,331 "material" text,332 "size" text,333 "color" text,334 "rarity" text,335 "production_quantity" integer,336 "original_msrp" numeric(18, 4),337 "original_msrp_currency" text,338 "release_date" text,339 "description" text,340 "hero_image_url" text,341 "identifiers" jsonb DEFAULT '{}'::jsonb NOT NULL,342 "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,343 "merged_from" text[] DEFAULT '{}'::text[] NOT NULL,344 "data_quality" real DEFAULT 0 NOT NULL,345 "verified" boolean DEFAULT false NOT NULL,346 "search" "tsvector" GENERATED ALWAYS AS (setweight(to_tsvector('simple', coalesce(name, '')), 'A') || setweight(to_tsvector('simple', coalesce(set_name, '') || ' ' || coalesce(number, '') || ' ' || coalesce(variant, '') || ' ' || coalesce(edition, '')), 'B') || setweight(to_tsvector('simple', coalesce(brand, '') || ' ' || coalesce(franchise, '') || ' ' || coalesce(reference, '') || ' ' || coalesce(model, '') || ' ' || coalesce(year::text, '') || ' ' || coalesce(category_slug, '')), 'C')) STORED,347 "created_at" timestamp with time zone DEFAULT now() NOT NULL,348 "updated_at" timestamp with time zone DEFAULT now() NOT NULL349);350--> statement-breakpoint351CREATE TABLE "grade_premiums" (352 "id" text PRIMARY KEY NOT NULL,353 "category_slug" text NOT NULL,354 "grader" text NOT NULL,355 "grade" text NOT NULL,356 "market_multiplier" real NOT NULL,357 "sample_size" integer NOT NULL,358 "computed_at" timestamp with time zone NOT NULL359);360--> statement-breakpoint361CREATE TABLE "images" (362 "id" text PRIMARY KEY NOT NULL,363 "asset_id" text,364 "listing_id" text,365 "sale_id" text,366 "source_id" text,367 "url" text NOT NULL,368 "role" text DEFAULT 'gallery' NOT NULL,369 "width" integer,370 "height" integer,371 "phash" text,372 "embedding" vector(512),373 "attribution" text,374 "created_at" timestamp with time zone DEFAULT now() NOT NULL375);376--> statement-breakpoint377CREATE TABLE "population_reports" (378 "id" text PRIMARY KEY NOT NULL,379 "asset_id" text NOT NULL,380 "grader" text NOT NULL,381 "source_id" text NOT NULL,382 "source_url" text,383 "report_date" text NOT NULL,384 "total" integer NOT NULL,385 "by_grade" jsonb NOT NULL,386 "created_at" timestamp with time zone DEFAULT now() NOT NULL387);388--> statement-breakpoint389CREATE TABLE "variant_stats" (390 "variant_id" text PRIMARY KEY NOT NULL,391 "asset_id" text NOT NULL,392 "riv_usd" numeric(18, 4),393 "riv_low_usd" numeric(18, 4),394 "riv_high_usd" numeric(18, 4),395 "riv_confidence" numeric(8, 6),396 "riv_sample_size" integer DEFAULT 0 NOT NULL,397 "latest_sale_usd" numeric(18, 4),398 "latest_sale_at" timestamp with time zone,399 "change_30d" numeric(8, 6),400 "change_1y" numeric(8, 6),401 "sales_count" integer DEFAULT 0 NOT NULL,402 "sales_30d" integer DEFAULT 0 NOT NULL,403 "active_listings" integer DEFAULT 0 NOT NULL,404 "min_ask_usd" numeric(18, 4),405 "liquidity_score" real,406 "updated_at" timestamp with time zone DEFAULT now() NOT NULL407);408--> statement-breakpoint409CREATE TABLE "auction_lots" (410 "id" text PRIMARY KEY NOT NULL,411 "auction_id" text NOT NULL,412 "asset_id" text,413 "variant_id" text,414 "source_id" text NOT NULL,415 "lot_number" text,416 "title" text NOT NULL,417 "url" text NOT NULL,418 "estimate_low" numeric(18, 4),419 "estimate_high" numeric(18, 4),420 "current_bid" numeric(18, 4),421 "hammer_price" numeric(18, 4),422 "currency" text,423 "bid_count" integer,424 "starts_at" timestamp with time zone,425 "ends_at" timestamp with time zone,426 "status" text DEFAULT 'upcoming' NOT NULL,427 "image_urls" jsonb DEFAULT '[]'::jsonb NOT NULL,428 "grader" text,429 "grade" text,430 "created_at" timestamp with time zone DEFAULT now() NOT NULL,431 "updated_at" timestamp with time zone DEFAULT now() NOT NULL432);433--> statement-breakpoint434CREATE TABLE "auctions" (435 "id" text PRIMARY KEY NOT NULL,436 "source_id" text NOT NULL,437 "auction_house" text NOT NULL,438 "name" text NOT NULL,439 "url" text NOT NULL,440 "starts_at" timestamp with time zone,441 "ends_at" timestamp with time zone,442 "location" text,443 "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL,444 "lot_count" integer,445 "status" text DEFAULT 'upcoming' NOT NULL,446 "currency" text,447 "created_at" timestamp with time zone DEFAULT now() NOT NULL,448 "updated_at" timestamp with time zone DEFAULT now() NOT NULL449);450--> statement-breakpoint451CREATE TABLE "cross_listing_groups" (452 "id" text PRIMARY KEY NOT NULL,453 "asset_id" text,454 "signals" jsonb DEFAULT '{}'::jsonb NOT NULL,455 "created_at" timestamp with time zone DEFAULT now() NOT NULL456);457--> statement-breakpoint458CREATE TABLE "fx_rates" (459 "date" date NOT NULL,460 "base" text NOT NULL,461 "quote" text NOT NULL,462 "rate" real NOT NULL,463 "source" text DEFAULT 'ecb' NOT NULL464);465--> statement-breakpoint466CREATE TABLE "listing_events" (467 "id" text PRIMARY KEY NOT NULL,468 "listing_id" text NOT NULL,469 "event_type" text NOT NULL,470 "old_price" numeric(18, 4),471 "new_price" numeric(18, 4),472 "currency" text,473 "occurred_at" timestamp with time zone NOT NULL474);475--> statement-breakpoint476CREATE TABLE "listings" (477 "id" text PRIMARY KEY NOT NULL,478 "asset_id" text NOT NULL,479 "variant_id" text,480 "source_id" text NOT NULL,481 "connector_id" text NOT NULL,482 "raw_record_id" text,483 "source_url" text NOT NULL,484 "external_id" text NOT NULL,485 "listing_type" text DEFAULT 'unknown' NOT NULL,486 "price" numeric(18, 4),487 "currency" text,488 "price_usd" numeric(18, 4),489 "seller" text,490 "seller_reputation" text,491 "location" text,492 "shipping_cost" numeric(18, 4),493 "quantity" integer,494 "condition" text,495 "grader" text,496 "grade" text,497 "certification_number" text,498 "image_urls" jsonb DEFAULT '[]'::jsonb NOT NULL,499 "raw_title" text NOT NULL,500 "description" text,501 "listed_at" timestamp with time zone,502 "ends_at" timestamp with time zone,503 "availability" text DEFAULT 'available' NOT NULL,504 "bid_count" integer,505 "first_seen_at" timestamp with time zone NOT NULL,506 "last_seen_at" timestamp with time zone NOT NULL,507 "price_changed_at" timestamp with time zone,508 "cross_listing_group_id" text,509 "confidence" numeric(8, 6) DEFAULT 0.8 NOT NULL,510 "data_quality" real DEFAULT 0 NOT NULL,511 "flags" text[] DEFAULT '{}'::text[] NOT NULL,512 "discount_to_riv" numeric(8, 6),513 "created_at" timestamp with time zone DEFAULT now() NOT NULL,514 "updated_at" timestamp with time zone DEFAULT now() NOT NULL515);516--> statement-breakpoint517CREATE TABLE "news" (518 "id" text PRIMARY KEY NOT NULL,519 "source_id" text NOT NULL,520 "url" text NOT NULL,521 "title" text NOT NULL,522 "summary" text,523 "ai_summary" text,524 "published_at" timestamp with time zone,525 "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL,526 "news_type" text,527 "image_url" text,528 "fetched_at" timestamp with time zone NOT NULL529);530--> statement-breakpoint531CREATE TABLE "price_observations" (532 "id" text PRIMARY KEY NOT NULL,533 "asset_id" text NOT NULL,534 "variant_id" text,535 "source_id" text NOT NULL,536 "connector_id" text NOT NULL,537 "raw_record_id" text,538 "source_url" text NOT NULL,539 "price_kind" text NOT NULL,540 "price" numeric(18, 4) NOT NULL,541 "currency" text NOT NULL,542 "price_usd" numeric(18, 4) NOT NULL,543 "observation_date" date NOT NULL,544 "sample_size" integer,545 "dedupe_key" text NOT NULL,546 "created_at" timestamp with time zone DEFAULT now() NOT NULL547);548--> statement-breakpoint549CREATE TABLE "sales" (550 "id" text PRIMARY KEY NOT NULL,551 "asset_id" text NOT NULL,552 "variant_id" text,553 "source_id" text NOT NULL,554 "connector_id" text NOT NULL,555 "raw_record_id" text,556 "normalized_record_id" text,557 "source_url" text NOT NULL,558 "external_id" text,559 "sale_type" text DEFAULT 'unknown' NOT NULL,560 "sale_date" timestamp with time zone NOT NULL,561 "price" numeric(18, 4) NOT NULL,562 "currency" text NOT NULL,563 "price_usd" numeric(18, 4) NOT NULL,564 "fx_rate" real,565 "fx_date" date,566 "buyer_premium_included" boolean,567 "quantity" integer DEFAULT 1 NOT NULL,568 "is_bundle" boolean DEFAULT false NOT NULL,569 "condition" text,570 "grader" text,571 "grade" text,572 "certification_number" text,573 "location" text,574 "auction_house" text,575 "lot_number" text,576 "image_urls" jsonb DEFAULT '[]'::jsonb NOT NULL,577 "raw_title" text NOT NULL,578 "confidence" numeric(8, 6) DEFAULT 0.8 NOT NULL,579 "data_quality" real DEFAULT 0 NOT NULL,580 "status" text DEFAULT 'valid' NOT NULL,581 "flags" text[] DEFAULT '{}'::text[] NOT NULL,582 "dedupe_key" text NOT NULL,583 "created_at" timestamp with time zone DEFAULT now() NOT NULL584);585--> statement-breakpoint586CREATE TABLE "benchmarks" (587 "ticker" text NOT NULL,588 "date" date NOT NULL,589 "value" real NOT NULL,590 "source" text NOT NULL,591 CONSTRAINT "benchmarks_ticker_date_pk" PRIMARY KEY("ticker","date")592);593--> statement-breakpoint594CREATE TABLE "category_snapshots" (595 "category_slug" text NOT NULL,596 "date" date NOT NULL,597 "index_value" real,598 "tracked_assets" integer DEFAULT 0 NOT NULL,599 "assets_with_valuation" integer DEFAULT 0 NOT NULL,600 "sales" integer DEFAULT 0 NOT NULL,601 "volume_usd" numeric(18, 4),602 "median_sale_usd" numeric(18, 4),603 "active_listings" integer DEFAULT 0 NOT NULL,604 "market_cap_est_usd" numeric(18, 4),605 "liquidity_score" real,606 "change_1d" numeric(8, 6),607 "change_7d" numeric(8, 6),608 "change_30d" numeric(8, 6),609 "change_1y" numeric(8, 6),610 CONSTRAINT "category_snapshots_category_slug_date_pk" PRIMARY KEY("category_slug","date")611);612--> statement-breakpoint613CREATE TABLE "correlations" (614 "a" text NOT NULL,615 "b" text NOT NULL,616 "window_days" integer NOT NULL,617 "coefficient" real NOT NULL,618 "observations" integer NOT NULL,619 "computed_at" timestamp with time zone NOT NULL,620 CONSTRAINT "correlations_a_b_window_days_pk" PRIMARY KEY("a","b","window_days")621);622--> statement-breakpoint623CREATE TABLE "index_constituents" (624 "index_id" text NOT NULL,625 "asset_id" text NOT NULL,626 "variant_id" text DEFAULT '' NOT NULL,627 "weight" real DEFAULT 1 NOT NULL,628 "added_at" date NOT NULL,629 "removed_at" date,630 "reason" text,631 CONSTRAINT "index_constituents_index_id_asset_id_variant_id_added_at_pk" PRIMARY KEY("index_id","asset_id","variant_id","added_at")632);633--> statement-breakpoint634CREATE TABLE "index_values" (635 "index_id" text NOT NULL,636 "date" date NOT NULL,637 "value" real NOT NULL,638 "constituents_count" integer DEFAULT 0 NOT NULL,639 "transactions" integer DEFAULT 0 NOT NULL,640 "volume_usd" numeric(18, 4),641 "median_sale_usd" numeric(18, 4),642 "avg_sale_usd" numeric(18, 4),643 "market_cap_est_usd" numeric(18, 4),644 "market_cap_confidence" text,645 "liquidity_score" real,646 "momentum" real,647 "breadth" integer,648 "tracked_assets" integer,649 "coverage" real,650 CONSTRAINT "index_values_index_id_date_pk" PRIMARY KEY("index_id","date")651);652--> statement-breakpoint653CREATE TABLE "indices" (654 "id" text PRIMARY KEY NOT NULL,655 "ticker" text NOT NULL,656 "name" text NOT NULL,657 "description" text,658 "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL,659 "family_slugs" text[] DEFAULT '{}'::text[] NOT NULL,660 "parent_ticker" text,661 "methodology" text DEFAULT 'chain_linked_equal_weight_v1' NOT NULL,662 "weighting" text DEFAULT 'equal' NOT NULL,663 "base_date" date NOT NULL,664 "base_value" real DEFAULT 1000 NOT NULL,665 "min_constituents" integer DEFAULT 10 NOT NULL,666 "active" boolean DEFAULT true NOT NULL,667 "is_flagship" boolean DEFAULT false NOT NULL,668 "color" text,669 "created_at" timestamp with time zone DEFAULT now() NOT NULL,670 CONSTRAINT "indices_ticker_unique" UNIQUE("ticker")671);672--> statement-breakpoint673CREATE TABLE "price_snapshots" (674 "asset_id" text NOT NULL,675 "variant_id" text DEFAULT '' NOT NULL,676 "date" date NOT NULL,677 "riv_usd" numeric(18, 4),678 "latest_sale_usd" numeric(18, 4),679 "median_usd" numeric(18, 4),680 "sales_count" integer DEFAULT 0 NOT NULL,681 "volume_usd" numeric(18, 4),682 "listings_count" integer DEFAULT 0 NOT NULL,683 "min_ask_usd" numeric(18, 4),684 "observation_usd" numeric(18, 4),685 CONSTRAINT "price_snapshots_asset_id_variant_id_date_pk" PRIMARY KEY("asset_id","variant_id","date")686);687--> statement-breakpoint688CREATE TABLE "radar_findings" (689 "id" text PRIMARY KEY NOT NULL,690 "asset_id" text NOT NULL,691 "kind" text NOT NULL,692 "score" real NOT NULL,693 "evidence" jsonb DEFAULT '{}'::jsonb NOT NULL,694 "entity_type" text,695 "entity_id" text,696 "detected_at" timestamp with time zone NOT NULL,697 "expires_at" timestamp with time zone698);699--> statement-breakpoint700CREATE TABLE "valuations" (701 "id" text PRIMARY KEY NOT NULL,702 "asset_id" text NOT NULL,703 "variant_id" text,704 "computed_at" timestamp with time zone NOT NULL,705 "riv_usd" numeric(18, 4),706 "low_usd" numeric(18, 4),707 "high_usd" numeric(18, 4),708 "confidence" numeric(8, 6) DEFAULT 0 NOT NULL,709 "confidence_label" text DEFAULT 'insufficient' NOT NULL,710 "sample_size" integer DEFAULT 0 NOT NULL,711 "window_days" integer DEFAULT 365 NOT NULL,712 "methods" jsonb DEFAULT '{}'::jsonb NOT NULL,713 "sales_used" text[] DEFAULT '{}'::text[] NOT NULL,714 "observations_used" integer DEFAULT 0 NOT NULL,715 "method" text DEFAULT 'ensemble_v1' NOT NULL,716 "notes" text[] DEFAULT '{}'::text[] NOT NULL717);718--> statement-breakpoint719CREATE TABLE "alert_events" (720 "id" text PRIMARY KEY NOT NULL,721 "alert_id" text NOT NULL,722 "user_id" text NOT NULL,723 "message" text NOT NULL,724 "payload" jsonb DEFAULT '{}'::jsonb NOT NULL,725 "read_at" timestamp with time zone,726 "created_at" timestamp with time zone DEFAULT now() NOT NULL727);728--> statement-breakpoint729CREATE TABLE "alerts" (730 "id" text PRIMARY KEY NOT NULL,731 "user_id" text NOT NULL,732 "alert_type" text NOT NULL,733 "target_type" text NOT NULL,734 "target_id" text NOT NULL,735 "threshold" numeric(18, 4),736 "currency" text,737 "params" jsonb DEFAULT '{}'::jsonb NOT NULL,738 "channel" text DEFAULT 'inapp' NOT NULL,739 "active" boolean DEFAULT true NOT NULL,740 "last_triggered_at" timestamp with time zone,741 "created_at" timestamp with time zone DEFAULT now() NOT NULL742);743--> statement-breakpoint744CREATE TABLE "api_keys" (745 "id" text PRIMARY KEY NOT NULL,746 "user_id" text,747 "name" text NOT NULL,748 "prefix" text NOT NULL,749 "key_hash" text NOT NULL,750 "tier" text DEFAULT 'free' NOT NULL,751 "rate_limit_per_minute" integer DEFAULT 60 NOT NULL,752 "daily_quota" integer DEFAULT 1000 NOT NULL,753 "created_at" timestamp with time zone DEFAULT now() NOT NULL,754 "last_used_at" timestamp with time zone,755 "revoked_at" timestamp with time zone,756 CONSTRAINT "api_keys_key_hash_unique" UNIQUE("key_hash")757);758--> statement-breakpoint759CREATE TABLE "api_usage" (760 "key_id" text NOT NULL,761 "date" date NOT NULL,762 "endpoint" text NOT NULL,763 "count" integer DEFAULT 0 NOT NULL,764 "latency_ms_avg" real,765 CONSTRAINT "api_usage_key_id_date_endpoint_pk" PRIMARY KEY("key_id","date","endpoint")766);767--> statement-breakpoint768CREATE TABLE "asset_views" (769 "asset_id" text NOT NULL,770 "date" date NOT NULL,771 "views" integer DEFAULT 0 NOT NULL,772 CONSTRAINT "asset_views_asset_id_date_pk" PRIMARY KEY("asset_id","date")773);774--> statement-breakpoint775CREATE TABLE "collection_items" (776 "id" text PRIMARY KEY NOT NULL,777 "collection_id" text NOT NULL,778 "asset_id" text NOT NULL,779 "variant_id" text,780 "quantity" integer DEFAULT 1 NOT NULL,781 "acquired_at" date,782 "purchase_price" numeric(18, 4),783 "purchase_currency" text,784 "purchase_price_usd" numeric(18, 4),785 "source" text,786 "grader" text,787 "grade" text,788 "certification_number" text,789 "serial" text,790 "photos" jsonb DEFAULT '[]'::jsonb NOT NULL,791 "notes" text,792 "created_at" timestamp with time zone DEFAULT now() NOT NULL,793 "updated_at" timestamp with time zone DEFAULT now() NOT NULL794);795--> statement-breakpoint796CREATE TABLE "collection_snapshots" (797 "collection_id" text NOT NULL,798 "date" date NOT NULL,799 "value_usd" numeric(18, 4) NOT NULL,800 "cost_basis_usd" numeric(18, 4) NOT NULL,801 "items" integer NOT NULL,802 CONSTRAINT "collection_snapshots_collection_id_date_pk" PRIMARY KEY("collection_id","date")803);804--> statement-breakpoint805CREATE TABLE "collections" (806 "id" text PRIMARY KEY NOT NULL,807 "user_id" text NOT NULL,808 "name" text NOT NULL,809 "description" text,810 "is_public" boolean DEFAULT false NOT NULL,811 "public_slug" text,812 "created_at" timestamp with time zone DEFAULT now() NOT NULL,813 "updated_at" timestamp with time zone DEFAULT now() NOT NULL814);815--> statement-breakpoint816CREATE TABLE "search_log" (817 "id" text PRIMARY KEY NOT NULL,818 "query" text NOT NULL,819 "normalized" text NOT NULL,820 "results" integer NOT NULL,821 "user_id" text,822 "created_at" timestamp with time zone DEFAULT now() NOT NULL823);824--> statement-breakpoint825CREATE TABLE "sessions" (826 "id" text PRIMARY KEY NOT NULL,827 "user_id" text NOT NULL,828 "expires_at" timestamp with time zone NOT NULL,829 "user_agent" text,830 "created_at" timestamp with time zone DEFAULT now() NOT NULL831);832--> statement-breakpoint833CREATE TABLE "users" (834 "id" text PRIMARY KEY NOT NULL,835 "email" text NOT NULL,836 "email_verified_at" timestamp with time zone,837 "password_hash" text,838 "name" text,839 "role" text DEFAULT 'user' NOT NULL,840 "display_currency" text DEFAULT 'USD' NOT NULL,841 "providers" jsonb DEFAULT '[]'::jsonb NOT NULL,842 "preferences" jsonb DEFAULT '{}'::jsonb NOT NULL,843 "created_at" timestamp with time zone DEFAULT now() NOT NULL,844 "last_login_at" timestamp with time zone,845 CONSTRAINT "users_email_unique" UNIQUE("email")846);847--> statement-breakpoint848CREATE TABLE "watchlist_items" (849 "id" text PRIMARY KEY NOT NULL,850 "watchlist_id" text NOT NULL,851 "target_type" text NOT NULL,852 "target_id" text NOT NULL,853 "created_at" timestamp with time zone DEFAULT now() NOT NULL854);855--> statement-breakpoint856CREATE TABLE "watchlists" (857 "id" text PRIMARY KEY NOT NULL,858 "user_id" text NOT NULL,859 "name" text DEFAULT 'Watchlist' NOT NULL,860 "created_at" timestamp with time zone DEFAULT now() NOT NULL861);862--> statement-breakpoint863CREATE INDEX "brands_name_idx" ON "brands" USING btree ("name");--> statement-breakpoint864CREATE INDEX "categories_parent_idx" ON "categories" USING btree ("parent_slug");--> statement-breakpoint865CREATE INDEX "categories_family_idx" ON "categories" USING btree ("family_slug");--> statement-breakpoint866CREATE INDEX "sets_category_idx" ON "sets" USING btree ("category_slug");--> statement-breakpoint867CREATE UNIQUE INDEX "sets_category_code_uq" ON "sets" USING btree ("category_slug","code");--> statement-breakpoint868CREATE INDEX "connector_runs_connector_started_idx" ON "connector_runs" USING btree ("connector_id","started_at");--> statement-breakpoint869CREATE INDEX "costs_occurred_idx" ON "costs" USING btree ("occurred_at");--> statement-breakpoint870CREATE INDEX "costs_connector_idx" ON "costs" USING btree ("connector_id");--> statement-breakpoint871CREATE INDEX "crawl_state_next_idx" ON "crawl_state" USING btree ("connector_id","next_fetch_at");--> statement-breakpoint872CREATE INDEX "audit_entity_idx" ON "audit_log" USING btree ("entity_type","entity_id");--> statement-breakpoint873CREATE INDEX "events_type_created_idx" ON "events" USING btree ("type","created_at");--> statement-breakpoint874CREATE INDEX "events_entity_idx" ON "events" USING btree ("entity_type","entity_id");--> statement-breakpoint875CREATE INDEX "normalized_records_status_idx" ON "normalized_records" USING btree ("status","created_at");--> statement-breakpoint876CREATE INDEX "normalized_records_asset_idx" ON "normalized_records" USING btree ("asset_id");--> statement-breakpoint877CREATE UNIQUE INDEX "normalized_records_raw_uq" ON "normalized_records" USING btree ("raw_record_id");--> statement-breakpoint878CREATE UNIQUE INDEX "raw_records_connector_hash_uq" ON "raw_records" USING btree ("connector_id","content_hash");--> statement-breakpoint879CREATE INDEX "raw_records_connector_fetched_idx" ON "raw_records" USING btree ("connector_id","fetched_at");--> statement-breakpoint880CREATE INDEX "raw_records_unprocessed_idx" ON "raw_records" USING btree ("processed_at");--> statement-breakpoint881CREATE INDEX "raw_records_external_idx" ON "raw_records" USING btree ("connector_id","external_id");--> statement-breakpoint882CREATE INDEX "asset_stats_riv_idx" ON "asset_stats" USING btree ("riv_usd");--> statement-breakpoint883CREATE INDEX "asset_stats_trending_idx" ON "asset_stats" USING btree ("trending_score");--> statement-breakpoint884CREATE INDEX "asset_stats_liquidity_idx" ON "asset_stats" USING btree ("liquidity_score");--> statement-breakpoint885CREATE UNIQUE INDEX "asset_variants_uq" ON "asset_variants" USING btree ("asset_id","variant_key");--> statement-breakpoint886CREATE INDEX "asset_variants_asset_idx" ON "asset_variants" USING btree ("asset_id");--> statement-breakpoint887CREATE UNIQUE INDEX "assets_canonical_key_uq" ON "assets" USING btree ("canonical_key");--> statement-breakpoint888CREATE UNIQUE INDEX "assets_slug_uq" ON "assets" USING btree ("slug");--> statement-breakpoint889CREATE INDEX "assets_category_idx" ON "assets" USING btree ("category_slug");--> statement-breakpoint890CREATE INDEX "assets_family_idx" ON "assets" USING btree ("family_slug");--> statement-breakpoint891CREATE INDEX "assets_set_idx" ON "assets" USING btree ("set_slug");--> statement-breakpoint892CREATE INDEX "assets_search_gin" ON "assets" USING gin ("search");--> statement-breakpoint893CREATE INDEX "assets_title_trgm" ON "assets" USING gin ("title" gin_trgm_ops);--> statement-breakpoint894CREATE INDEX "assets_identifiers_gin" ON "assets" USING gin ("identifiers");--> statement-breakpoint895CREATE UNIQUE INDEX "grade_premiums_uq" ON "grade_premiums" USING btree ("category_slug","grader","grade");--> statement-breakpoint896CREATE INDEX "images_asset_idx" ON "images" USING btree ("asset_id");--> statement-breakpoint897CREATE INDEX "images_phash_idx" ON "images" USING btree ("phash");--> statement-breakpoint898CREATE UNIQUE INDEX "images_url_uq" ON "images" USING btree ("url");--> statement-breakpoint899CREATE UNIQUE INDEX "population_reports_uq" ON "population_reports" USING btree ("asset_id","grader","report_date");--> statement-breakpoint900CREATE UNIQUE INDEX "auction_lots_url_uq" ON "auction_lots" USING btree ("url");--> statement-breakpoint901CREATE INDEX "auction_lots_auction_idx" ON "auction_lots" USING btree ("auction_id");--> statement-breakpoint902CREATE INDEX "auction_lots_asset_idx" ON "auction_lots" USING btree ("asset_id");--> statement-breakpoint903CREATE INDEX "auction_lots_ends_idx" ON "auction_lots" USING btree ("ends_at");--> statement-breakpoint904CREATE UNIQUE INDEX "auctions_url_uq" ON "auctions" USING btree ("url");--> statement-breakpoint905CREATE INDEX "auctions_ends_idx" ON "auctions" USING btree ("ends_at");--> statement-breakpoint906CREATE UNIQUE INDEX "fx_rates_uq" ON "fx_rates" USING btree ("date","base","quote");--> statement-breakpoint907CREATE INDEX "listing_events_listing_idx" ON "listing_events" USING btree ("listing_id","occurred_at");--> statement-breakpoint908CREATE UNIQUE INDEX "listings_source_external_uq" ON "listings" USING btree ("source_id","external_id");--> statement-breakpoint909CREATE INDEX "listings_asset_avail_idx" ON "listings" USING btree ("asset_id","availability");--> statement-breakpoint910CREATE INDEX "listings_avail_price_idx" ON "listings" USING btree ("availability","price_usd");--> statement-breakpoint911CREATE INDEX "listings_ends_idx" ON "listings" USING btree ("ends_at");--> statement-breakpoint912CREATE INDEX "listings_last_seen_idx" ON "listings" USING btree ("last_seen_at");--> statement-breakpoint913CREATE UNIQUE INDEX "news_url_uq" ON "news" USING btree ("url");--> statement-breakpoint914CREATE INDEX "news_published_idx" ON "news" USING btree ("published_at");--> statement-breakpoint915CREATE UNIQUE INDEX "price_observations_dedupe_uq" ON "price_observations" USING btree ("dedupe_key");--> statement-breakpoint916CREATE INDEX "price_observations_asset_date_idx" ON "price_observations" USING btree ("asset_id","observation_date");--> statement-breakpoint917CREATE UNIQUE INDEX "sales_dedupe_uq" ON "sales" USING btree ("dedupe_key");--> statement-breakpoint918CREATE INDEX "sales_asset_date_idx" ON "sales" USING btree ("asset_id","sale_date");--> statement-breakpoint919CREATE INDEX "sales_variant_date_idx" ON "sales" USING btree ("variant_id","sale_date");--> statement-breakpoint920CREATE INDEX "sales_source_idx" ON "sales" USING btree ("source_id","sale_date");--> statement-breakpoint921CREATE INDEX "sales_date_idx" ON "sales" USING btree ("sale_date");--> statement-breakpoint922CREATE INDEX "sales_price_idx" ON "sales" USING btree ("price_usd");--> statement-breakpoint923CREATE INDEX "index_constituents_asset_idx" ON "index_constituents" USING btree ("asset_id");--> statement-breakpoint924CREATE INDEX "price_snapshots_date_idx" ON "price_snapshots" USING btree ("date");--> statement-breakpoint925CREATE INDEX "radar_kind_idx" ON "radar_findings" USING btree ("kind","detected_at");--> statement-breakpoint926CREATE UNIQUE INDEX "radar_entity_uq" ON "radar_findings" USING btree ("kind","entity_type","entity_id");--> statement-breakpoint927CREATE INDEX "valuations_asset_idx" ON "valuations" USING btree ("asset_id","computed_at");--> statement-breakpoint928CREATE INDEX "valuations_variant_idx" ON "valuations" USING btree ("variant_id","computed_at");--> statement-breakpoint929CREATE INDEX "alert_events_user_idx" ON "alert_events" USING btree ("user_id","created_at");--> statement-breakpoint930CREATE INDEX "alerts_user_idx" ON "alerts" USING btree ("user_id");--> statement-breakpoint931CREATE INDEX "alerts_target_idx" ON "alerts" USING btree ("target_type","target_id","active");--> statement-breakpoint932CREATE INDEX "api_keys_user_idx" ON "api_keys" USING btree ("user_id");--> statement-breakpoint933CREATE INDEX "collection_items_collection_idx" ON "collection_items" USING btree ("collection_id");--> statement-breakpoint934CREATE INDEX "collection_items_asset_idx" ON "collection_items" USING btree ("asset_id");--> statement-breakpoint935CREATE INDEX "collections_user_idx" ON "collections" USING btree ("user_id");--> statement-breakpoint936CREATE UNIQUE INDEX "collections_public_slug_uq" ON "collections" USING btree ("public_slug");--> statement-breakpoint937CREATE INDEX "search_log_created_idx" ON "search_log" USING btree ("created_at");--> statement-breakpoint938CREATE INDEX "search_log_normalized_idx" ON "search_log" USING btree ("normalized");--> statement-breakpoint939CREATE INDEX "sessions_user_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint940CREATE UNIQUE INDEX "watchlist_items_uq" ON "watchlist_items" USING btree ("watchlist_id","target_type","target_id");--> statement-breakpoint941CREATE INDEX "watchlists_user_idx" ON "watchlists" USING btree ("user_id");