1
CREATE TABLE `users` (
2
	`id` text PRIMARY KEY NOT NULL,
3
	`tessera_sub` text NOT NULL,
4
	`created_at` integer NOT NULL
5
);
6
--> statement-breakpoint
7
CREATE UNIQUE INDEX `users_tessera_sub_unique` ON `users` (`tessera_sub`);--> statement-breakpoint
8
CREATE TABLE `namespaces` (
9
	`id` text PRIMARY KEY NOT NULL,
10
	`slug` text NOT NULL,
11
	`created_by` text NOT NULL,
12
	`created_at` integer NOT NULL,
13
	FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE restrict
14
);
15
--> statement-breakpoint
16
CREATE UNIQUE INDEX `namespaces_slug_unique` ON `namespaces` (`slug`);--> statement-breakpoint
17
CREATE TABLE `namespace_memberships` (
18
	`namespace_id` text NOT NULL,
19
	`user_id` text NOT NULL,
20
	`created_at` integer NOT NULL,
21
	PRIMARY KEY(`namespace_id`, `user_id`),
22
	FOREIGN KEY (`namespace_id`) REFERENCES `namespaces`(`id`) ON UPDATE no action ON DELETE cascade,
23
	FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE cascade
24
);
25
--> statement-breakpoint
26
CREATE INDEX `idx_namespace_memberships_user_ns` ON `namespace_memberships` (`user_id`,`namespace_id`);--> statement-breakpoint
27
CREATE TABLE `repositories` (
28
	`id` text PRIMARY KEY NOT NULL,
29
	`namespace_id` text NOT NULL,
30
	`created_by` text NOT NULL,
31
	`slug` text NOT NULL,
32
	`do_name` text NOT NULL,
33
	`visibility` text NOT NULL,
34
	`created_at` integer NOT NULL,
35
	`updated_at` integer NOT NULL,
36
	FOREIGN KEY (`namespace_id`) REFERENCES `namespaces`(`id`) ON UPDATE no action ON DELETE cascade,
37
	FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE restrict,
38
	CONSTRAINT "chk_repositories_visibility" CHECK("visibility" IN ('public','private'))
39
);
40
--> statement-breakpoint
41
CREATE UNIQUE INDEX `uq_repositories_namespace_slug` ON `repositories` (`namespace_id`,`slug`);--> statement-breakpoint
42
CREATE UNIQUE INDEX `uq_repositories_do_name` ON `repositories` (`do_name`);--> statement-breakpoint
43
CREATE INDEX `idx_repositories_namespace_updated` ON `repositories` (`namespace_id`,"updated_at" desc,`slug`);--> statement-breakpoint
44
CREATE TABLE `personal_access_tokens` (
45
	`id` text PRIMARY KEY NOT NULL,
46
	`user_id` text NOT NULL,
47
	`name` text NOT NULL,
48
	`prefix` text NOT NULL,
49
	`hash` text NOT NULL,
50
	`created_at` integer NOT NULL,
51
	`expires_at` integer,
52
	`revoked_at` integer,
53
	`last_used_at` integer,
54
	FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE cascade
55
);
56
--> statement-breakpoint
57
CREATE UNIQUE INDEX `personal_access_tokens_prefix_unique` ON `personal_access_tokens` (`prefix`);--> statement-breakpoint
58
CREATE INDEX `idx_pats_user_created` ON `personal_access_tokens` (`user_id`,"created_at" desc);--> statement-breakpoint
59
CREATE TABLE `pat_namespace_grants` (
60
	`pat_id` text NOT NULL,
61
	`namespace_id` text NOT NULL,
62
	`level` text NOT NULL,
63
	PRIMARY KEY(`pat_id`, `namespace_id`),
64
	FOREIGN KEY (`pat_id`) REFERENCES `personal_access_tokens`(`id`) ON UPDATE no action ON DELETE cascade,
65
	FOREIGN KEY (`namespace_id`) REFERENCES `namespaces`(`id`) ON UPDATE no action ON DELETE cascade,
66
	CONSTRAINT "chk_pat_namespace_grants_level" CHECK("level" IN ('pull','push'))
67
);
68
--> statement-breakpoint
69
CREATE INDEX `idx_pat_namespace_grants_namespace` ON `pat_namespace_grants` (`namespace_id`);--> statement-breakpoint
70
CREATE TABLE `pat_repo_grants` (
71
	`pat_id` text NOT NULL,
72
	`repo_id` text NOT NULL,
73
	`level` text NOT NULL,
74
	PRIMARY KEY(`pat_id`, `repo_id`),
75
	FOREIGN KEY (`pat_id`) REFERENCES `personal_access_tokens`(`id`) ON UPDATE no action ON DELETE cascade,
76
	FOREIGN KEY (`repo_id`) REFERENCES `repositories`(`id`) ON UPDATE no action ON DELETE cascade,
77
	CONSTRAINT "chk_pat_repo_grants_level" CHECK("level" IN ('pull','push'))
78
);
79
--> statement-breakpoint
80
CREATE INDEX `idx_pat_repo_grants_repo` ON `pat_repo_grants` (`repo_id`);