1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
CREATE TABLE `categories` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`name` text NOT NULL,
`slug` text NOT NULL,
`description` text,
`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL
);
--> statement-breakpoint
CREATE TABLE `comments` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`content` text NOT NULL,
`is_internal` integer DEFAULT false NOT NULL,
`ticket_id` integer NOT NULL,
`author_id` integer NOT NULL,
`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON UPDATE no action ON DELETE cascade,
FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE restrict
);
--> statement-breakpoint
CREATE TABLE `tickets` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`title` text NOT NULL,
`description` text NOT NULL,
`status` text DEFAULT 'open' NOT NULL,
`priority` text DEFAULT 'medium' NOT NULL,
`creator_id` integer NOT NULL,
`assignee_id` integer,
`category_id` integer,
`due_at` text,
`resolved_at` text,
`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
FOREIGN KEY (`creator_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE restrict,
FOREIGN KEY (`assignee_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE set null,
FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE TABLE `users` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`name` text NOT NULL,
`email` text NOT NULL,
`password` text NOT NULL,
`role` text DEFAULT 'requester' NOT NULL,
`is_active` integer DEFAULT true NOT NULL,
`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `categories_slug_unique_idx` ON `categories` (`slug`);--> statement-breakpoint
CREATE INDEX `comments_ticket_id_idx` ON `comments` (`ticket_id`);--> statement-breakpoint
CREATE INDEX `comments_author_id_idx` ON `comments` (`author_id`);--> statement-breakpoint
CREATE INDEX `tickets_status_idx` ON `tickets` (`status`);--> statement-breakpoint
CREATE INDEX `tickets_priority_idx` ON `tickets` (`priority`);--> statement-breakpoint
CREATE INDEX `tickets_assignee_id_idx` ON `tickets` (`assignee_id`);--> statement-breakpoint
CREATE INDEX `tickets_creator_id_idx` ON `tickets` (`creator_id`);--> statement-breakpoint
CREATE INDEX `tickets_category_id_idx` ON `tickets` (`category_id`);--> statement-breakpoint
CREATE INDEX `tickets_status_assignee_idx` ON `tickets` (`status`,`assignee_id`);--> statement-breakpoint
CREATE UNIQUE INDEX `users_email_unique_idx` ON `users` (`email`);--> statement-breakpoint
CREATE INDEX `users_role_idx` ON `users` (`role`);
|
