root
674709c86b
Drop tenant scoping on site_domain_mappings now that the citation query resolves mappings without tenant filtering. Add a unique index on registrable_domain plus an updated_at column, and switch the dev-seed upsert to use ON CONFLICT (registrable_domain). Adds a SiteDomainMapping type to ops/domain for the upcoming console management UI.
246 lines
11 KiB
SQL
246 lines
11 KiB
SQL
CREATE TABLE monitoring_question_config_snapshots (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
brand_id BIGINT NOT NULL,
|
|
question_id BIGINT NOT NULL,
|
|
keyword_id BIGINT,
|
|
question_hash BYTEA NOT NULL,
|
|
question_text_snapshot TEXT NOT NULL,
|
|
monitor_enabled BOOLEAN NOT NULL DEFAULT true,
|
|
superseded_at TIMESTAMPTZ,
|
|
deleted_at TIMESTAMPTZ,
|
|
projected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uk_monitoring_question_snapshot UNIQUE (tenant_id, question_id, question_hash)
|
|
);
|
|
|
|
CREATE INDEX idx_monitoring_question_snapshot_active
|
|
ON monitoring_question_config_snapshots(tenant_id, brand_id, monitor_enabled, deleted_at, superseded_at);
|
|
|
|
CREATE TABLE monitoring_platform_access_snapshots (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
client_id UUID NOT NULL,
|
|
ai_platform_id VARCHAR(30) NOT NULL,
|
|
business_date DATE NOT NULL,
|
|
access_status VARCHAR(20) NOT NULL,
|
|
connected BOOLEAN NOT NULL DEFAULT false,
|
|
accessible BOOLEAN NOT NULL DEFAULT false,
|
|
detected_at TIMESTAMPTZ NOT NULL,
|
|
reason_code VARCHAR(50),
|
|
reason_message TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uk_platform_access_snapshot UNIQUE (tenant_id, client_id, ai_platform_id, business_date)
|
|
);
|
|
|
|
CREATE INDEX idx_platform_access_snapshot_lookup
|
|
ON monitoring_platform_access_snapshots(tenant_id, business_date, ai_platform_id, access_status);
|
|
|
|
CREATE TABLE monitoring_collect_tasks (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
brand_id BIGINT NOT NULL,
|
|
question_id BIGINT NOT NULL,
|
|
question_hash BYTEA NOT NULL,
|
|
ai_platform_id VARCHAR(30) NOT NULL,
|
|
collector_type VARCHAR(20) NOT NULL DEFAULT 'desktop',
|
|
trigger_source VARCHAR(20) NOT NULL DEFAULT 'automatic',
|
|
run_mode VARCHAR(30) NOT NULL DEFAULT 'desktop_standard',
|
|
business_date DATE NOT NULL,
|
|
planned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
lease_token_hash VARCHAR(128),
|
|
leased_to_executor VARCHAR(100),
|
|
leased_at TIMESTAMPTZ,
|
|
lease_expires_at TIMESTAMPTZ,
|
|
callback_received_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
skip_reason VARCHAR(50),
|
|
request_payload_json JSONB,
|
|
error_message TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uk_collect_task_idempotent
|
|
UNIQUE (tenant_id, brand_id, question_id, ai_platform_id, collector_type, run_mode, business_date)
|
|
);
|
|
|
|
CREATE INDEX idx_collect_tasks_lease
|
|
ON monitoring_collect_tasks(tenant_id, status, business_date, lease_expires_at);
|
|
|
|
CREATE TABLE question_monitor_runs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
brand_id BIGINT NOT NULL,
|
|
question_id BIGINT NOT NULL,
|
|
question_text_snapshot TEXT NOT NULL,
|
|
question_hash BYTEA NOT NULL,
|
|
ai_platform_id VARCHAR(30) NOT NULL,
|
|
collector_type VARCHAR(20) NOT NULL DEFAULT 'desktop',
|
|
trigger_source VARCHAR(20) NOT NULL DEFAULT 'automatic',
|
|
run_mode VARCHAR(30) NOT NULL DEFAULT 'desktop_standard',
|
|
business_date DATE NOT NULL,
|
|
provider_model VARCHAR(100),
|
|
provider_request_id VARCHAR(100),
|
|
request_id VARCHAR(100),
|
|
raw_answer_text TEXT,
|
|
raw_answer_storage_key TEXT,
|
|
raw_response_json JSONB,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'succeeded',
|
|
completed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uk_monitor_run_idempotent
|
|
UNIQUE (tenant_id, brand_id, question_id, ai_platform_id, collector_type, run_mode, business_date)
|
|
);
|
|
|
|
CREATE INDEX idx_monitor_runs_query
|
|
ON question_monitor_runs(tenant_id, business_date, ai_platform_id, brand_id);
|
|
|
|
CREATE TABLE question_monitor_parse_results (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
run_id BIGINT NOT NULL REFERENCES question_monitor_runs(id) ON DELETE CASCADE,
|
|
brand_id BIGINT NOT NULL,
|
|
question_id BIGINT NOT NULL,
|
|
question_hash BYTEA NOT NULL,
|
|
ai_platform_id VARCHAR(30) NOT NULL,
|
|
business_date DATE NOT NULL,
|
|
brand_mentioned BOOLEAN,
|
|
brand_mention_position VARCHAR(20),
|
|
first_recommended BOOLEAN,
|
|
sentiment_label VARCHAR(20),
|
|
matched_brand_terms JSONB,
|
|
parse_version VARCHAR(40) NOT NULL DEFAULT 'v1',
|
|
parse_status VARCHAR(20) NOT NULL DEFAULT 'succeeded',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uk_question_parse_run UNIQUE (tenant_id, run_id)
|
|
);
|
|
|
|
CREATE INDEX idx_question_parse_lookup
|
|
ON question_monitor_parse_results(tenant_id, business_date, brand_id, question_id, ai_platform_id);
|
|
|
|
CREATE TABLE monitoring_citation_facts (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
run_id BIGINT NOT NULL REFERENCES question_monitor_runs(id) ON DELETE CASCADE,
|
|
brand_id BIGINT NOT NULL,
|
|
ai_platform_id VARCHAR(30) NOT NULL,
|
|
business_date DATE NOT NULL,
|
|
cited_url TEXT NOT NULL,
|
|
cited_title TEXT,
|
|
normalized_url TEXT NOT NULL,
|
|
host VARCHAR(255) NOT NULL,
|
|
registrable_domain VARCHAR(255) NOT NULL,
|
|
subdomain VARCHAR(255),
|
|
suffix VARCHAR(50),
|
|
site_key VARCHAR(255) NOT NULL,
|
|
article_id BIGINT,
|
|
publish_record_id BIGINT,
|
|
resolution_status VARCHAR(20) NOT NULL DEFAULT 'resolved',
|
|
resolution_confidence VARCHAR(20) NOT NULL DEFAULT 'high',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_citation_facts_domain
|
|
ON monitoring_citation_facts(tenant_id, business_date, registrable_domain);
|
|
|
|
CREATE INDEX idx_citation_facts_run
|
|
ON monitoring_citation_facts(run_id);
|
|
|
|
CREATE TABLE site_domain_mappings (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
registrable_domain VARCHAR(255) NOT NULL,
|
|
site_key VARCHAR(255) NOT NULL,
|
|
site_name VARCHAR(255) NOT NULL,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX uk_site_domain_mappings_domain
|
|
ON site_domain_mappings(registrable_domain);
|
|
|
|
CREATE INDEX idx_site_domain_mappings_active_domain
|
|
ON site_domain_mappings(registrable_domain) WHERE is_active = TRUE;
|
|
|
|
CREATE INDEX idx_site_domain_mappings_created
|
|
ON site_domain_mappings(created_at DESC, id DESC);
|
|
|
|
CREATE TABLE monitoring_article_url_aliases (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
article_id BIGINT NOT NULL,
|
|
publish_record_id BIGINT,
|
|
platform_id VARCHAR(64),
|
|
publish_platform_name_snapshot VARCHAR(100),
|
|
external_article_id VARCHAR(128),
|
|
article_title_snapshot TEXT,
|
|
original_url TEXT NOT NULL,
|
|
normalized_url TEXT NOT NULL,
|
|
last_path_segment VARCHAR(255),
|
|
confidence VARCHAR(20) NOT NULL DEFAULT 'high',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX uk_article_url_alias
|
|
ON monitoring_article_url_aliases(tenant_id, normalized_url);
|
|
|
|
CREATE TABLE monitoring_brand_daily (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
brand_id BIGINT NOT NULL,
|
|
collector_type VARCHAR(20) NOT NULL DEFAULT 'desktop',
|
|
business_date DATE NOT NULL,
|
|
sample_status VARCHAR(20) NOT NULL,
|
|
desired_sample_count INT NOT NULL DEFAULT 0,
|
|
planned_sample_count INT NOT NULL DEFAULT 0,
|
|
actual_sample_count INT NOT NULL DEFAULT 0,
|
|
coverage_rate NUMERIC(8,4),
|
|
confidence_level VARCHAR(20) NOT NULL,
|
|
trigger_source VARCHAR(20),
|
|
snapshot_updated_at TIMESTAMPTZ,
|
|
mentioned_count INT NOT NULL DEFAULT 0,
|
|
top1_mentioned_count INT NOT NULL DEFAULT 0,
|
|
first_recommended_count INT NOT NULL DEFAULT 0,
|
|
positive_mentioned_count INT NOT NULL DEFAULT 0,
|
|
cited_answer_count INT NOT NULL DEFAULT 0,
|
|
mention_rate NUMERIC(8,4),
|
|
top1_mention_rate NUMERIC(8,4),
|
|
first_recommend_rate NUMERIC(8,4),
|
|
positive_mention_rate NUMERIC(8,4),
|
|
citation_rate NUMERIC(8,4),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uk_monitoring_brand_daily
|
|
UNIQUE (tenant_id, brand_id, collector_type, business_date)
|
|
);
|
|
|
|
CREATE INDEX idx_monitoring_brand_daily_lookup
|
|
ON monitoring_brand_daily(tenant_id, brand_id, business_date DESC, collector_type);
|
|
|
|
CREATE TABLE monitoring_brand_platform_daily (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
brand_id BIGINT NOT NULL,
|
|
ai_platform_id VARCHAR(30) NOT NULL,
|
|
collector_type VARCHAR(20) NOT NULL DEFAULT 'desktop',
|
|
business_date DATE NOT NULL,
|
|
platform_sample_status VARCHAR(20) NOT NULL,
|
|
planned_sample_count INT NOT NULL DEFAULT 0,
|
|
actual_sample_count INT NOT NULL DEFAULT 0,
|
|
mention_rate NUMERIC(8,4),
|
|
top1_mention_rate NUMERIC(8,4),
|
|
last_sampled_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uk_monitoring_brand_platform_daily
|
|
UNIQUE (tenant_id, brand_id, ai_platform_id, collector_type, business_date)
|
|
);
|
|
|
|
CREATE INDEX idx_monitoring_brand_platform_daily_lookup
|
|
ON monitoring_brand_platform_daily(tenant_id, brand_id, business_date DESC, ai_platform_id);
|