← back to Rentv Adintel
db/schema.sql
623 lines
-- RENTV Advertiser Intelligence — canonical PostgreSQL schema (spec §11)
-- Postgres is the single source of truth. UUIDs internal; source-native ids preserved.
-- Idempotent: safe to re-run. Uses pgcrypto gen_random_uuid + pg_trgm for search.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- ---------------------------------------------------------------------------
-- Enumerated domains (kept as CHECK-constrained text so seed/migration stay simple)
-- Canonical value lists also live in lib/types.js (the shared JS contract).
-- ---------------------------------------------------------------------------
-- ============================ ORGANIZATIONS ================================
CREATE TABLE IF NOT EXISTS organizations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
legal_name text,
display_name text NOT NULL,
normalized_name text NOT NULL,
aliases jsonb NOT NULL DEFAULT '[]'::jsonb,
domain text,
organization_type text,
advertiser_categories jsonb NOT NULL DEFAULT '[]'::jsonb,
description text,
headquarters_state text,
headquarters_city text,
active_status text NOT NULL DEFAULT 'ACTIVE',
logo_asset_id uuid,
first_seen_at timestamptz,
last_seen_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_org_normname_trgm ON organizations USING gin (normalized_name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_org_domain ON organizations (domain);
CREATE UNIQUE INDEX IF NOT EXISTS uq_org_normname_domain ON organizations (normalized_name, coalesce(domain,''));
-- ============================ PEOPLE / CONTACTS ============================
CREATE TABLE IF NOT EXISTS people (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
full_name text NOT NULL,
normalized_name text NOT NULL,
public_title text,
organization_id uuid REFERENCES organizations(id) ON DELETE SET NULL,
role_category text, -- CMO / VP_MARKETING / MARKETING_DIRECTOR / ...
city text,
state text,
linkedin_url text,
linkedin_discovery_source_id uuid,
linkedin_review_status text NOT NULL DEFAULT 'UNREVIEWED',
last_verified_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_people_org ON people (organization_id);
CREATE TABLE IF NOT EXISTS contact_points (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
person_id uuid REFERENCES people(id) ON DELETE SET NULL,
type text NOT NULL, -- BUSINESS_EMAIL, BUSINESS_PHONE, CONTACT_FORM, WEBSITE, LINKEDIN, OTHER_PUBLIC_PROFILE
value text NOT NULL,
normalized_value text,
source_evidence_id uuid,
explicitly_public boolean NOT NULL DEFAULT false,
verified_at timestamptz,
confidence numeric(4,3) NOT NULL DEFAULT 0.5,
do_not_contact boolean NOT NULL DEFAULT false,
export_allowed boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_cp_org ON contact_points (organization_id);
CREATE INDEX IF NOT EXISTS idx_cp_type ON contact_points (type);
-- ============================ GEOGRAPHY ====================================
CREATE TABLE IF NOT EXISTS markets (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
state text NOT NULL,
region text,
county text,
city text,
metro text,
normalized_name text NOT NULL,
priority integer NOT NULL DEFAULT 999,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_market_norm ON markets (normalized_name);
CREATE TABLE IF NOT EXISTS organization_markets (
organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
market_id uuid NOT NULL REFERENCES markets(id) ON DELETE CASCADE,
relationship_type text NOT NULL DEFAULT 'OPERATES_IN',
confidence numeric(4,3) NOT NULL DEFAULT 0.5,
evidence_id uuid,
PRIMARY KEY (organization_id, market_id, relationship_type)
);
-- ============================ PUBLICATIONS / PLACEMENTS =====================
CREATE TABLE IF NOT EXISTS publications (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL,
domain text,
publication_type text,
market_scope text,
media_kit_url text,
advertising_contact_url text,
source_policy_id uuid,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS placements (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
publication_id uuid REFERENCES publications(id) ON DELETE CASCADE,
name text NOT NULL,
channel text NOT NULL, -- WEBSITE, NEWSLETTER, EBLAST, VIDEO, PODCAST, EVENT, PRINT, SOCIAL, OTHER
size_or_format text,
market text,
list_size_snapshot integer,
public_rate numeric(12,2),
currency text DEFAULT 'USD',
rate_effective_date date,
source_evidence_id uuid,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS campaigns (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
publication_id uuid REFERENCES publications(id) ON DELETE SET NULL,
campaign_name text,
campaign_type text,
start_date date,
end_date date,
status text,
destination_url text,
utm_source text,
utm_medium text,
utm_campaign text,
notes text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS creative_assets (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid REFERENCES organizations(id) ON DELETE SET NULL,
file_name text NOT NULL,
mime_type text,
width integer,
height integer,
checksum text,
perceptual_hash text,
object_key text,
source_image_url text,
capture_method text, -- DOM_IMAGE, PAGE_SCREENSHOT, REGION_CROP, EMAIL_ATTACHMENT, MANUAL_UPLOAD, GENERATED_PLACEHOLDER
rights_status text NOT NULL DEFAULT 'UNKNOWN', -- INTERNAL_EVIDENCE_ONLY, EXPORT_ALLOWED, LINK_ONLY, UNKNOWN
captured_at timestamptz,
alt_text text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS ad_sightings (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
publication_id uuid REFERENCES publications(id) ON DELETE SET NULL,
campaign_id uuid REFERENCES campaigns(id) ON DELETE SET NULL,
placement_id uuid REFERENCES placements(id) ON DELETE SET NULL,
relationship_status text NOT NULL DEFAULT 'RESEARCH_NEEDED',
source_page_url text,
ad_click_url text,
landing_url text,
tracking_url text,
headline text,
visible_copy text,
observed_at timestamptz,
first_observed_at timestamptz,
last_observed_at timestamptz,
market_id uuid REFERENCES markets(id) ON DELETE SET NULL,
creative_asset_id uuid REFERENCES creative_assets(id) ON DELETE SET NULL,
full_screenshot_asset_id uuid REFERENCES creative_assets(id) ON DELETE SET NULL,
thumbnail_asset_id uuid REFERENCES creative_assets(id) ON DELETE SET NULL,
evidence_id uuid,
verified_by_user_id uuid,
verification_status text NOT NULL DEFAULT 'UNVERIFIED',
confidence numeric(4,3) NOT NULL DEFAULT 0.5,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_ad_org ON ad_sightings (organization_id);
CREATE INDEX IF NOT EXISTS idx_ad_status ON ad_sightings (relationship_status);
-- ============================ EVENTS / SPONSORSHIPS ========================
CREATE TABLE IF NOT EXISTS events (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organizer_organization_id uuid REFERENCES organizations(id) ON DELETE SET NULL,
publication_id uuid REFERENCES publications(id) ON DELETE SET NULL,
name text NOT NULL,
event_type text,
start_date date,
end_date date,
venue text,
city text,
state text,
market_id uuid REFERENCES markets(id) ON DELETE SET NULL,
official_url text,
sponsor_page_url text,
exhibitor_page_url text,
program_url text,
registration_url text,
source_evidence_id uuid,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS event_relationships (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
event_id uuid NOT NULL REFERENCES events(id) ON DELETE CASCADE,
organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
person_id uuid REFERENCES people(id) ON DELETE SET NULL,
relationship_status text NOT NULL DEFAULT 'SPEAKER_OR_PANELIST_ONLY',
sponsor_level text,
booth_number text,
session_title text,
panel_role text,
observed_at timestamptz,
evidence_id uuid,
confidence numeric(4,3) NOT NULL DEFAULT 0.5,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_evrel_event ON event_relationships (event_id);
CREATE INDEX IF NOT EXISTS idx_evrel_org ON event_relationships (organization_id);
-- ============================ SOURCES / PROVENANCE =========================
CREATE TABLE IF NOT EXISTS source_policies (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
source_key text UNIQUE NOT NULL,
display_name text NOT NULL,
owner text,
base_url text,
access_method text NOT NULL, -- official_api, official_bulk_download, rss_or_sitemap, first_party_public_web, authorized_mailbox, manual_upload, manual_review_only
terms_url text,
robots_url text,
allows_automated_access boolean NOT NULL DEFAULT false,
allows_screenshot_capture boolean NOT NULL DEFAULT false,
allows_internal_storage boolean NOT NULL DEFAULT true,
allows_export boolean NOT NULL DEFAULT false,
prohibited_hosts jsonb NOT NULL DEFAULT '[]'::jsonb,
permitted_paths jsonb NOT NULL DEFAULT '[]'::jsonb,
prohibited_paths jsonb NOT NULL DEFAULT '[]'::jsonb,
max_requests_per_minute integer,
minimum_delay_ms integer,
retention_days integer,
reviewed_at timestamptz,
review_notes text,
enabled boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS sources (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
source_policy_id uuid REFERENCES source_policies(id) ON DELETE SET NULL,
external_id text,
url text,
title text,
discovered_at timestamptz,
event_date date,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS source_documents (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
source_id uuid REFERENCES sources(id) ON DELETE CASCADE,
content_type text,
object_key text,
final_url text,
checksum text,
retrieved_at timestamptz,
parser_version text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS evidence_records (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
evidence_type text NOT NULL, -- WEB_PAGE, EMAIL, PDF, IMAGE, SCREENSHOT, CSV, API, MANUAL_NOTE
source_url text,
source_title text NOT NULL,
source_owner text,
observed_at timestamptz,
retrieved_at timestamptz NOT NULL DEFAULT now(),
excerpt text,
object_key text,
checksum text,
confidence numeric(4,3) NOT NULL DEFAULT 0.5,
export_allowed boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS field_evidence (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
entity_table text NOT NULL,
entity_id uuid NOT NULL,
field_name text NOT NULL,
field_value text,
evidence_id uuid REFERENCES evidence_records(id) ON DELETE CASCADE,
confidence numeric(4,3) NOT NULL DEFAULT 0.5,
is_conflicting boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_fieldev_entity ON field_evidence (entity_table, entity_id);
CREATE TABLE IF NOT EXISTS ingestion_runs (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
source_key text,
started_at timestamptz NOT NULL DEFAULT now(),
finished_at timestamptz,
status text NOT NULL DEFAULT 'RUNNING',
dry_run boolean NOT NULL DEFAULT false,
stats jsonb NOT NULL DEFAULT '{}'::jsonb,
error text
);
CREATE TABLE IF NOT EXISTS ingestion_jobs (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
run_id uuid REFERENCES ingestion_runs(id) ON DELETE CASCADE,
job_type text NOT NULL,
payload jsonb NOT NULL DEFAULT '{}'::jsonb,
status text NOT NULL DEFAULT 'PENDING',
attempts integer NOT NULL DEFAULT 0,
last_error text,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS parser_versions (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
parser_key text NOT NULL,
version text NOT NULL,
notes text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS source_health_checks (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
source_key text NOT NULL,
checked_at timestamptz NOT NULL DEFAULT now(),
ok boolean NOT NULL,
status_code integer,
latency_ms integer,
freshness_days integer,
note text
);
CREATE TABLE IF NOT EXISTS manual_review_items (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
review_type text NOT NULL, -- LINKEDIN_URL, AD_LIBRARY, MERGE, CROP, SOURCE
organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
person_id uuid REFERENCES people(id) ON DELETE CASCADE,
payload jsonb NOT NULL DEFAULT '{}'::jsonb,
status text NOT NULL DEFAULT 'PENDING',
discovery_query text,
discovered_at timestamptz,
reviewed_at timestamptz,
reviewed_by uuid,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS merge_candidates (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
left_id uuid NOT NULL,
right_id uuid NOT NULL,
entity_table text NOT NULL DEFAULT 'organizations',
score numeric(4,3) NOT NULL,
signals jsonb NOT NULL DEFAULT '{}'::jsonb,
status text NOT NULL DEFAULT 'PENDING',
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS merge_audit (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
kept_id uuid NOT NULL,
merged_id uuid NOT NULL,
entity_table text NOT NULL DEFAULT 'organizations',
snapshot jsonb NOT NULL, -- full pre-merge state for reversibility
reversed boolean NOT NULL DEFAULT false,
performed_by uuid,
performed_at timestamptz NOT NULL DEFAULT now()
);
-- ============================ ANALYTICS ====================================
CREATE TABLE IF NOT EXISTS analytics_connections (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
kind text NOT NULL, -- GA4, GSC, GOOGLE_ADS
property_id text,
site_url text,
status text NOT NULL DEFAULT 'NOT_CONNECTED',
is_demo boolean NOT NULL DEFAULT true,
last_import_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS ga4_daily_metrics (
metric_date date NOT NULL,
sessions integer,
total_users integer,
new_users integer,
engaged_sessions integer,
engagement_rate numeric(6,4),
avg_engagement_time numeric(10,2),
views integer,
event_count integer,
key_events integer,
is_demo boolean NOT NULL DEFAULT true,
PRIMARY KEY (metric_date)
);
CREATE TABLE IF NOT EXISTS ga4_landing_page_metrics (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
metric_date date NOT NULL,
landing_page text NOT NULL,
sessions integer, users integer, views integer,
engaged_sessions integer, engagement_rate numeric(6,4), key_events integer,
is_demo boolean NOT NULL DEFAULT true
);
CREATE TABLE IF NOT EXISTS ga4_acquisition_metrics (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
metric_date date NOT NULL,
channel_group text, session_source text, session_medium text, session_campaign text,
sessions integer, users integer, key_events integer,
is_demo boolean NOT NULL DEFAULT true
);
CREATE TABLE IF NOT EXISTS ga4_geo_metrics (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
metric_date date NOT NULL,
country text, region text, city text, market_id uuid REFERENCES markets(id) ON DELETE SET NULL,
sessions integer, users integer,
is_demo boolean NOT NULL DEFAULT true
);
CREATE TABLE IF NOT EXISTS gsc_query_metrics (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
metric_date date NOT NULL,
query text, country text, device text,
clicks integer, impressions integer, ctr numeric(6,4), position numeric(6,2),
is_brand boolean, cluster text,
is_demo boolean NOT NULL DEFAULT true
);
CREATE TABLE IF NOT EXISTS gsc_page_metrics (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
metric_date date NOT NULL,
page text, country text, device text,
clicks integer, impressions integer, ctr numeric(6,4), position numeric(6,2),
is_demo boolean NOT NULL DEFAULT true
);
CREATE TABLE IF NOT EXISTS google_ads_campaign_metrics (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
metric_date date NOT NULL,
campaign text, cost_micros bigint, clicks integer, impressions integer,
conversions numeric(12,2), currency text DEFAULT 'USD',
is_demo boolean NOT NULL DEFAULT true
);
CREATE TABLE IF NOT EXISTS google_ads_search_term_metrics (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
metric_date date NOT NULL,
search_term text, campaign text, clicks integer, impressions integer,
cost_micros bigint, conversions numeric(12,2),
is_demo boolean NOT NULL DEFAULT true
);
CREATE TABLE IF NOT EXISTS analytics_import_runs (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
kind text NOT NULL,
started_at timestamptz NOT NULL DEFAULT now(),
finished_at timestamptz,
status text NOT NULL DEFAULT 'RUNNING',
source_file text, checksum text, row_count integer, is_demo boolean NOT NULL DEFAULT true,
error text
);
CREATE TABLE IF NOT EXISTS analytics_annotations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
annotation_date date NOT NULL,
note text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
-- ============================ SALES WORKSPACE ==============================
CREATE TABLE IF NOT EXISTS opportunity_stages (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
key text UNIQUE NOT NULL,
label text NOT NULL,
sort_order integer NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS opportunities (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
stage_id uuid REFERENCES opportunity_stages(id) ON DELETE SET NULL,
recommended_product text,
recommended_market text,
sales_angle text,
owner_user_id uuid,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS opportunity_scores (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
score integer NOT NULL,
factors jsonb NOT NULL, -- full AdvertiserOpportunityInput + weights used
computed_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_oppscore_org ON opportunity_scores (organization_id);
CREATE TABLE IF NOT EXISTS notes (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
person_id uuid REFERENCES people(id) ON DELETE CASCADE,
body text NOT NULL,
is_private boolean NOT NULL DEFAULT false,
author_user_id uuid,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS tasks (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
title text NOT NULL,
due_date date,
status text NOT NULL DEFAULT 'OPEN',
assignee_user_id uuid,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS saved_views (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL,
query_json jsonb NOT NULL DEFAULT '{}'::jsonb,
owner_user_id uuid,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS tags (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
label text UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS organization_tags (
organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
tag_id uuid NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (organization_id, tag_id)
);
CREATE TABLE IF NOT EXISTS suppression_requests (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
scope text NOT NULL, -- ORGANIZATION, PERSON, CONTACT_POINT, EMAIL, PHONE
target_value text,
organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
person_id uuid REFERENCES people(id) ON DELETE CASCADE,
reason text,
active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS exports (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
kind text NOT NULL DEFAULT 'DOWNLOAD_EVERYTHING',
status text NOT NULL DEFAULT 'PENDING',
object_key text,
row_counts jsonb NOT NULL DEFAULT '{}'::jsonb,
requested_by uuid,
created_at timestamptz NOT NULL DEFAULT now(),
finished_at timestamptz
);
CREATE TABLE IF NOT EXISTS audit_logs (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
action text NOT NULL,
entity_table text,
entity_id uuid,
actor text,
detail jsonb NOT NULL DEFAULT '{}'::jsonb,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_logs (created_at);
-- ============================ RENTV RATE / AUDIENCE SNAPSHOTS ===============
-- Dated snapshots preserved (never overwritten) per spec §21.
CREATE TABLE IF NOT EXISTS rentv_rate_snapshots (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
product_key text NOT NULL, -- website_banner, newsletter_tile, property_spotlight, cre_talk_sponsorship, ...
product_label text NOT NULL,
rate numeric(12,2),
currency text DEFAULT 'USD',
unit text, -- per month, per eblast, per event, ...
package_notes text,
observed_at date NOT NULL,
effective_date date,
source_key text,
evidence_id uuid REFERENCES evidence_records(id) ON DELETE SET NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS rentv_audience_snapshots (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
metric_key text NOT NULL, -- net_recipients, open_rate, ...
metric_label text NOT NULL,
value_numeric numeric(14,2),
value_text text,
observed_at date NOT NULL,
source_key text,
evidence_id uuid REFERENCES evidence_records(id) ON DELETE SET NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
-- ============================ MIGRATIONS BOOKKEEPING =======================
CREATE TABLE IF NOT EXISTS schema_migrations (
version text PRIMARY KEY,
applied_at timestamptz NOT NULL DEFAULT now()
);