Spaces:

hsmm
/

lea-GEO

Sleeping

lea-GEO / db /schema.sql

Initial commit for HF Space

35bdde1 9 months ago

2.77 kB

-- 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);

	-- 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);