1
CREATE TABLE `doc_snapshots` (
2
	`page_id` text PRIMARY KEY NOT NULL,
3
	`yjs_state` blob NOT NULL,
4
	`snapshot_at` text DEFAULT (datetime('now')) NOT NULL,
5
	FOREIGN KEY (`page_id`) REFERENCES `pages`(`id`) ON UPDATE no action ON DELETE no action
6
);
7
--> statement-breakpoint
8
CREATE TABLE `invites` (
9
	`id` text PRIMARY KEY NOT NULL,
10
	`email` text,
11
	`workspace_id` text NOT NULL,
12
	`invited_by` text NOT NULL,
13
	`role` text DEFAULT 'member' NOT NULL,
14
	`token` text NOT NULL,
15
	`accepted_at` text,
16
	`accepted_by` text,
17
	`revoked_at` text,
18
	`expires_at` text NOT NULL,
19
	`created_at` text DEFAULT (datetime('now')) NOT NULL,
20
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action,
21
	FOREIGN KEY (`invited_by`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
22
	FOREIGN KEY (`accepted_by`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
23
);
24
--> statement-breakpoint
25
CREATE UNIQUE INDEX `invites_token_unique` ON `invites` (`token`);--> statement-breakpoint
26
CREATE INDEX `idx_invites_token` ON `invites` (`token`);--> statement-breakpoint
27
CREATE INDEX `idx_invites_email` ON `invites` (`email`);--> statement-breakpoint
28
CREATE TABLE `memberships` (
29
	`user_id` text NOT NULL,
30
	`workspace_id` text NOT NULL,
31
	`role` text NOT NULL,
32
	`joined_at` text DEFAULT (datetime('now')) NOT NULL,
33
	PRIMARY KEY(`user_id`, `workspace_id`),
34
	FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
35
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action
36
);
37
--> statement-breakpoint
38
CREATE TABLE `page_shares` (
39
	`id` text PRIMARY KEY NOT NULL,
40
	`page_id` text NOT NULL,
41
	`grantee_type` text NOT NULL,
42
	`grantee_id` text,
43
	`permission` text NOT NULL,
44
	`link_token` text,
45
	`created_by` text NOT NULL,
46
	`created_at` text DEFAULT (datetime('now')) NOT NULL,
47
	FOREIGN KEY (`page_id`) REFERENCES `pages`(`id`) ON UPDATE no action ON DELETE no action,
48
	FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
49
);
50
--> statement-breakpoint
51
CREATE UNIQUE INDEX `page_shares_link_token_unique` ON `page_shares` (`link_token`);--> statement-breakpoint
52
CREATE INDEX `idx_page_shares_page` ON `page_shares` (`page_id`);--> statement-breakpoint
53
CREATE INDEX `idx_page_shares_grantee` ON `page_shares` (`grantee_type`,`grantee_id`);--> statement-breakpoint
54
CREATE TABLE `pages` (
55
	`id` text PRIMARY KEY NOT NULL,
56
	`workspace_id` text NOT NULL,
57
	`parent_id` text,
58
	`title` text DEFAULT 'Untitled' NOT NULL,
59
	`icon` text,
60
	`cover_url` text,
61
	`position` real NOT NULL,
62
	`created_by` text NOT NULL,
63
	`created_at` text DEFAULT (datetime('now')) NOT NULL,
64
	`updated_at` text DEFAULT (datetime('now')) NOT NULL,
65
	`archived_at` text,
66
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action,
67
	FOREIGN KEY (`parent_id`) REFERENCES `pages`(`id`) ON UPDATE no action ON DELETE set null,
68
	FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
69
);
70
--> statement-breakpoint
71
CREATE INDEX `idx_pages_parent` ON `pages` (`workspace_id`,`parent_id`,`position`);--> statement-breakpoint
72
CREATE INDEX `idx_pages_workspace` ON `pages` (`workspace_id`,`archived_at`);--> statement-breakpoint
73
CREATE TABLE `uploads` (
74
	`id` text PRIMARY KEY NOT NULL,
75
	`workspace_id` text NOT NULL,
76
	`page_id` text,
77
	`uploaded_by` text NOT NULL,
78
	`filename` text NOT NULL,
79
	`content_type` text NOT NULL,
80
	`size_bytes` integer NOT NULL,
81
	`r2_key` text NOT NULL,
82
	`created_at` text DEFAULT (datetime('now')) NOT NULL,
83
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action,
84
	FOREIGN KEY (`page_id`) REFERENCES `pages`(`id`) ON UPDATE no action ON DELETE no action,
85
	FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
86
);
87
--> statement-breakpoint
88
CREATE TABLE `users` (
89
	`id` text PRIMARY KEY NOT NULL,
90
	`email` text NOT NULL,
91
	`password_hash` text NOT NULL,
92
	`name` text NOT NULL,
93
	`avatar_url` text,
94
	`created_at` text DEFAULT (datetime('now')) NOT NULL,
95
	`updated_at` text DEFAULT (datetime('now')) NOT NULL
96
);
97
--> statement-breakpoint
98
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);--> statement-breakpoint
99
CREATE TABLE `workspaces` (
100
	`id` text PRIMARY KEY NOT NULL,
101
	`name` text NOT NULL,
102
	`slug` text NOT NULL,
103
	`icon` text,
104
	`owner_id` text NOT NULL,
105
	`created_at` text DEFAULT (datetime('now')) NOT NULL,
106
	FOREIGN KEY (`owner_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
107
);
108
--> statement-breakpoint
109
CREATE UNIQUE INDEX `workspaces_slug_unique` ON `workspaces` (`slug`);