CREATE TABLE "brands" ( "slug" text PRIMARY KEY NOT NULL, "name" text NOT NULL, "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL, "country" text, "founded_year" integer, "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "categories" ( "slug" text PRIMARY KEY NOT NULL, "parent_slug" text, "family_slug" text NOT NULL, "name" text NOT NULL, "short_name" text, "description" text, "level" integer DEFAULT 0 NOT NULL, "phase" integer DEFAULT 3 NOT NULL, "active" boolean DEFAULT true NOT NULL, "condition_scale" text, "graders" text[] DEFAULT '{}'::text[] NOT NULL, "index_ticker" text, "attribute_schema" jsonb DEFAULT '{}'::jsonb NOT NULL, "sort_order" integer DEFAULT 0 NOT NULL, "compliance_flags" text[] DEFAULT '{}'::text[] NOT NULL, "icon" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "franchises" ( "slug" text PRIMARY KEY NOT NULL, "name" text NOT NULL, "owner" text, "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL, "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "graders" ( "slug" text PRIMARY KEY NOT NULL, "name" text NOT NULL, "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL, "scale" jsonb DEFAULT '{}'::jsonb NOT NULL, "population_url" text, "verify_url" text, "active" boolean DEFAULT true NOT NULL ); --> statement-breakpoint CREATE TABLE "sets" ( "slug" text PRIMARY KEY NOT NULL, "name" text NOT NULL, "code" text, "category_slug" text NOT NULL, "franchise_slug" text, "brand_slug" text, "release_year" integer, "release_date" text, "language" text, "total_items" integer, "identifiers" jsonb DEFAULT '{}'::jsonb NOT NULL, "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "taxonomy_proposals" ( "id" text PRIMARY KEY NOT NULL, "proposed_slug" text NOT NULL, "name" text NOT NULL, "parent_slug" text, "evidence" jsonb DEFAULT '{}'::jsonb NOT NULL, "volume_estimate" integer, "status" text DEFAULT 'pending' NOT NULL, "decided_by" text, "decided_at" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "connector_health" ( "connector_id" text PRIMARY KEY NOT NULL, "computed_at" timestamp with time zone NOT NULL, "status" text NOT NULL, "health" jsonb DEFAULT '{}'::jsonb NOT NULL ); --> statement-breakpoint CREATE TABLE "connector_runs" ( "id" text PRIMARY KEY NOT NULL, "connector_id" text NOT NULL, "trigger" text DEFAULT 'schedule' NOT NULL, "started_at" timestamp with time zone NOT NULL, "finished_at" timestamp with time zone, "status" text DEFAULT 'running' NOT NULL, "pages_attempted" integer DEFAULT 0 NOT NULL, "pages_success" integer DEFAULT 0 NOT NULL, "records_raw" integer DEFAULT 0 NOT NULL, "records_normalized" integer DEFAULT 0 NOT NULL, "records_duplicate" integer DEFAULT 0 NOT NULL, "records_rejected" integer DEFAULT 0 NOT NULL, "engine_stats" jsonb DEFAULT '{}'::jsonb NOT NULL, "anomalies" jsonb DEFAULT '[]'::jsonb NOT NULL, "error" text, "cost_credits" real DEFAULT 0 NOT NULL, "cost_usd_est" real DEFAULT 0 NOT NULL, "cursor" jsonb DEFAULT '{}'::jsonb NOT NULL ); --> statement-breakpoint CREATE TABLE "connectors" ( "id" text PRIMARY KEY NOT NULL, "source_id" text NOT NULL, "display_name" text NOT NULL, "engine_priority" text[] DEFAULT '{}'::text[] NOT NULL, "categories" text[] DEFAULT '{}'::text[] NOT NULL, "regions" text[] DEFAULT '{}'::text[] NOT NULL, "languages" text[] DEFAULT '{}'::text[] NOT NULL, "currency" text[] DEFAULT '{}'::text[] NOT NULL, "supports_listings" boolean DEFAULT false NOT NULL, "supports_sold" boolean DEFAULT false NOT NULL, "supports_auctions" boolean DEFAULT false NOT NULL, "supports_images" boolean DEFAULT true NOT NULL, "supports_catalog" boolean DEFAULT false NOT NULL, "supports_population" boolean DEFAULT false NOT NULL, "refresh_frequency_minutes" integer DEFAULT 1440 NOT NULL, "priority" text DEFAULT 'medium' NOT NULL, "status" text DEFAULT 'active' NOT NULL, "schema_version" text DEFAULT '1.0' NOT NULL, "connector_version" text DEFAULT '1.0.0' NOT NULL, "config" jsonb DEFAULT '{}'::jsonb NOT NULL, "last_run_at" timestamp with time zone, "last_success_at" timestamp with time zone, "next_run_at" timestamp with time zone, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "costs" ( "id" text PRIMARY KEY NOT NULL, "occurred_at" timestamp with time zone NOT NULL, "kind" text NOT NULL, "provider" text, "connector_id" text, "category_slug" text, "endpoint" text, "user_id" text, "units" real DEFAULT 1 NOT NULL, "credits" real DEFAULT 0 NOT NULL, "usd_est" real DEFAULT 0 NOT NULL, "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL ); --> statement-breakpoint CREATE TABLE "crawl_state" ( "url_hash" text PRIMARY KEY NOT NULL, "connector_id" text NOT NULL, "url" text NOT NULL, "etag" text, "last_modified" text, "content_hash" text, "last_fetched_at" timestamp with time zone, "last_changed_at" timestamp with time zone, "fetch_count" integer DEFAULT 0 NOT NULL, "change_count" integer DEFAULT 0 NOT NULL, "change_interval_hours" real, "next_fetch_at" timestamp with time zone, "last_status" integer, "failures" integer DEFAULT 0 NOT NULL ); --> statement-breakpoint CREATE TABLE "sources" ( "id" text PRIMARY KEY NOT NULL, "name" text NOT NULL, "source_type" text NOT NULL, "url" text, "countries" text[] DEFAULT '{}'::text[] NOT NULL, "languages" text[] DEFAULT '{}'::text[] NOT NULL, "currencies" text[] DEFAULT '{}'::text[] NOT NULL, "trust_score" numeric(8, 6) DEFAULT 0.5 NOT NULL, "trust_factors" jsonb DEFAULT '{}'::jsonb NOT NULL, "attribution_required" boolean DEFAULT true NOT NULL, "terms_url" text, "active" boolean DEFAULT true NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "audit_log" ( "id" text PRIMARY KEY NOT NULL, "entity_type" text NOT NULL, "entity_id" text NOT NULL, "action" text NOT NULL, "reason" text NOT NULL, "actor" text DEFAULT 'system' NOT NULL, "details" jsonb DEFAULT '{}'::jsonb NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "events" ( "id" text PRIMARY KEY NOT NULL, "type" text NOT NULL, "entity_type" text, "entity_id" text, "payload" jsonb DEFAULT '{}'::jsonb NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "normalized_records" ( "id" text PRIMARY KEY NOT NULL, "raw_record_id" text NOT NULL, "connector_id" text NOT NULL, "source_id" text NOT NULL, "kind" text NOT NULL, "payload" jsonb NOT NULL, "asset_id" text, "variant_id" text, "match_method" text, "match_confidence" numeric(8, 6), "status" text DEFAULT 'pending' NOT NULL, "reject_reason" text, "target_id" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "processed_at" timestamp with time zone ); --> statement-breakpoint CREATE TABLE "raw_records" ( "id" text PRIMARY KEY NOT NULL, "connector_id" text NOT NULL, "source_id" text NOT NULL, "run_id" text, "engine" text NOT NULL, "url" text NOT NULL, "external_id" text, "kind" text NOT NULL, "fetched_at" timestamp with time zone NOT NULL, "content_hash" text NOT NULL, "http_status" integer, "payload" jsonb NOT NULL, "snapshot_ref" text, "parser_version" text NOT NULL, "connector_version" text NOT NULL, "processed_at" timestamp with time zone, "process_error" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "asset_embeddings" ( "asset_id" text PRIMARY KEY NOT NULL, "model" text NOT NULL, "embedding" vector(1536) NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "asset_stats" ( "asset_id" text PRIMARY KEY NOT NULL, "riv_usd" numeric(18, 4), "riv_low_usd" numeric(18, 4), "riv_high_usd" numeric(18, 4), "riv_confidence" numeric(8, 6), "riv_sample_size" integer DEFAULT 0 NOT NULL, "riv_variant_id" text, "latest_sale_usd" numeric(18, 4), "latest_sale_at" timestamp with time zone, "change_1d" numeric(8, 6), "change_7d" numeric(8, 6), "change_30d" numeric(8, 6), "change_90d" numeric(8, 6), "change_1y" numeric(8, 6), "ath_usd" numeric(18, 4), "ath_at" timestamp with time zone, "atl_usd" numeric(18, 4), "atl_at" timestamp with time zone, "sales_count" integer DEFAULT 0 NOT NULL, "sales_30d" integer DEFAULT 0 NOT NULL, "sales_1y" integer DEFAULT 0 NOT NULL, "volume_30d_usd" numeric(18, 4), "active_listings" integer DEFAULT 0 NOT NULL, "min_ask_usd" numeric(18, 4), "observations_count" integer DEFAULT 0 NOT NULL, "sources_count" integer DEFAULT 0 NOT NULL, "liquidity_score" real, "rarity_score" real, "momentum_7d" real, "momentum_30d" real, "momentum_90d" real, "momentum_1y" real, "trending_score" real, "value_opportunity" real, "data_quality" real, "watchers" integer DEFAULT 0 NOT NULL, "views_30d" integer DEFAULT 0 NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "asset_variants" ( "id" text PRIMARY KEY NOT NULL, "asset_id" text NOT NULL, "variant_key" text NOT NULL, "grader" text, "grade" text, "qualifier" text, "condition" text, "completeness" text, "size_label" text, "label" text NOT NULL, "is_default" boolean DEFAULT false NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "assets" ( "id" text PRIMARY KEY NOT NULL, "slug" text NOT NULL, "canonical_key" text NOT NULL, "category_slug" text NOT NULL, "subcategory_slug" text, "family_slug" text NOT NULL, "franchise" text, "brand" text, "series" text, "set_slug" text, "set_name" text, "set_code" text, "name" text NOT NULL, "title" text NOT NULL, "model" text, "reference" text, "number" text, "year" integer, "edition" text, "variant" text, "language" text, "region" text, "country" text, "material" text, "size" text, "color" text, "rarity" text, "production_quantity" integer, "original_msrp" numeric(18, 4), "original_msrp_currency" text, "release_date" text, "description" text, "hero_image_url" text, "identifiers" jsonb DEFAULT '{}'::jsonb NOT NULL, "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL, "merged_from" text[] DEFAULT '{}'::text[] NOT NULL, "data_quality" real DEFAULT 0 NOT NULL, "verified" boolean DEFAULT false NOT NULL, "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, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "grade_premiums" ( "id" text PRIMARY KEY NOT NULL, "category_slug" text NOT NULL, "grader" text NOT NULL, "grade" text NOT NULL, "market_multiplier" real NOT NULL, "sample_size" integer NOT NULL, "computed_at" timestamp with time zone NOT NULL ); --> statement-breakpoint CREATE TABLE "images" ( "id" text PRIMARY KEY NOT NULL, "asset_id" text, "listing_id" text, "sale_id" text, "source_id" text, "url" text NOT NULL, "role" text DEFAULT 'gallery' NOT NULL, "width" integer, "height" integer, "phash" text, "embedding" vector(512), "attribution" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "population_reports" ( "id" text PRIMARY KEY NOT NULL, "asset_id" text NOT NULL, "grader" text NOT NULL, "source_id" text NOT NULL, "source_url" text, "report_date" text NOT NULL, "total" integer NOT NULL, "by_grade" jsonb NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "variant_stats" ( "variant_id" text PRIMARY KEY NOT NULL, "asset_id" text NOT NULL, "riv_usd" numeric(18, 4), "riv_low_usd" numeric(18, 4), "riv_high_usd" numeric(18, 4), "riv_confidence" numeric(8, 6), "riv_sample_size" integer DEFAULT 0 NOT NULL, "latest_sale_usd" numeric(18, 4), "latest_sale_at" timestamp with time zone, "change_30d" numeric(8, 6), "change_1y" numeric(8, 6), "sales_count" integer DEFAULT 0 NOT NULL, "sales_30d" integer DEFAULT 0 NOT NULL, "active_listings" integer DEFAULT 0 NOT NULL, "min_ask_usd" numeric(18, 4), "liquidity_score" real, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "auction_lots" ( "id" text PRIMARY KEY NOT NULL, "auction_id" text NOT NULL, "asset_id" text, "variant_id" text, "source_id" text NOT NULL, "lot_number" text, "title" text NOT NULL, "url" text NOT NULL, "estimate_low" numeric(18, 4), "estimate_high" numeric(18, 4), "current_bid" numeric(18, 4), "hammer_price" numeric(18, 4), "currency" text, "bid_count" integer, "starts_at" timestamp with time zone, "ends_at" timestamp with time zone, "status" text DEFAULT 'upcoming' NOT NULL, "image_urls" jsonb DEFAULT '[]'::jsonb NOT NULL, "grader" text, "grade" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "auctions" ( "id" text PRIMARY KEY NOT NULL, "source_id" text NOT NULL, "auction_house" text NOT NULL, "name" text NOT NULL, "url" text NOT NULL, "starts_at" timestamp with time zone, "ends_at" timestamp with time zone, "location" text, "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL, "lot_count" integer, "status" text DEFAULT 'upcoming' NOT NULL, "currency" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "cross_listing_groups" ( "id" text PRIMARY KEY NOT NULL, "asset_id" text, "signals" jsonb DEFAULT '{}'::jsonb NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "fx_rates" ( "date" date NOT NULL, "base" text NOT NULL, "quote" text NOT NULL, "rate" real NOT NULL, "source" text DEFAULT 'ecb' NOT NULL ); --> statement-breakpoint CREATE TABLE "listing_events" ( "id" text PRIMARY KEY NOT NULL, "listing_id" text NOT NULL, "event_type" text NOT NULL, "old_price" numeric(18, 4), "new_price" numeric(18, 4), "currency" text, "occurred_at" timestamp with time zone NOT NULL ); --> statement-breakpoint CREATE TABLE "listings" ( "id" text PRIMARY KEY NOT NULL, "asset_id" text NOT NULL, "variant_id" text, "source_id" text NOT NULL, "connector_id" text NOT NULL, "raw_record_id" text, "source_url" text NOT NULL, "external_id" text NOT NULL, "listing_type" text DEFAULT 'unknown' NOT NULL, "price" numeric(18, 4), "currency" text, "price_usd" numeric(18, 4), "seller" text, "seller_reputation" text, "location" text, "shipping_cost" numeric(18, 4), "quantity" integer, "condition" text, "grader" text, "grade" text, "certification_number" text, "image_urls" jsonb DEFAULT '[]'::jsonb NOT NULL, "raw_title" text NOT NULL, "description" text, "listed_at" timestamp with time zone, "ends_at" timestamp with time zone, "availability" text DEFAULT 'available' NOT NULL, "bid_count" integer, "first_seen_at" timestamp with time zone NOT NULL, "last_seen_at" timestamp with time zone NOT NULL, "price_changed_at" timestamp with time zone, "cross_listing_group_id" text, "confidence" numeric(8, 6) DEFAULT 0.8 NOT NULL, "data_quality" real DEFAULT 0 NOT NULL, "flags" text[] DEFAULT '{}'::text[] NOT NULL, "discount_to_riv" numeric(8, 6), "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "news" ( "id" text PRIMARY KEY NOT NULL, "source_id" text NOT NULL, "url" text NOT NULL, "title" text NOT NULL, "summary" text, "ai_summary" text, "published_at" timestamp with time zone, "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL, "news_type" text, "image_url" text, "fetched_at" timestamp with time zone NOT NULL ); --> statement-breakpoint CREATE TABLE "price_observations" ( "id" text PRIMARY KEY NOT NULL, "asset_id" text NOT NULL, "variant_id" text, "source_id" text NOT NULL, "connector_id" text NOT NULL, "raw_record_id" text, "source_url" text NOT NULL, "price_kind" text NOT NULL, "price" numeric(18, 4) NOT NULL, "currency" text NOT NULL, "price_usd" numeric(18, 4) NOT NULL, "observation_date" date NOT NULL, "sample_size" integer, "dedupe_key" text NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "sales" ( "id" text PRIMARY KEY NOT NULL, "asset_id" text NOT NULL, "variant_id" text, "source_id" text NOT NULL, "connector_id" text NOT NULL, "raw_record_id" text, "normalized_record_id" text, "source_url" text NOT NULL, "external_id" text, "sale_type" text DEFAULT 'unknown' NOT NULL, "sale_date" timestamp with time zone NOT NULL, "price" numeric(18, 4) NOT NULL, "currency" text NOT NULL, "price_usd" numeric(18, 4) NOT NULL, "fx_rate" real, "fx_date" date, "buyer_premium_included" boolean, "quantity" integer DEFAULT 1 NOT NULL, "is_bundle" boolean DEFAULT false NOT NULL, "condition" text, "grader" text, "grade" text, "certification_number" text, "location" text, "auction_house" text, "lot_number" text, "image_urls" jsonb DEFAULT '[]'::jsonb NOT NULL, "raw_title" text NOT NULL, "confidence" numeric(8, 6) DEFAULT 0.8 NOT NULL, "data_quality" real DEFAULT 0 NOT NULL, "status" text DEFAULT 'valid' NOT NULL, "flags" text[] DEFAULT '{}'::text[] NOT NULL, "dedupe_key" text NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "benchmarks" ( "ticker" text NOT NULL, "date" date NOT NULL, "value" real NOT NULL, "source" text NOT NULL, CONSTRAINT "benchmarks_ticker_date_pk" PRIMARY KEY("ticker","date") ); --> statement-breakpoint CREATE TABLE "category_snapshots" ( "category_slug" text NOT NULL, "date" date NOT NULL, "index_value" real, "tracked_assets" integer DEFAULT 0 NOT NULL, "assets_with_valuation" integer DEFAULT 0 NOT NULL, "sales" integer DEFAULT 0 NOT NULL, "volume_usd" numeric(18, 4), "median_sale_usd" numeric(18, 4), "active_listings" integer DEFAULT 0 NOT NULL, "market_cap_est_usd" numeric(18, 4), "liquidity_score" real, "change_1d" numeric(8, 6), "change_7d" numeric(8, 6), "change_30d" numeric(8, 6), "change_1y" numeric(8, 6), CONSTRAINT "category_snapshots_category_slug_date_pk" PRIMARY KEY("category_slug","date") ); --> statement-breakpoint CREATE TABLE "correlations" ( "a" text NOT NULL, "b" text NOT NULL, "window_days" integer NOT NULL, "coefficient" real NOT NULL, "observations" integer NOT NULL, "computed_at" timestamp with time zone NOT NULL, CONSTRAINT "correlations_a_b_window_days_pk" PRIMARY KEY("a","b","window_days") ); --> statement-breakpoint CREATE TABLE "index_constituents" ( "index_id" text NOT NULL, "asset_id" text NOT NULL, "variant_id" text DEFAULT '' NOT NULL, "weight" real DEFAULT 1 NOT NULL, "added_at" date NOT NULL, "removed_at" date, "reason" text, CONSTRAINT "index_constituents_index_id_asset_id_variant_id_added_at_pk" PRIMARY KEY("index_id","asset_id","variant_id","added_at") ); --> statement-breakpoint CREATE TABLE "index_values" ( "index_id" text NOT NULL, "date" date NOT NULL, "value" real NOT NULL, "constituents_count" integer DEFAULT 0 NOT NULL, "transactions" integer DEFAULT 0 NOT NULL, "volume_usd" numeric(18, 4), "median_sale_usd" numeric(18, 4), "avg_sale_usd" numeric(18, 4), "market_cap_est_usd" numeric(18, 4), "market_cap_confidence" text, "liquidity_score" real, "momentum" real, "breadth" integer, "tracked_assets" integer, "coverage" real, CONSTRAINT "index_values_index_id_date_pk" PRIMARY KEY("index_id","date") ); --> statement-breakpoint CREATE TABLE "indices" ( "id" text PRIMARY KEY NOT NULL, "ticker" text NOT NULL, "name" text NOT NULL, "description" text, "category_slugs" text[] DEFAULT '{}'::text[] NOT NULL, "family_slugs" text[] DEFAULT '{}'::text[] NOT NULL, "parent_ticker" text, "methodology" text DEFAULT 'chain_linked_equal_weight_v1' NOT NULL, "weighting" text DEFAULT 'equal' NOT NULL, "base_date" date NOT NULL, "base_value" real DEFAULT 1000 NOT NULL, "min_constituents" integer DEFAULT 10 NOT NULL, "active" boolean DEFAULT true NOT NULL, "is_flagship" boolean DEFAULT false NOT NULL, "color" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, CONSTRAINT "indices_ticker_unique" UNIQUE("ticker") ); --> statement-breakpoint CREATE TABLE "price_snapshots" ( "asset_id" text NOT NULL, "variant_id" text DEFAULT '' NOT NULL, "date" date NOT NULL, "riv_usd" numeric(18, 4), "latest_sale_usd" numeric(18, 4), "median_usd" numeric(18, 4), "sales_count" integer DEFAULT 0 NOT NULL, "volume_usd" numeric(18, 4), "listings_count" integer DEFAULT 0 NOT NULL, "min_ask_usd" numeric(18, 4), "observation_usd" numeric(18, 4), CONSTRAINT "price_snapshots_asset_id_variant_id_date_pk" PRIMARY KEY("asset_id","variant_id","date") ); --> statement-breakpoint CREATE TABLE "radar_findings" ( "id" text PRIMARY KEY NOT NULL, "asset_id" text NOT NULL, "kind" text NOT NULL, "score" real NOT NULL, "evidence" jsonb DEFAULT '{}'::jsonb NOT NULL, "entity_type" text, "entity_id" text, "detected_at" timestamp with time zone NOT NULL, "expires_at" timestamp with time zone ); --> statement-breakpoint CREATE TABLE "valuations" ( "id" text PRIMARY KEY NOT NULL, "asset_id" text NOT NULL, "variant_id" text, "computed_at" timestamp with time zone NOT NULL, "riv_usd" numeric(18, 4), "low_usd" numeric(18, 4), "high_usd" numeric(18, 4), "confidence" numeric(8, 6) DEFAULT 0 NOT NULL, "confidence_label" text DEFAULT 'insufficient' NOT NULL, "sample_size" integer DEFAULT 0 NOT NULL, "window_days" integer DEFAULT 365 NOT NULL, "methods" jsonb DEFAULT '{}'::jsonb NOT NULL, "sales_used" text[] DEFAULT '{}'::text[] NOT NULL, "observations_used" integer DEFAULT 0 NOT NULL, "method" text DEFAULT 'ensemble_v1' NOT NULL, "notes" text[] DEFAULT '{}'::text[] NOT NULL ); --> statement-breakpoint CREATE TABLE "alert_events" ( "id" text PRIMARY KEY NOT NULL, "alert_id" text NOT NULL, "user_id" text NOT NULL, "message" text NOT NULL, "payload" jsonb DEFAULT '{}'::jsonb NOT NULL, "read_at" timestamp with time zone, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "alerts" ( "id" text PRIMARY KEY NOT NULL, "user_id" text NOT NULL, "alert_type" text NOT NULL, "target_type" text NOT NULL, "target_id" text NOT NULL, "threshold" numeric(18, 4), "currency" text, "params" jsonb DEFAULT '{}'::jsonb NOT NULL, "channel" text DEFAULT 'inapp' NOT NULL, "active" boolean DEFAULT true NOT NULL, "last_triggered_at" timestamp with time zone, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "api_keys" ( "id" text PRIMARY KEY NOT NULL, "user_id" text, "name" text NOT NULL, "prefix" text NOT NULL, "key_hash" text NOT NULL, "tier" text DEFAULT 'free' NOT NULL, "rate_limit_per_minute" integer DEFAULT 60 NOT NULL, "daily_quota" integer DEFAULT 1000 NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "last_used_at" timestamp with time zone, "revoked_at" timestamp with time zone, CONSTRAINT "api_keys_key_hash_unique" UNIQUE("key_hash") ); --> statement-breakpoint CREATE TABLE "api_usage" ( "key_id" text NOT NULL, "date" date NOT NULL, "endpoint" text NOT NULL, "count" integer DEFAULT 0 NOT NULL, "latency_ms_avg" real, CONSTRAINT "api_usage_key_id_date_endpoint_pk" PRIMARY KEY("key_id","date","endpoint") ); --> statement-breakpoint CREATE TABLE "asset_views" ( "asset_id" text NOT NULL, "date" date NOT NULL, "views" integer DEFAULT 0 NOT NULL, CONSTRAINT "asset_views_asset_id_date_pk" PRIMARY KEY("asset_id","date") ); --> statement-breakpoint CREATE TABLE "collection_items" ( "id" text PRIMARY KEY NOT NULL, "collection_id" text NOT NULL, "asset_id" text NOT NULL, "variant_id" text, "quantity" integer DEFAULT 1 NOT NULL, "acquired_at" date, "purchase_price" numeric(18, 4), "purchase_currency" text, "purchase_price_usd" numeric(18, 4), "source" text, "grader" text, "grade" text, "certification_number" text, "serial" text, "photos" jsonb DEFAULT '[]'::jsonb NOT NULL, "notes" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "collection_snapshots" ( "collection_id" text NOT NULL, "date" date NOT NULL, "value_usd" numeric(18, 4) NOT NULL, "cost_basis_usd" numeric(18, 4) NOT NULL, "items" integer NOT NULL, CONSTRAINT "collection_snapshots_collection_id_date_pk" PRIMARY KEY("collection_id","date") ); --> statement-breakpoint CREATE TABLE "collections" ( "id" text PRIMARY KEY NOT NULL, "user_id" text NOT NULL, "name" text NOT NULL, "description" text, "is_public" boolean DEFAULT false NOT NULL, "public_slug" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "search_log" ( "id" text PRIMARY KEY NOT NULL, "query" text NOT NULL, "normalized" text NOT NULL, "results" integer NOT NULL, "user_id" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "sessions" ( "id" text PRIMARY KEY NOT NULL, "user_id" text NOT NULL, "expires_at" timestamp with time zone NOT NULL, "user_agent" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "users" ( "id" text PRIMARY KEY NOT NULL, "email" text NOT NULL, "email_verified_at" timestamp with time zone, "password_hash" text, "name" text, "role" text DEFAULT 'user' NOT NULL, "display_currency" text DEFAULT 'USD' NOT NULL, "providers" jsonb DEFAULT '[]'::jsonb NOT NULL, "preferences" jsonb DEFAULT '{}'::jsonb NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "last_login_at" timestamp with time zone, CONSTRAINT "users_email_unique" UNIQUE("email") ); --> statement-breakpoint CREATE TABLE "watchlist_items" ( "id" text PRIMARY KEY NOT NULL, "watchlist_id" text NOT NULL, "target_type" text NOT NULL, "target_id" text NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "watchlists" ( "id" text PRIMARY KEY NOT NULL, "user_id" text NOT NULL, "name" text DEFAULT 'Watchlist' NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE INDEX "brands_name_idx" ON "brands" USING btree ("name");--> statement-breakpoint CREATE INDEX "categories_parent_idx" ON "categories" USING btree ("parent_slug");--> statement-breakpoint CREATE INDEX "categories_family_idx" ON "categories" USING btree ("family_slug");--> statement-breakpoint CREATE INDEX "sets_category_idx" ON "sets" USING btree ("category_slug");--> statement-breakpoint CREATE UNIQUE INDEX "sets_category_code_uq" ON "sets" USING btree ("category_slug","code");--> statement-breakpoint CREATE INDEX "connector_runs_connector_started_idx" ON "connector_runs" USING btree ("connector_id","started_at");--> statement-breakpoint CREATE INDEX "costs_occurred_idx" ON "costs" USING btree ("occurred_at");--> statement-breakpoint CREATE INDEX "costs_connector_idx" ON "costs" USING btree ("connector_id");--> statement-breakpoint CREATE INDEX "crawl_state_next_idx" ON "crawl_state" USING btree ("connector_id","next_fetch_at");--> statement-breakpoint CREATE INDEX "audit_entity_idx" ON "audit_log" USING btree ("entity_type","entity_id");--> statement-breakpoint CREATE INDEX "events_type_created_idx" ON "events" USING btree ("type","created_at");--> statement-breakpoint CREATE INDEX "events_entity_idx" ON "events" USING btree ("entity_type","entity_id");--> statement-breakpoint CREATE INDEX "normalized_records_status_idx" ON "normalized_records" USING btree ("status","created_at");--> statement-breakpoint CREATE INDEX "normalized_records_asset_idx" ON "normalized_records" USING btree ("asset_id");--> statement-breakpoint CREATE UNIQUE INDEX "normalized_records_raw_uq" ON "normalized_records" USING btree ("raw_record_id");--> statement-breakpoint CREATE UNIQUE INDEX "raw_records_connector_hash_uq" ON "raw_records" USING btree ("connector_id","content_hash");--> statement-breakpoint CREATE INDEX "raw_records_connector_fetched_idx" ON "raw_records" USING btree ("connector_id","fetched_at");--> statement-breakpoint CREATE INDEX "raw_records_unprocessed_idx" ON "raw_records" USING btree ("processed_at");--> statement-breakpoint CREATE INDEX "raw_records_external_idx" ON "raw_records" USING btree ("connector_id","external_id");--> statement-breakpoint CREATE INDEX "asset_stats_riv_idx" ON "asset_stats" USING btree ("riv_usd");--> statement-breakpoint CREATE INDEX "asset_stats_trending_idx" ON "asset_stats" USING btree ("trending_score");--> statement-breakpoint CREATE INDEX "asset_stats_liquidity_idx" ON "asset_stats" USING btree ("liquidity_score");--> statement-breakpoint CREATE UNIQUE INDEX "asset_variants_uq" ON "asset_variants" USING btree ("asset_id","variant_key");--> statement-breakpoint CREATE INDEX "asset_variants_asset_idx" ON "asset_variants" USING btree ("asset_id");--> statement-breakpoint CREATE UNIQUE INDEX "assets_canonical_key_uq" ON "assets" USING btree ("canonical_key");--> statement-breakpoint CREATE UNIQUE INDEX "assets_slug_uq" ON "assets" USING btree ("slug");--> statement-breakpoint CREATE INDEX "assets_category_idx" ON "assets" USING btree ("category_slug");--> statement-breakpoint CREATE INDEX "assets_family_idx" ON "assets" USING btree ("family_slug");--> statement-breakpoint CREATE INDEX "assets_set_idx" ON "assets" USING btree ("set_slug");--> statement-breakpoint CREATE INDEX "assets_search_gin" ON "assets" USING gin ("search");--> statement-breakpoint CREATE INDEX "assets_title_trgm" ON "assets" USING gin ("title" gin_trgm_ops);--> statement-breakpoint CREATE INDEX "assets_identifiers_gin" ON "assets" USING gin ("identifiers");--> statement-breakpoint CREATE UNIQUE INDEX "grade_premiums_uq" ON "grade_premiums" USING btree ("category_slug","grader","grade");--> statement-breakpoint CREATE INDEX "images_asset_idx" ON "images" USING btree ("asset_id");--> statement-breakpoint CREATE INDEX "images_phash_idx" ON "images" USING btree ("phash");--> statement-breakpoint CREATE UNIQUE INDEX "images_url_uq" ON "images" USING btree ("url");--> statement-breakpoint CREATE UNIQUE INDEX "population_reports_uq" ON "population_reports" USING btree ("asset_id","grader","report_date");--> statement-breakpoint CREATE UNIQUE INDEX "auction_lots_url_uq" ON "auction_lots" USING btree ("url");--> statement-breakpoint CREATE INDEX "auction_lots_auction_idx" ON "auction_lots" USING btree ("auction_id");--> statement-breakpoint CREATE INDEX "auction_lots_asset_idx" ON "auction_lots" USING btree ("asset_id");--> statement-breakpoint CREATE INDEX "auction_lots_ends_idx" ON "auction_lots" USING btree ("ends_at");--> statement-breakpoint CREATE UNIQUE INDEX "auctions_url_uq" ON "auctions" USING btree ("url");--> statement-breakpoint CREATE INDEX "auctions_ends_idx" ON "auctions" USING btree ("ends_at");--> statement-breakpoint CREATE UNIQUE INDEX "fx_rates_uq" ON "fx_rates" USING btree ("date","base","quote");--> statement-breakpoint CREATE INDEX "listing_events_listing_idx" ON "listing_events" USING btree ("listing_id","occurred_at");--> statement-breakpoint CREATE UNIQUE INDEX "listings_source_external_uq" ON "listings" USING btree ("source_id","external_id");--> statement-breakpoint CREATE INDEX "listings_asset_avail_idx" ON "listings" USING btree ("asset_id","availability");--> statement-breakpoint CREATE INDEX "listings_avail_price_idx" ON "listings" USING btree ("availability","price_usd");--> statement-breakpoint CREATE INDEX "listings_ends_idx" ON "listings" USING btree ("ends_at");--> statement-breakpoint CREATE INDEX "listings_last_seen_idx" ON "listings" USING btree ("last_seen_at");--> statement-breakpoint CREATE UNIQUE INDEX "news_url_uq" ON "news" USING btree ("url");--> statement-breakpoint CREATE INDEX "news_published_idx" ON "news" USING btree ("published_at");--> statement-breakpoint CREATE UNIQUE INDEX "price_observations_dedupe_uq" ON "price_observations" USING btree ("dedupe_key");--> statement-breakpoint CREATE INDEX "price_observations_asset_date_idx" ON "price_observations" USING btree ("asset_id","observation_date");--> statement-breakpoint CREATE UNIQUE INDEX "sales_dedupe_uq" ON "sales" USING btree ("dedupe_key");--> statement-breakpoint CREATE INDEX "sales_asset_date_idx" ON "sales" USING btree ("asset_id","sale_date");--> statement-breakpoint CREATE INDEX "sales_variant_date_idx" ON "sales" USING btree ("variant_id","sale_date");--> statement-breakpoint CREATE INDEX "sales_source_idx" ON "sales" USING btree ("source_id","sale_date");--> statement-breakpoint CREATE INDEX "sales_date_idx" ON "sales" USING btree ("sale_date");--> statement-breakpoint CREATE INDEX "sales_price_idx" ON "sales" USING btree ("price_usd");--> statement-breakpoint CREATE INDEX "index_constituents_asset_idx" ON "index_constituents" USING btree ("asset_id");--> statement-breakpoint CREATE INDEX "price_snapshots_date_idx" ON "price_snapshots" USING btree ("date");--> statement-breakpoint CREATE INDEX "radar_kind_idx" ON "radar_findings" USING btree ("kind","detected_at");--> statement-breakpoint CREATE UNIQUE INDEX "radar_entity_uq" ON "radar_findings" USING btree ("kind","entity_type","entity_id");--> statement-breakpoint CREATE INDEX "valuations_asset_idx" ON "valuations" USING btree ("asset_id","computed_at");--> statement-breakpoint CREATE INDEX "valuations_variant_idx" ON "valuations" USING btree ("variant_id","computed_at");--> statement-breakpoint CREATE INDEX "alert_events_user_idx" ON "alert_events" USING btree ("user_id","created_at");--> statement-breakpoint CREATE INDEX "alerts_user_idx" ON "alerts" USING btree ("user_id");--> statement-breakpoint CREATE INDEX "alerts_target_idx" ON "alerts" USING btree ("target_type","target_id","active");--> statement-breakpoint CREATE INDEX "api_keys_user_idx" ON "api_keys" USING btree ("user_id");--> statement-breakpoint CREATE INDEX "collection_items_collection_idx" ON "collection_items" USING btree ("collection_id");--> statement-breakpoint CREATE INDEX "collection_items_asset_idx" ON "collection_items" USING btree ("asset_id");--> statement-breakpoint CREATE INDEX "collections_user_idx" ON "collections" USING btree ("user_id");--> statement-breakpoint CREATE UNIQUE INDEX "collections_public_slug_uq" ON "collections" USING btree ("public_slug");--> statement-breakpoint CREATE INDEX "search_log_created_idx" ON "search_log" USING btree ("created_at");--> statement-breakpoint CREATE INDEX "search_log_normalized_idx" ON "search_log" USING btree ("normalized");--> statement-breakpoint CREATE INDEX "sessions_user_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint CREATE UNIQUE INDEX "watchlist_items_uq" ON "watchlist_items" USING btree ("watchlist_id","target_type","target_id");--> statement-breakpoint CREATE INDEX "watchlists_user_idx" ON "watchlists" USING btree ("user_id");