kangshifu-board/server/voc-library/schema.sql

PRAGMA foreign_keys=ON;
CREATE TABLE meta(key TEXT PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE batches(id TEXT PRIMARY KEY, archive_name TEXT NOT NULL, archive_sha256 TEXT NOT NULL UNIQUE, imported_at TEXT NOT NULL, rule_version TEXT NOT NULL);
CREATE TABLE assets(path TEXT PRIMARY KEY, sha256 TEXT NOT NULL, byte_size INTEGER NOT NULL CHECK(byte_size>=0));
CREATE TABLE collection_requests(asset_path TEXT PRIMARY KEY REFERENCES assets(path), endpoint_id TEXT NOT NULL, run_key TEXT,
 safe_parameters_json TEXT NOT NULL, collector_status TEXT, http_status INTEGER, upstream_code TEXT, upstream_request_id TEXT,
 started_at_raw TEXT, finished_at_raw TEXT, record_time_raw TEXT);
CREATE TABLE source_records(id TEXT PRIMARY KEY, kind TEXT NOT NULL, asset_path TEXT NOT NULL REFERENCES assets(path), line_number INTEGER NOT NULL,
 payload_sha256 TEXT NOT NULL, payload_json TEXT NOT NULL CHECK(json_valid(payload_json)), UNIQUE(asset_path,line_number));
CREATE TABLE source_links(record_id TEXT NOT NULL REFERENCES source_records(id), role TEXT NOT NULL, original_path TEXT NOT NULL,
 asset_path TEXT REFERENCES assets(path), resolution TEXT NOT NULL CHECK(resolution IN ('matched','missing','ambiguous')), PRIMARY KEY(record_id,role,original_path));
CREATE TABLE brands(code TEXT PRIMARY KEY, name TEXT NOT NULL, aliases_json TEXT NOT NULL);
CREATE TABLE categories(code TEXT PRIMARY KEY, name TEXT NOT NULL, parent_code TEXT REFERENCES categories(code), definition TEXT NOT NULL);
CREATE TABLE products(id TEXT PRIMARY KEY, platform_family TEXT NOT NULL CHECK(platform_family IN ('jd','taobao')), item_id TEXT NOT NULL,
 marketplace TEXT NOT NULL CHECK(marketplace IN ('jd','tmall','taobao_unverified')), marketplace_basis TEXT NOT NULL,
 shop_id TEXT, shop_name TEXT, title TEXT NOT NULL, url TEXT, brand_code TEXT REFERENCES brands(code), category_code TEXT REFERENCES categories(code),
 classification_state TEXT NOT NULL CHECK(classification_state IN ('supported','pending')), brand_basis TEXT NOT NULL, category_basis TEXT NOT NULL,
 source_record_id TEXT NOT NULL REFERENCES source_records(id), source_time_raw TEXT, collected_at_raw TEXT, coverage_type TEXT,
 UNIQUE(platform_family,item_id));
CREATE INDEX idx_products_scope ON products(marketplace,brand_code,category_code,classification_state,id);
CREATE INDEX idx_products_shop ON products(marketplace,shop_id,id);
CREATE TABLE classification_assertions(id TEXT PRIMARY KEY, product_id TEXT NOT NULL REFERENCES products(id), dimension TEXT NOT NULL,
 value TEXT, method TEXT NOT NULL, rule_version TEXT NOT NULL, evidence_record_id TEXT NOT NULL REFERENCES source_records(id), evidence_field TEXT NOT NULL, note TEXT NOT NULL);
CREATE TABLE product_observations(id TEXT PRIMARY KEY, product_id TEXT NOT NULL REFERENCES products(id), source_record_id TEXT NOT NULL REFERENCES source_records(id),
 source_file TEXT, source_time_raw TEXT, collected_at_raw TEXT, query TEXT, acquisition TEXT);
CREATE TABLE specs(id TEXT PRIMARY KEY, product_id TEXT NOT NULL REFERENCES products(id), upstream_sku_id TEXT, spec_text TEXT,
 net_weight_g REAL CHECK(net_weight_g>0), pack_count INTEGER CHECK(pack_count>0), total_weight_g REAL CHECK(total_weight_g>0),
 verification TEXT NOT NULL CHECK(verification IN ('source_attribute','title_candidate','unknown')), source_record_id TEXT NOT NULL REFERENCES source_records(id),
 basis TEXT NOT NULL);
CREATE INDEX idx_specs_product ON specs(product_id,verification,id);
CREATE TABLE prices(id TEXT PRIMARY KEY, product_id TEXT NOT NULL REFERENCES products(id), currency TEXT NOT NULL CHECK(currency='CNY'), amount_minor INTEGER CHECK(amount_minor>0),
 raw_amount TEXT NOT NULL, price_basis TEXT NOT NULL, source_time_raw TEXT, source_time_utc TEXT, source_calendar_date TEXT, collected_at_raw TEXT, collected_at_utc TEXT,
 price_per_100g REAL, unit_price_verification TEXT NOT NULL CHECK(unit_price_verification IN ('verified','unavailable')),
 source_record_id TEXT NOT NULL REFERENCES source_records(id));
CREATE INDEX idx_prices_product_time ON prices(product_id,source_time_utc,id);
CREATE TABLE price_sources(price_id TEXT NOT NULL REFERENCES prices(id), source_record_id TEXT NOT NULL REFERENCES source_records(id), PRIMARY KEY(price_id,source_record_id));
CREATE TABLE reviews(id TEXT PRIMARY KEY, platform_family TEXT NOT NULL, item_id TEXT NOT NULL, review_id TEXT NOT NULL, id_method TEXT NOT NULL,
 product_id TEXT REFERENCES products(id), marketplace_claim TEXT NOT NULL, content TEXT NOT NULL, append_content_json TEXT NOT NULL,
 rating_raw TEXT, rating_scale TEXT, review_date_raw TEXT, review_date_utc TEXT, review_calendar_date TEXT, purchased_sku_text TEXT,
 is_default INTEGER CHECK(is_default IN (0,1)), identity_state TEXT NOT NULL CHECK(identity_state IN ('matched','unverified','mismatch','orphan')),
 category_state TEXT NOT NULL CHECK(category_state IN ('consistent','listing_only','conflict','unknown')), source_time_raw TEXT, collected_at_raw TEXT,
 source_record_id TEXT NOT NULL REFERENCES source_records(id), UNIQUE(platform_family,item_id,review_id));
CREATE INDEX idx_reviews_product ON reviews(product_id,is_default,identity_state,category_state,id);
CREATE INDEX idx_reviews_time ON reviews(review_date_utc,id);
CREATE TABLE social_posts(id TEXT PRIMARY KEY, platform TEXT NOT NULL, post_id TEXT NOT NULL, title TEXT, content TEXT, url TEXT,
 content_scope TEXT NOT NULL, published_at_raw TEXT, published_at_utc TEXT, collected_at_raw TEXT, likes INTEGER, comment_count INTEGER, collects INTEGER, shares INTEGER,
 fulltext_available INTEGER NOT NULL CHECK(fulltext_available IN (0,1)), source_record_id TEXT NOT NULL REFERENCES source_records(id), UNIQUE(platform,post_id));
CREATE INDEX idx_social_scope ON social_posts(platform,content_scope,id);
CREATE TABLE social_brand_mentions(post_id TEXT NOT NULL REFERENCES social_posts(id), brand_code TEXT NOT NULL REFERENCES brands(code), basis TEXT NOT NULL,
 PRIMARY KEY(post_id,brand_code));
CREATE TABLE policy_documents(id TEXT PRIMARY KEY, title TEXT NOT NULL, declared_status TEXT NOT NULL, status_as_of TEXT NOT NULL,
 issued_date TEXT, effective_date TEXT, source_url TEXT, expected_sha256 TEXT, attachment_hash_state TEXT NOT NULL, hash_basis TEXT, coverage TEXT, full_text TEXT,
 source_record_id TEXT NOT NULL REFERENCES source_records(id));
CREATE INDEX idx_policy_status ON policy_documents(declared_status,id);
CREATE TABLE policy_clauses(id TEXT PRIMARY KEY, document_id TEXT NOT NULL REFERENCES policy_documents(id), clause TEXT NOT NULL, pdf_page TEXT,
 excerpt TEXT NOT NULL, excerpt_type TEXT NOT NULL CHECK(excerpt_type IN ('paraphrase_or_transcription','unverified_excerpt')), application_note TEXT,
 excerpt_method TEXT, source_record_id TEXT NOT NULL REFERENCES source_records(id));
CREATE INDEX idx_clauses_document ON policy_clauses(document_id,id);
CREATE TABLE ranking_signals(id TEXT PRIMARY KEY, product_id TEXT REFERENCES products(id), rank_id TEXT, rank_number INTEGER, title TEXT, scope TEXT NOT NULL,
 source_time_raw TEXT, source_record_id TEXT NOT NULL REFERENCES source_records(id));
CREATE TABLE coverage_observations(id TEXT PRIMARY KEY, product_id TEXT REFERENCES products(id), status TEXT, available_reviews INTEGER, reported_total TEXT,
 historical_total_complete INTEGER NOT NULL CHECK(historical_total_complete IN (0,1)), source_record_id TEXT NOT NULL REFERENCES source_records(id));
CREATE TABLE quality_issues(id TEXT PRIMARY KEY, entity_type TEXT NOT NULL, entity_id TEXT NOT NULL, code TEXT NOT NULL, detail TEXT NOT NULL);
CREATE INDEX idx_issues_entity ON quality_issues(entity_type,entity_id,code);