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

-- 0000_initial.sql
CREATE TABLE `app_settings` (
	`key` varchar(128) NOT NULL,
	`value` json NOT NULL,
	`updated_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `app_settings_key` PRIMARY KEY(`key`)
);
--> statement-breakpoint
CREATE TABLE `assets` (
	`id` varchar(36) NOT NULL,
	`campaign_id` varchar(36),
	`workspace_id` varchar(36),
	`kind` varchar(16) NOT NULL,
	`file_path` varchar(1024) NOT NULL,
	`public_url` varchar(1024) NOT NULL,
	`alt_text` varchar(512),
	`prompt_hash` varchar(128),
	`prompt_json` json,
	`width` int,
	`height` int,
	`bytes` int,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `assets_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `behavior_events` (
	`id` varchar(36) NOT NULL,
	`campaign_id` varchar(36) NOT NULL,
	`recipient_id` varchar(36) NOT NULL,
	`variant_id` varchar(36),
	`type` varchar(32) NOT NULL,
	`url` varchar(2048),
	`product_id` varchar(128),
	`value` int,
	`duration_ms` int,
	`scroll_pct` int,
	`meta_json` json,
	`user_agent` varchar(512),
	`ip_hash` varchar(128),
	`source` varchar(16) NOT NULL,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `behavior_events_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `campaign_variants` (
	`id` varchar(36) NOT NULL,
	`campaign_id` varchar(36) NOT NULL,
	`label` varchar(16) NOT NULL,
	`subject` varchar(512) NOT NULL,
	`preheader` varchar(512) NOT NULL,
	`mjml` text,
	`html` text,
	`html_text` text,
	`strategy_json` json,
	`spam_report_json` json,
	`spam_score` int,
	`is_winner` boolean NOT NULL DEFAULT false,
	`sent_count` int NOT NULL DEFAULT 0,
	`open_count` int NOT NULL DEFAULT 0,
	`click_count` int NOT NULL DEFAULT 0,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `campaign_variants_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `campaign_versions` (
	`id` varchar(36) NOT NULL,
	`campaign_id` varchar(36) NOT NULL,
	`source` varchar(16) NOT NULL,
	`label` varchar(255),
	`milestone` varchar(16),
	`name` varchar(255) NOT NULL,
	`briefing_json` json NOT NULL,
	`tiptap_json` json NOT NULL,
	`strategy_json` json,
	`email_html` text,
	`email_text` text,
	`spam_report_json` json,
	`hero_asset_id` varchar(36),
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `campaign_versions_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `campaigns` (
	`id` varchar(36) NOT NULL,
	`workspace_id` varchar(36) NOT NULL,
	`template_id` varchar(36),
	`created_by_id` varchar(36) NOT NULL,
	`name` varchar(255) NOT NULL,
	`briefing_json` json NOT NULL,
	`tiptap_json` json NOT NULL,
	`output_dir` varchar(512),
	`strategy_json` json,
	`email_html_path` varchar(512),
	`email_txt_path` varchar(512),
	`hero_asset_id` varchar(36),
	`spam_report_json` json,
	`spam_score` int,
	`spam_verdict` varchar(16),
	`ab_enabled` boolean NOT NULL DEFAULT false,
	`ab_winner_hours` int NOT NULL DEFAULT 4,
	`ab_winner_variant_id` varchar(36),
	`utm_defaults_json` json,
	`status` varchar(32) NOT NULL DEFAULT 'draft',
	`kind` varchar(32) NOT NULL DEFAULT 'marketing',
	`scheduled_for` bigint,
	`started_at` bigint,
	`completed_at` bigint,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	`updated_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `campaigns_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `email_queue` (
	`id` varchar(36) NOT NULL,
	`campaign_id` varchar(36) NOT NULL,
	`recipient_id` varchar(36) NOT NULL,
	`variant_id` varchar(36),
	`status` varchar(32) NOT NULL DEFAULT 'pending',
	`scheduled_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	`attempts` int NOT NULL DEFAULT 0,
	`last_error` text,
	`message_id` varchar(255),
	`sent_at` bigint,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `email_queue_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `magic_links` (
	`id` varchar(36) NOT NULL,
	`email` varchar(255) NOT NULL,
	`token` varchar(255) NOT NULL,
	`expires_at` bigint NOT NULL,
	`used_at` bigint,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `magic_links_id` PRIMARY KEY(`id`),
	CONSTRAINT `magic_links_token_unique` UNIQUE(`token`)
);
--> statement-breakpoint
CREATE TABLE `recipients` (
	`id` varchar(36) NOT NULL,
	`campaign_id` varchar(36) NOT NULL,
	`variant_id` varchar(36),
	`crm_id` varchar(128),
	`email` varchar(255) NOT NULL,
	`first_name` varchar(128),
	`last_name` varchar(128),
	`pool_type` varchar(16) NOT NULL DEFAULT 'private',
	`custom_data_json` json,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `recipients_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `section_templates` (
	`id` varchar(36) NOT NULL,
	`workspace_id` varchar(36) NOT NULL,
	`kind` varchar(32) NOT NULL,
	`name` varchar(255) NOT NULL,
	`description` varchar(1024),
	`default_content_json` json NOT NULL,
	`mjml_block` text NOT NULL,
	`ai_prompt_hint` text,
	`default_position` varchar(32) NOT NULL DEFAULT 'before_signature',
	`body_mode` varchar(16) NOT NULL DEFAULT 'tiptap',
	`is_system` boolean NOT NULL DEFAULT false,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	`updated_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `section_templates_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `sessions` (
	`id` varchar(36) NOT NULL,
	`user_id` varchar(36) NOT NULL,
	`token` varchar(255) NOT NULL,
	`expires_at` bigint NOT NULL,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `sessions_id` PRIMARY KEY(`id`),
	CONSTRAINT `sessions_token_unique` UNIQUE(`token`)
);
--> statement-breakpoint
CREATE TABLE `templates` (
	`id` varchar(36) NOT NULL,
	`workspace_id` varchar(36) NOT NULL,
	`name` varchar(255) NOT NULL,
	`description` varchar(1024),
	`tiptap_json` json NOT NULL,
	`briefing_json` json,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	`updated_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `templates_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `tracking_events` (
	`id` varchar(36) NOT NULL,
	`campaign_id` varchar(36) NOT NULL,
	`recipient_id` varchar(36),
	`variant_id` varchar(36),
	`message_id` varchar(255) NOT NULL,
	`event_type` varchar(32) NOT NULL,
	`link_url` varchar(2048),
	`user_agent` varchar(512),
	`ip_hash` varchar(128),
	`is_mpp` boolean,
	`value` int,
	`meta_json` json,
	`source` varchar(16),
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `tracking_events_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `trigger_logs` (
	`id` varchar(36) NOT NULL,
	`trigger_template_id` varchar(36) NOT NULL,
	`recipient_email` varchar(255) NOT NULL,
	`idempotency_key` varchar(128),
	`status` varchar(32) NOT NULL,
	`error_message` text,
	`request_payload_json` json,
	`email_queue_job_id` varchar(36),
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `trigger_logs_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `trigger_templates` (
	`id` varchar(36) NOT NULL,
	`key` varchar(64) NOT NULL,
	`name` varchar(255) NOT NULL,
	`description` varchar(1024),
	`workspace_id` varchar(36) NOT NULL,
	`container_campaign_id` varchar(36) NOT NULL,
	`container_variant_id` varchar(36) NOT NULL,
	`subject` varchar(512) NOT NULL,
	`preheader` varchar(512) NOT NULL,
	`mjml` text NOT NULL,
	`html` text NOT NULL,
	`html_text` text NOT NULL,
	`variable_schema_json` json NOT NULL,
	`sender_email` varchar(255),
	`api_key_hash` varchar(128) NOT NULL,
	`api_key_prefix` varchar(16) NOT NULL,
	`is_active` boolean NOT NULL DEFAULT true,
	`utm_defaults_json` json,
	`rate_limit_per_hour` int,
	`last_triggered_at` bigint,
	`trigger_count` int NOT NULL DEFAULT 0,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	`updated_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `trigger_templates_id` PRIMARY KEY(`id`),
	CONSTRAINT `trigger_templates_key_unique` UNIQUE(`key`)
);
--> statement-breakpoint
CREATE TABLE `unsubscribes` (
	`id` varchar(36) NOT NULL,
	`email` varchar(255) NOT NULL,
	`campaign_id` varchar(36),
	`reason` varchar(512),
	`source` varchar(16) NOT NULL,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `unsubscribes_id` PRIMARY KEY(`id`),
	CONSTRAINT `unsubscribes_email_unique` UNIQUE(`email`)
);
--> statement-breakpoint
CREATE TABLE `users` (
	`id` varchar(36) NOT NULL,
	`email` varchar(255) NOT NULL,
	`name` varchar(255),
	`role` varchar(32) NOT NULL DEFAULT 'editor',
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	`last_login_at` bigint,
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_email_unique` UNIQUE(`email`)
);
--> statement-breakpoint
CREATE TABLE `workspaces` (
	`id` varchar(36) NOT NULL,
	`slug` varchar(64) NOT NULL,
	`name` varchar(255) NOT NULL,
	`primary_color` varchar(16) NOT NULL,
	`secondary_color` varchar(16),
	`logo_url` varchar(1024),
	`sender_domain` varchar(255) NOT NULL,
	`sender_name` varchar(255) NOT NULL,
	`sender_email` varchar(255) NOT NULL,
	`reply_to_email` varchar(255) NOT NULL,
	`shop_domain` varchar(255),
	`webhook_secret` varchar(128),
	`ga4_property_id` varchar(64),
	`ga4_credentials_json` json,
	`footer_text` text NOT NULL,
	`brand_voice` text NOT NULL,
	`wording_style` varchar(512),
	`design_style` varchar(512),
	`company_id` int,
	`imprint_url` varchar(1024) NOT NULL,
	`privacy_url` varchar(1024) NOT NULL,
	`brand_kit_json` json NOT NULL,
	`attribution_model` varchar(32) NOT NULL DEFAULT 'last-click',
	`attribution_lookback_days` int NOT NULL DEFAULT 30,
	`cumas_recipient_field` varchar(128),
	`active_conversion_kinds` json NOT NULL,
	`created_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	`updated_at` bigint NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),
	CONSTRAINT `workspaces_id` PRIMARY KEY(`id`),
	CONSTRAINT `workspaces_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
ALTER TABLE `assets` ADD CONSTRAINT `assets_campaign_id_campaigns_id_fk` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `assets` ADD CONSTRAINT `assets_workspace_id_workspaces_id_fk` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `behavior_events` ADD CONSTRAINT `behavior_events_campaign_id_campaigns_id_fk` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `behavior_events` ADD CONSTRAINT `behavior_events_recipient_id_recipients_id_fk` FOREIGN KEY (`recipient_id`) REFERENCES `recipients`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `behavior_events` ADD CONSTRAINT `behavior_events_variant_id_campaign_variants_id_fk` FOREIGN KEY (`variant_id`) REFERENCES `campaign_variants`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `campaign_variants` ADD CONSTRAINT `campaign_variants_campaign_id_campaigns_id_fk` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `campaign_versions` ADD CONSTRAINT `campaign_versions_campaign_id_campaigns_id_fk` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `campaigns` ADD CONSTRAINT `campaigns_workspace_id_workspaces_id_fk` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `campaigns` ADD CONSTRAINT `campaigns_template_id_templates_id_fk` FOREIGN KEY (`template_id`) REFERENCES `templates`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `campaigns` ADD CONSTRAINT `campaigns_created_by_id_users_id_fk` FOREIGN KEY (`created_by_id`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `email_queue` ADD CONSTRAINT `email_queue_campaign_id_campaigns_id_fk` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `email_queue` ADD CONSTRAINT `email_queue_recipient_id_recipients_id_fk` FOREIGN KEY (`recipient_id`) REFERENCES `recipients`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `email_queue` ADD CONSTRAINT `email_queue_variant_id_campaign_variants_id_fk` FOREIGN KEY (`variant_id`) REFERENCES `campaign_variants`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `recipients` ADD CONSTRAINT `recipients_campaign_id_campaigns_id_fk` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `recipients` ADD CONSTRAINT `recipients_variant_id_campaign_variants_id_fk` FOREIGN KEY (`variant_id`) REFERENCES `campaign_variants`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `section_templates` ADD CONSTRAINT `section_templates_workspace_id_workspaces_id_fk` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `sessions` ADD CONSTRAINT `sessions_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `templates` ADD CONSTRAINT `templates_workspace_id_workspaces_id_fk` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `tracking_events` ADD CONSTRAINT `tracking_events_campaign_id_campaigns_id_fk` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `tracking_events` ADD CONSTRAINT `tracking_events_recipient_id_recipients_id_fk` FOREIGN KEY (`recipient_id`) REFERENCES `recipients`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `tracking_events` ADD CONSTRAINT `tracking_events_variant_id_campaign_variants_id_fk` FOREIGN KEY (`variant_id`) REFERENCES `campaign_variants`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `trigger_logs` ADD CONSTRAINT `trigger_logs_trigger_template_id_trigger_templates_id_fk` FOREIGN KEY (`trigger_template_id`) REFERENCES `trigger_templates`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `trigger_templates` ADD CONSTRAINT `trigger_templates_workspace_id_workspaces_id_fk` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `trigger_templates` ADD CONSTRAINT `trigger_templates_container_campaign_id_campaigns_id_fk` FOREIGN KEY (`container_campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `trigger_templates` ADD CONSTRAINT `trigger_templates_container_variant_id_campaign_variants_id_fk` FOREIGN KEY (`container_variant_id`) REFERENCES `campaign_variants`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `unsubscribes` ADD CONSTRAINT `unsubscribes_campaign_id_campaigns_id_fk` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `assets_prompt_hash` ON `assets` (`prompt_hash`);--> 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 INDEX `variants_campaign` ON `campaign_variants` (`campaign_id`);--> statement-breakpoint
CREATE INDEX `versions_campaign_created` ON `campaign_versions` (`campaign_id`,`created_at`);--> 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 INDEX `recipients_campaign_email` ON `recipients` (`campaign_id`,`email`);--> statement-breakpoint
CREATE INDEX `section_templates_workspace_kind` ON `section_templates` (`workspace_id`,`kind`);--> 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 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`);
