File size: 2,770 Bytes
35bdde1 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 |
-- Minimal schema for GEO runs/tasks/evidence/mentions + brand mapping.
-- Dialect: PostgreSQL (recommended). Adjust types if using MySQL.
create table if not exists dim_brand_pairs (
version text not null,
effective_at timestamptz not null default now(),
main_brand text not null,
competitor_brand text not null,
industry text not null,
priority integer not null default 0,
primary key (version, main_brand)
);
create table if not exists runs (
run_id text primary key,
trigger text not null, -- scheduled | manual
schedule_id text,
config_version text not null,
config_snapshot_url text,
params_json jsonb not null default '{}'::jsonb,
started_at timestamptz not null default now(),
finished_at timestamptz
);
create table if not exists questions (
question_id text not null,
run_id text not null references runs(run_id) on delete cascade,
question_text text not null,
entity_set_json jsonb not null default '[]'::jsonb,
primary key (run_id, question_id)
);
create table if not exists tasks (
task_id text primary key,
run_id text not null references runs(run_id) on delete cascade,
question_id text not null,
site text not null,
profile_id text not null,
status text not null, -- running | success | failed | needs_human
reason text,
handoff_url text,
started_at timestamptz not null default now(),
finished_at timestamptz
);
create index if not exists tasks_run_site_status_idx on tasks (run_id, site, status);
create table if not exists task_artifacts (
task_id text primary key references tasks(task_id) on delete cascade,
answer_text text not null,
citations_json jsonb not null default '[]'::jsonb,
screenshots_json jsonb not null default '[]'::jsonb,
raw_html_url text,
log_url text
);
create table if not exists mentions (
task_id text not null references tasks(task_id) on delete cascade,
entity_name text not null,
entity_type text not null, -- main | competitor
recommended boolean not null,
rank integer,
first_index integer,
citations_json jsonb not null default '[]'::jsonb,
primary key (task_id, entity_name)
);
create index if not exists mentions_entity_type_idx on mentions (entity_name, entity_type);
-- Optional: daily pre-aggregation for dashboards.
create table if not exists fact_geo_daily (
date date not null,
site text not null,
main_brand text not null,
competitor_brand text not null,
recommended_rate double precision not null,
top1_share double precision not null,
avg_rank double precision,
win_rate double precision,
absent_vs_competitor_rate double precision,
sample_n integer not null,
primary key (date, site, main_brand)
);
create index if not exists fact_geo_daily_site_date_idx on fact_geo_daily (site, date);
|