486 lines
19 KiB
SQL
486 lines
19 KiB
SQL
-- Marketing QBit (com.kingsrook.qbits:qbit-marketing) - PostgreSQL DDL
|
|
--
|
|
-- Generated by scripts/generate-model.py (the single spec for entities, enums and DDL).
|
|
-- Table names are the QQQ table names (marketing_*), and column names are the
|
|
-- snake_case forms of the QQQ field names, which the qbit sets as each field's
|
|
-- backend name (MarketingQBitConfig.inferBackendNames, default true).
|
|
--
|
|
-- Liquibase: paste into a formatted-SQL changeset. Idempotent (IF NOT EXISTS).
|
|
|
|
-- A product the marketing team promotes: its brief, maturity and official links.
|
|
CREATE TABLE IF NOT EXISTS marketing_product (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
slug VARCHAR(64) NOT NULL,
|
|
tagline VARCHAR(255),
|
|
summary TEXT,
|
|
maturity INTEGER NOT NULL,
|
|
readiness_notes TEXT,
|
|
primary_url VARCHAR(1000),
|
|
repository_url VARCHAR(1000),
|
|
documentation_url VARCHAR(1000),
|
|
releases_url VARCHAR(1000),
|
|
license VARCHAR(100),
|
|
platforms VARCHAR(255),
|
|
latest_release VARCHAR(100),
|
|
adoption_path TEXT,
|
|
value_propositions TEXT,
|
|
owner_user_id VARCHAR(100),
|
|
last_verified_date DATE,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_product PRIMARY KEY (id),
|
|
CONSTRAINT uq_marketing_product_1 UNIQUE (slug)
|
|
);
|
|
|
|
|
|
-- An audience of a product: who they are, the real problem and the task the product helps complete.
|
|
CREATE TABLE IF NOT EXISTS marketing_audience (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER NOT NULL,
|
|
name VARCHAR(150) NOT NULL,
|
|
problem TEXT,
|
|
useful_task TEXT,
|
|
description TEXT,
|
|
evidence_note TEXT,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_audience PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_audience_product_id ON marketing_audience (product_id);
|
|
|
|
-- A source that product facts were verified against, with its verification date.
|
|
CREATE TABLE IF NOT EXISTS marketing_evidence_source (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
url VARCHAR(1000) NOT NULL,
|
|
source_type INTEGER NOT NULL,
|
|
publisher VARCHAR(150),
|
|
verified_date DATE,
|
|
verified_by VARCHAR(100),
|
|
excerpt TEXT,
|
|
notes TEXT,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_evidence_source PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_evidence_source_product_id ON marketing_evidence_source (product_id);
|
|
|
|
-- A factual claim about a product, its status (released, planned, disputed) and its source.
|
|
CREATE TABLE IF NOT EXISTS marketing_claim (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER NOT NULL,
|
|
claim_text VARCHAR(1000) NOT NULL,
|
|
status INTEGER NOT NULL,
|
|
source_id INTEGER,
|
|
category VARCHAR(64),
|
|
evidence_quote TEXT,
|
|
verified_date DATE,
|
|
discrepancy_note TEXT,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_claim PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_claim_product_id ON marketing_claim (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_claim_source_id ON marketing_claim (source_id);
|
|
|
|
-- A campaign: one goal, audience, message, channels, call to action and measurable success criteria.
|
|
CREATE TABLE IF NOT EXISTS marketing_campaign (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER NOT NULL,
|
|
name VARCHAR(200) NOT NULL,
|
|
goal INTEGER NOT NULL,
|
|
status INTEGER NOT NULL,
|
|
audience_id INTEGER,
|
|
objective TEXT,
|
|
problem TEXT,
|
|
messaging TEXT,
|
|
channel_plan TEXT,
|
|
call_to_action VARCHAR(500),
|
|
destination_url VARCHAR(1000),
|
|
utm_campaign VARCHAR(100),
|
|
schedule_start TIMESTAMP WITHOUT TIME ZONE,
|
|
schedule_end TIMESTAMP WITHOUT TIME ZONE,
|
|
owner_user_id VARCHAR(100),
|
|
success_criteria TEXT,
|
|
lead_qualification_enabled BOOLEAN,
|
|
qualify_min_form_submissions INTEGER,
|
|
qualify_require_email_consent BOOLEAN,
|
|
lead_qualification_note TEXT,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_campaign PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_campaign_product_id ON marketing_campaign (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_campaign_audience_id ON marketing_campaign (audience_id);
|
|
|
|
-- A channel content can go to, its transport and the rules it must respect.
|
|
CREATE TABLE IF NOT EXISTS marketing_channel (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
channel_type INTEGER NOT NULL,
|
|
transport_name VARCHAR(64),
|
|
is_active BOOLEAN,
|
|
allows_automation BOOLEAN,
|
|
ai_content_policy INTEGER NOT NULL,
|
|
rules_summary TEXT,
|
|
rules_source_url VARCHAR(1000),
|
|
rules_verified_date DATE,
|
|
rate_limit_per_hour INTEGER,
|
|
max_recipients_per_execution INTEGER,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_channel PRIMARY KEY (id),
|
|
CONSTRAINT uq_marketing_channel_1 UNIQUE (name)
|
|
);
|
|
|
|
|
|
-- A content item: the exact publication or message proposed for a channel.
|
|
CREATE TABLE IF NOT EXISTS marketing_content (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
campaign_id INTEGER NOT NULL,
|
|
channel_id INTEGER,
|
|
title VARCHAR(255) NOT NULL,
|
|
body TEXT,
|
|
destination_url VARCHAR(1000),
|
|
call_to_action VARCHAR(500),
|
|
recipient_scope VARCHAR(255),
|
|
scheduled_for TIMESTAMP WITHOUT TIME ZONE,
|
|
status INTEGER,
|
|
author_type INTEGER,
|
|
revision_number INTEGER,
|
|
content_hash VARCHAR(64),
|
|
agent_run_id INTEGER,
|
|
owner_user_id VARCHAR(100),
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_content PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_content_product_id ON marketing_content (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_content_campaign_id ON marketing_content (campaign_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_content_channel_id ON marketing_content (channel_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_content_agent_run_id ON marketing_content (agent_run_id);
|
|
|
|
-- An immutable revision of a content item (written on every relevant edit).
|
|
CREATE TABLE IF NOT EXISTS marketing_content_revision (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
content_id INTEGER NOT NULL,
|
|
revision_number INTEGER NOT NULL,
|
|
title VARCHAR(255),
|
|
body TEXT,
|
|
destination_url VARCHAR(1000),
|
|
call_to_action VARCHAR(500),
|
|
recipient_scope VARCHAR(255),
|
|
scheduled_for TIMESTAMP WITHOUT TIME ZONE,
|
|
channel_id INTEGER,
|
|
content_hash VARCHAR(64) NOT NULL,
|
|
author_type INTEGER,
|
|
author_user_id VARCHAR(100),
|
|
agent_run_id INTEGER,
|
|
change_note VARCHAR(500),
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_content_revision PRIMARY KEY (id),
|
|
CONSTRAINT uq_marketing_content_revision_1 UNIQUE (content_id, revision_number)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_content_revision_product_id ON marketing_content_revision (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_content_revision_content_id ON marketing_content_revision (content_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_content_revision_channel_id ON marketing_content_revision (channel_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_content_revision_agent_run_id ON marketing_content_revision (agent_run_id);
|
|
|
|
-- A human approval of one exact content revision, destination, recipient scope and schedule.
|
|
CREATE TABLE IF NOT EXISTS marketing_approval (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
content_id INTEGER NOT NULL,
|
|
revision_id INTEGER,
|
|
revision_number INTEGER,
|
|
content_hash VARCHAR(64) NOT NULL,
|
|
channel_id INTEGER,
|
|
destination_url VARCHAR(1000),
|
|
recipient_scope VARCHAR(255),
|
|
scheduled_for TIMESTAMP WITHOUT TIME ZONE,
|
|
status INTEGER NOT NULL,
|
|
approved_by_user_id VARCHAR(100),
|
|
approved_at TIMESTAMP WITHOUT TIME ZONE,
|
|
note VARCHAR(500),
|
|
invalidated_at TIMESTAMP WITHOUT TIME ZONE,
|
|
invalidated_reason VARCHAR(500),
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_approval PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_approval_product_id ON marketing_approval (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_approval_content_id ON marketing_approval (content_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_approval_revision_id ON marketing_approval (revision_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_approval_channel_id ON marketing_approval (channel_id);
|
|
|
|
-- A durable execution job: one approved content item to one channel transport.
|
|
CREATE TABLE IF NOT EXISTS marketing_execution (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
campaign_id INTEGER,
|
|
content_id INTEGER NOT NULL,
|
|
approval_id INTEGER NOT NULL,
|
|
revision_id INTEGER,
|
|
channel_id INTEGER,
|
|
transport_name VARCHAR(64),
|
|
scheduled_for TIMESTAMP WITHOUT TIME ZONE,
|
|
status INTEGER NOT NULL,
|
|
idempotency_key VARCHAR(200) NOT NULL,
|
|
attempt_count INTEGER,
|
|
max_attempts INTEGER,
|
|
next_attempt_at TIMESTAMP WITHOUT TIME ZONE,
|
|
last_attempt_at TIMESTAMP WITHOUT TIME ZONE,
|
|
completed_at TIMESTAMP WITHOUT TIME ZONE,
|
|
provider_receipt_id VARCHAR(255),
|
|
status_detail TEXT,
|
|
requested_by_user_id VARCHAR(100),
|
|
is_test_data BOOLEAN,
|
|
lease_owner VARCHAR(100),
|
|
lease_expires_at TIMESTAMP WITHOUT TIME ZONE,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_execution PRIMARY KEY (id),
|
|
CONSTRAINT uq_marketing_execution_1 UNIQUE (idempotency_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_execution_product_id ON marketing_execution (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_execution_campaign_id ON marketing_execution (campaign_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_execution_content_id ON marketing_execution (content_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_execution_approval_id ON marketing_execution (approval_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_execution_revision_id ON marketing_execution (revision_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_execution_channel_id ON marketing_execution (channel_id);
|
|
|
|
-- One attempt to run an execution, with the provider's receipt or failure.
|
|
CREATE TABLE IF NOT EXISTS marketing_execution_attempt (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
execution_id INTEGER NOT NULL,
|
|
attempt_number INTEGER,
|
|
started_at TIMESTAMP WITHOUT TIME ZONE,
|
|
finished_at TIMESTAMP WITHOUT TIME ZONE,
|
|
outcome INTEGER NOT NULL,
|
|
recipient_count INTEGER,
|
|
provider_receipt_id VARCHAR(255),
|
|
provider_status VARCHAR(100),
|
|
message TEXT,
|
|
duration_ms BIGINT,
|
|
worker_name VARCHAR(100),
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_execution_attempt PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_execution_attempt_product_id ON marketing_execution_attempt (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_execution_attempt_execution_id ON marketing_execution_attempt (execution_id);
|
|
|
|
-- What the controlled test transport accepted: the exact payload, never sent anywhere.
|
|
CREATE TABLE IF NOT EXISTS marketing_test_delivery (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
execution_id INTEGER,
|
|
idempotency_key VARCHAR(200) NOT NULL,
|
|
recipient_key VARCHAR(200),
|
|
channel_name VARCHAR(100),
|
|
title VARCHAR(255),
|
|
body TEXT,
|
|
destination_url VARCHAR(1000),
|
|
receipt_id VARCHAR(100),
|
|
accepted_at TIMESTAMP WITHOUT TIME ZONE,
|
|
duplicate_count INTEGER,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_test_delivery PRIMARY KEY (id),
|
|
CONSTRAINT uq_marketing_test_delivery_1 UNIQUE (idempotency_key, recipient_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_test_delivery_product_id ON marketing_test_delivery (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_test_delivery_execution_id ON marketing_test_delivery (execution_id);
|
|
|
|
-- A run of the marketing agent: task, status, provider, model usage and outputs.
|
|
CREATE TABLE IF NOT EXISTS marketing_agent_run (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
campaign_id INTEGER,
|
|
content_id INTEGER,
|
|
task INTEGER NOT NULL,
|
|
status INTEGER NOT NULL,
|
|
instructions TEXT,
|
|
provider_name VARCHAR(64),
|
|
model VARCHAR(100),
|
|
requested_by_user_id VARCHAR(100),
|
|
started_at TIMESTAMP WITHOUT TIME ZONE,
|
|
heartbeat_at TIMESTAMP WITHOUT TIME ZONE,
|
|
finished_at TIMESTAMP WITHOUT TIME ZONE,
|
|
model_calls INTEGER,
|
|
tool_calls INTEGER,
|
|
input_tokens BIGINT,
|
|
output_tokens BIGINT,
|
|
cache_read_tokens BIGINT,
|
|
cost_micros BIGINT,
|
|
drafts_created INTEGER,
|
|
findings_created INTEGER,
|
|
summary TEXT,
|
|
output_json TEXT,
|
|
error_message TEXT,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_agent_run PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_agent_run_product_id ON marketing_agent_run (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_agent_run_campaign_id ON marketing_agent_run (campaign_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_agent_run_content_id ON marketing_agent_run (content_id);
|
|
|
|
-- One step of an agent run: a model call, a tool call or a deterministic check.
|
|
CREATE TABLE IF NOT EXISTS marketing_agent_step (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
agent_run_id INTEGER NOT NULL,
|
|
seq INTEGER,
|
|
kind INTEGER NOT NULL,
|
|
tool_name VARCHAR(100),
|
|
outcome VARCHAR(32),
|
|
summary VARCHAR(1000),
|
|
input_json TEXT,
|
|
result_json TEXT,
|
|
input_tokens BIGINT,
|
|
output_tokens BIGINT,
|
|
duration_ms BIGINT,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_agent_step PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_agent_step_product_id ON marketing_agent_step (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_agent_step_agent_run_id ON marketing_agent_step (agent_run_id);
|
|
|
|
-- A finding for a person to act on: unsupported claims, stale sources, gaps, limitations, recommendations.
|
|
CREATE TABLE IF NOT EXISTS marketing_finding (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
campaign_id INTEGER,
|
|
content_id INTEGER,
|
|
claim_id INTEGER,
|
|
source_id INTEGER,
|
|
agent_run_id INTEGER,
|
|
finding_type INTEGER NOT NULL,
|
|
severity INTEGER NOT NULL,
|
|
status INTEGER NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
detail TEXT,
|
|
recommendation TEXT,
|
|
origin VARCHAR(32),
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_finding PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_finding_product_id ON marketing_finding (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_finding_campaign_id ON marketing_finding (campaign_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_finding_content_id ON marketing_finding (content_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_finding_claim_id ON marketing_finding (claim_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_finding_source_id ON marketing_finding (source_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_finding_agent_run_id ON marketing_finding (agent_run_id);
|
|
|
|
-- A campaign metric: its stage, the event it counts, how it counts and whether it is instrumented.
|
|
CREATE TABLE IF NOT EXISTS marketing_metric (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
campaign_id INTEGER NOT NULL,
|
|
stage INTEGER NOT NULL,
|
|
name VARCHAR(150) NOT NULL,
|
|
event_type VARCHAR(100) NOT NULL,
|
|
count_mode INTEGER NOT NULL,
|
|
instrumentation_status INTEGER NOT NULL,
|
|
target INTEGER,
|
|
notes TEXT,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_metric PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_metric_product_id ON marketing_metric (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_metric_campaign_id ON marketing_metric (campaign_id);
|
|
|
|
-- A raw outcome event (deduplicated by source and external id), with its funnel stage and anonymous actor.
|
|
CREATE TABLE IF NOT EXISTS marketing_outcome_event (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
product_id INTEGER,
|
|
campaign_id INTEGER,
|
|
content_id INTEGER,
|
|
execution_id INTEGER,
|
|
stage INTEGER NOT NULL,
|
|
event_type VARCHAR(100) NOT NULL,
|
|
source VARCHAR(100) NOT NULL,
|
|
external_event_id VARCHAR(200) NOT NULL,
|
|
actor_key VARCHAR(200),
|
|
occurred_at TIMESTAMP WITHOUT TIME ZONE,
|
|
quantity INTEGER,
|
|
is_test_data BOOLEAN,
|
|
note VARCHAR(500),
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_outcome_event PRIMARY KEY (id),
|
|
CONSTRAINT uq_marketing_outcome_event_1 UNIQUE (source, external_event_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_outcome_event_product_id ON marketing_outcome_event (product_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_outcome_event_campaign_id ON marketing_outcome_event (campaign_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_outcome_event_content_id ON marketing_outcome_event (content_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_outcome_event_execution_id ON marketing_outcome_event (execution_id);
|
|
|
|
-- A persisted daily rollup of a campaign's outcome events per stage and event type (system-written, recomputable from the raw events).
|
|
CREATE TABLE IF NOT EXISTS marketing_metric_daily (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
campaign_id INTEGER NOT NULL,
|
|
product_id INTEGER,
|
|
day_utc DATE NOT NULL,
|
|
funnel_stage INTEGER NOT NULL,
|
|
event_type VARCHAR(100) NOT NULL,
|
|
is_test_data BOOLEAN NOT NULL,
|
|
raw_count INTEGER,
|
|
unique_actors INTEGER,
|
|
events_without_actor INTEGER,
|
|
recomputed_at TIMESTAMP WITHOUT TIME ZONE,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_metric_daily PRIMARY KEY (id),
|
|
CONSTRAINT uq_marketing_metric_daily_1 UNIQUE (campaign_id, day_utc, funnel_stage, event_type, is_test_data)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_metric_daily_campaign_id ON marketing_metric_daily (campaign_id);
|
|
CREATE INDEX IF NOT EXISTS ix_marketing_metric_daily_product_id ON marketing_metric_daily (product_id);
|
|
|
|
-- Runtime switches and limits: agent and execution kill switches, provider, model, budgets, concurrency.
|
|
CREATE TABLE IF NOT EXISTS marketing_policy (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
|
name VARCHAR(64) NOT NULL,
|
|
agent_enabled BOOLEAN,
|
|
execution_enabled BOOLEAN,
|
|
kill_switch_reason VARCHAR(255),
|
|
provider_name VARCHAR(64),
|
|
model VARCHAR(100),
|
|
daily_token_limit BIGINT,
|
|
monthly_cost_limit_micros BIGINT,
|
|
max_concurrent_agent_runs INTEGER,
|
|
max_tool_calls_per_run INTEGER,
|
|
create_date TIMESTAMP WITHOUT TIME ZONE,
|
|
modify_date TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pk_marketing_policy PRIMARY KEY (id),
|
|
CONSTRAINT uq_marketing_policy_1 UNIQUE (name)
|
|
);
|
|
|
|
|