-- Mailforge Plugin — SQLITE DDL
-- Generated by scripts/export-ddl.ts on 2026-05-13T15:06:58.191Z
-- Source: concat of ./drizzle/sqlite/*.sql
-- Do NOT edit by hand. Re-run `npm run db:export-ddl` after schema changes.

-- 0000_condemned_wendigo.sql
CREATE TABLE `app_settings` (
	`key` text PRIMARY KEY NOT NULL,
	`value` text NOT NULL,
	`updated_at` integer DEFAULT (unixepoch() * 1000) NOT NULL
);
--> statement-breakpoint
CREATE TABLE `assets` (
	`id` text PRIMARY KEY NOT NULL,
	`campaign_id` text,
	`workspace_id` text,
	`kind` text NOT NULL,
	`file_path` text NOT NULL,
	`public_url` text NOT NULL,
	`alt_text` text,
	`prompt_hash` text,
	`prompt_json` text,
	`width` integer,
	`height` integer,
	`bytes` integer,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `assets_prompt_hash` ON `assets` (`prompt_hash`);--> statement-breakpoint
CREATE TABLE `behavior_events` (
	`id` text PRIMARY KEY NOT NULL,
	`campaign_id` text NOT NULL,
	`recipient_id` text NOT NULL,
	`variant_id` text,
	`type` text NOT NULL,
	`url` text,
	`product_id` text,
	`value` integer,
	`duration_ms` integer,
	`scroll_pct` integer,
	`meta_json` text,
	`user_agent` text,
	`ip_hash` text,
	`source` text NOT NULL,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`recipient_id`) REFERENCES `recipients`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`variant_id`) REFERENCES `campaign_variants`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `behavior_campaign_ts` ON `behavior_events` (`campaign_id`,`created_at`);--> statement-breakpoint
CREATE INDEX `behavior_recipient` ON `behavior_events` (`recipient_id`);--> statement-breakpoint
CREATE INDEX `behavior_recipient_type` ON `behavior_events` (`recipient_id`,`type`);--> statement-breakpoint
CREATE TABLE `campaign_variants` (
	`id` text PRIMARY KEY NOT NULL,
	`campaign_id` text NOT NULL,
	`label` text NOT NULL,
	`subject` text NOT NULL,
	`preheader` text NOT NULL,
	`mjml` text,
	`html` text,
	`html_text` text,
	`strategy_json` text,
	`spam_report_json` text,
	`spam_score` integer,
	`is_winner` integer DEFAULT false NOT NULL,
	`sent_count` integer DEFAULT 0 NOT NULL,
	`open_count` integer DEFAULT 0 NOT NULL,
	`click_count` integer DEFAULT 0 NOT NULL,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `variants_campaign` ON `campaign_variants` (`campaign_id`);--> statement-breakpoint
CREATE TABLE `campaign_versions` (
	`id` text PRIMARY KEY NOT NULL,
	`campaign_id` text NOT NULL,
	`source` text NOT NULL,
	`label` text,
	`milestone` text,
	`name` text NOT NULL,
	`briefing_json` text NOT NULL,
	`tiptap_json` text NOT NULL,
	`strategy_json` text,
	`email_html` text,
	`email_text` text,
	`spam_report_json` text,
	`hero_asset_id` text,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `versions_campaign_created` ON `campaign_versions` (`campaign_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `campaigns` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`template_id` text,
	`created_by_id` text NOT NULL,
	`name` text NOT NULL,
	`briefing_json` text NOT NULL,
	`tiptap_json` text NOT NULL,
	`output_dir` text,
	`strategy_json` text,
	`email_html_path` text,
	`email_txt_path` text,
	`hero_asset_id` text,
	`spam_report_json` text,
	`spam_score` integer,
	`spam_verdict` text,
	`ab_enabled` integer DEFAULT false NOT NULL,
	`ab_winner_hours` integer DEFAULT 4 NOT NULL,
	`ab_winner_variant_id` text,
	`utm_defaults_json` text,
	`status` text DEFAULT 'draft' NOT NULL,
	`kind` text DEFAULT 'marketing' NOT NULL,
	`scheduled_for` integer,
	`started_at` integer,
	`completed_at` integer,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	`updated_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`template_id`) REFERENCES `templates`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`created_by_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `email_queue` (
	`id` text PRIMARY KEY NOT NULL,
	`campaign_id` text NOT NULL,
	`recipient_id` text NOT NULL,
	`variant_id` text,
	`status` text DEFAULT 'pending' NOT NULL,
	`scheduled_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	`attempts` integer DEFAULT 0 NOT NULL,
	`last_error` text,
	`message_id` text,
	`sent_at` integer,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`recipient_id`) REFERENCES `recipients`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`variant_id`) REFERENCES `campaign_variants`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `queue_status_scheduled` ON `email_queue` (`status`,`scheduled_at`);--> statement-breakpoint
CREATE INDEX `queue_campaign_status` ON `email_queue` (`campaign_id`,`status`);--> statement-breakpoint
CREATE TABLE `magic_links` (
	`id` text PRIMARY KEY NOT NULL,
	`email` text NOT NULL,
	`token` text NOT NULL,
	`expires_at` integer NOT NULL,
	`used_at` integer,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `magic_links_token_unique` ON `magic_links` (`token`);--> statement-breakpoint
CREATE TABLE `recipients` (
	`id` text PRIMARY KEY NOT NULL,
	`campaign_id` text NOT NULL,
	`variant_id` text,
	`crm_id` text,
	`email` text NOT NULL,
	`first_name` text,
	`last_name` text,
	`pool_type` text DEFAULT 'private' NOT NULL,
	`custom_data_json` text,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`variant_id`) REFERENCES `campaign_variants`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `recipients_campaign_email` ON `recipients` (`campaign_id`,`email`);--> statement-breakpoint
CREATE TABLE `section_templates` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`kind` text NOT NULL,
	`name` text NOT NULL,
	`description` text,
	`default_content_json` text NOT NULL,
	`mjml_block` text NOT NULL,
	`ai_prompt_hint` text,
	`default_position` text DEFAULT 'before_signature' NOT NULL,
	`body_mode` text DEFAULT 'tiptap' NOT NULL,
	`is_system` integer DEFAULT false NOT NULL,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	`updated_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `section_templates_workspace_kind` ON `section_templates` (`workspace_id`,`kind`);--> statement-breakpoint
CREATE TABLE `sessions` (
	`id` text PRIMARY KEY NOT NULL,
	`user_id` text NOT NULL,
	`token` text NOT NULL,
	`expires_at` integer NOT NULL,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `sessions_token_unique` ON `sessions` (`token`);--> statement-breakpoint
CREATE TABLE `templates` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`name` text NOT NULL,
	`description` text,
	`tiptap_json` text NOT NULL,
	`briefing_json` text,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	`updated_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `tracking_events` (
	`id` text PRIMARY KEY NOT NULL,
	`campaign_id` text NOT NULL,
	`recipient_id` text,
	`variant_id` text,
	`message_id` text NOT NULL,
	`event_type` text NOT NULL,
	`link_url` text,
	`user_agent` text,
	`ip_hash` text,
	`is_mpp` integer,
	`value` integer,
	`meta_json` text,
	`source` text,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`recipient_id`) REFERENCES `recipients`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`variant_id`) REFERENCES `campaign_variants`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `events_campaign_ts` ON `tracking_events` (`campaign_id`,`created_at`);--> statement-breakpoint
CREATE INDEX `events_message` ON `tracking_events` (`message_id`,`event_type`);--> statement-breakpoint
CREATE INDEX `events_recipient_event_ts` ON `tracking_events` (`recipient_id`,`event_type`,`created_at`);--> statement-breakpoint
CREATE TABLE `trigger_logs` (
	`id` text PRIMARY KEY NOT NULL,
	`trigger_template_id` text NOT NULL,
	`recipient_email` text NOT NULL,
	`idempotency_key` text,
	`status` text NOT NULL,
	`error_message` text,
	`request_payload_json` text,
	`email_queue_job_id` text,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`trigger_template_id`) REFERENCES `trigger_templates`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `trigger_logs_template_created` ON `trigger_logs` (`trigger_template_id`,`created_at`);--> statement-breakpoint
CREATE INDEX `trigger_logs_idempotency` ON `trigger_logs` (`trigger_template_id`,`idempotency_key`);--> statement-breakpoint
CREATE TABLE `trigger_templates` (
	`id` text PRIMARY KEY NOT NULL,
	`key` text NOT NULL,
	`name` text NOT NULL,
	`description` text,
	`workspace_id` text NOT NULL,
	`container_campaign_id` text NOT NULL,
	`container_variant_id` text NOT NULL,
	`subject` text NOT NULL,
	`preheader` text NOT NULL,
	`mjml` text NOT NULL,
	`html` text NOT NULL,
	`html_text` text NOT NULL,
	`variable_schema_json` text NOT NULL,
	`sender_email` text,
	`api_key_hash` text NOT NULL,
	`api_key_prefix` text NOT NULL,
	`is_active` integer DEFAULT true NOT NULL,
	`utm_defaults_json` text,
	`rate_limit_per_hour` integer,
	`last_triggered_at` integer,
	`trigger_count` integer DEFAULT 0 NOT NULL,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	`updated_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`container_campaign_id`) REFERENCES `campaigns`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`container_variant_id`) REFERENCES `campaign_variants`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `trigger_templates_key_unique` ON `trigger_templates` (`key`);--> statement-breakpoint
CREATE TABLE `unsubscribes` (
	`id` text PRIMARY KEY NOT NULL,
	`email` text NOT NULL,
	`campaign_id` text,
	`reason` text,
	`source` text NOT NULL,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `unsubscribes_email_unique` ON `unsubscribes` (`email`);--> statement-breakpoint
CREATE TABLE `users` (
	`id` text PRIMARY KEY NOT NULL,
	`email` text NOT NULL,
	`name` text,
	`role` text DEFAULT 'editor' NOT NULL,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	`last_login_at` integer
);
--> statement-breakpoint
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);--> statement-breakpoint
CREATE TABLE `workspaces` (
	`id` text PRIMARY KEY NOT NULL,
	`slug` text NOT NULL,
	`name` text NOT NULL,
	`primary_color` text NOT NULL,
	`secondary_color` text,
	`logo_url` text,
	`sender_domain` text NOT NULL,
	`sender_name` text NOT NULL,
	`sender_email` text NOT NULL,
	`reply_to_email` text NOT NULL,
	`shop_domain` text,
	`webhook_secret` text,
	`ga4_property_id` text,
	`ga4_credentials_json` text,
	`footer_text` text NOT NULL,
	`brand_voice` text NOT NULL,
	`wording_style` text,
	`design_style` text,
	`company_id` integer,
	`imprint_url` text NOT NULL,
	`privacy_url` text NOT NULL,
	`brand_kit_json` text NOT NULL,
	`attribution_model` text DEFAULT 'last-click' NOT NULL,
	`attribution_lookback_days` integer DEFAULT 30 NOT NULL,
	`cumas_recipient_field` text,
	`active_conversion_kinds` text DEFAULT '["order","lead","magic_login","form_submit"]' NOT NULL,
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
	`updated_at` integer DEFAULT (unixepoch() * 1000) NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `workspaces_slug_unique` ON `workspaces` (`slug`);
