james.maesandClaude Opus 5.5
094f728d85
Tests for the required checks, and the fixes they drove: - Unauthorized access is denied with core permission enforcement on. Preparation, approval, execution and the agent need separate permissions. The optional product security key bounds what users and the agent's tools see. - Drafts are made without publication authority. The agent has no approve, schedule or send tool. A draft that cites a non-released claim gets a blocker finding, which stops approval. - Approval is invalidated by any approved-field edit, including a schedule change. Status and approval rows can't be forged. - Pause, resume and cancel. Duplicate scheduling and runs are refused. Ambiguous outcomes are reconciled or decided by a person and never retried blindly. Repeats are deduplicated by the idempotency key. Retries happen only when the request was certainly not sent. Expired leases need reconciliation. - At execution time: the kill switch, the schedule, consent and suppression, the bulk limit, channel AI-content rules, and unconfigured transports. - Outcome events are deduplicated by (source, external id). Raw events and unique actors are reported separately, and uninstrumented metrics show as "Not measured". CRM import is idempotent, and leads are qualified only by an explicit rule. - Missing provider: the run fails with a plain reason and manual work still runs. Provider errors are retried or failed by kind, and no secret appears in errors. Budgets, the concurrency limit, cancellation and the model-call limit are enforced. - Persistence across a restart, on the RDBMS module over a file-backed H2 database created from the shipped DDL. - The OpenAI-compatible provider is tested against a local HTTP fixture. Fixes: - Service entry points run as trusted internal work after their own permission check, the same way QQQ trusts nested calls. Agent tools still force PermissionEnforcement.ENFORCE. - Content status and authorType are managed rather than required, and productId is derived. - Schedules are kept to the second, so stored values hash the same as approved ones on every database. - The Result screen lists skipped records with their reason. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
463 lines
18 KiB
SQL
463 lines
18 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,
|
|
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);
|
|
|
|
-- 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)
|
|
);
|
|
|
|
|