1
CREATE TABLE `attachments` (
2
	`id` text PRIMARY KEY NOT NULL,
3
	`email_id` text NOT NULL,
4
	`filename` text,
5
	`content_type` text,
6
	`size_bytes` integer DEFAULT 0,
7
	`storage_key` text NOT NULL,
8
	FOREIGN KEY (`email_id`) REFERENCES `emails`(`id`) ON UPDATE no action ON DELETE cascade
9
);
10
--> statement-breakpoint
11
CREATE INDEX `idx_attachments_email` ON `attachments` (`email_id`);--> statement-breakpoint
12
CREATE TABLE `domains` (
13
	`id` text PRIMARY KEY NOT NULL,
14
	`domain` text NOT NULL,
15
	`is_active` integer DEFAULT true NOT NULL,
16
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL
17
);
18
--> statement-breakpoint
19
CREATE UNIQUE INDEX `domains_domain_unique` ON `domains` (`domain`);--> statement-breakpoint
20
CREATE INDEX `idx_domains_active` ON `domains` (`is_active`);--> statement-breakpoint
21
CREATE TABLE `emails` (
22
	`id` text PRIMARY KEY NOT NULL,
23
	`inbox_id` text NOT NULL,
24
	`recipient_address` text DEFAULT '' NOT NULL,
25
	`from_address` text NOT NULL,
26
	`from_name` text,
27
	`subject` text DEFAULT '(no subject)',
28
	`received_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
29
	`is_read` integer DEFAULT false NOT NULL,
30
	`size_bytes` integer DEFAULT 0,
31
	`has_attachments` integer DEFAULT false NOT NULL,
32
	`body_key` text,
33
	FOREIGN KEY (`inbox_id`) REFERENCES `inboxes`(`id`) ON UPDATE no action ON DELETE cascade
34
);
35
--> statement-breakpoint
36
CREATE INDEX `idx_emails_inbox_received_id` ON `emails` (`inbox_id`,`received_at`,`id`);--> statement-breakpoint
37
CREATE TABLE `inboxes` (
38
	`id` text PRIMARY KEY NOT NULL,
39
	`local_part` text NOT NULL,
40
	`domain` text NOT NULL,
41
	`full_address` text NOT NULL,
42
	`is_permanent` integer DEFAULT false NOT NULL,
43
	`created_at` integer DEFAULT (unixepoch() * 1000) NOT NULL,
44
	`expires_at` integer
45
);
46
--> statement-breakpoint
47
CREATE UNIQUE INDEX `inboxes_full_address_unique` ON `inboxes` (`full_address`);--> statement-breakpoint
48
CREATE UNIQUE INDEX `idx_inboxes_local_domain` ON `inboxes` (`local_part`,`domain`);--> statement-breakpoint
49
CREATE INDEX `idx_inboxes_domain` ON `inboxes` (`domain`);--> statement-breakpoint
50
CREATE INDEX `idx_inboxes_permanent_expires` ON `inboxes` (`is_permanent`,`expires_at`);--> statement-breakpoint
51
CREATE INDEX `idx_inboxes_permanent_created` ON `inboxes` (`is_permanent`,`created_at`);--> statement-breakpoint
52
CREATE INDEX `idx_inboxes_permanent_domain_local` ON `inboxes` (`is_permanent`,`domain`,`local_part`);