all repos — stealth-developers @ 0ad474abcf4c9b5950a6297e3403fb7537b0186b

src/database/schema/tickets.ts (view raw)

 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
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
 100
 101
 102
 103
 104
 105
 106
 107
 108
 109
 110
 111
 112
 113
 114
 115
 116
 117
 118
 119
 
import { defineRelations, sql } from "drizzle-orm";
import { index, integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const tickets = sqliteTable(
	"tickets",
	{
		id: integer("id").primaryKey({ autoIncrement: true }),
		guildId: text("guild_id").notNull(),
		channelId: text("channel_id"),

		authorId: text("author_id").notNull(),
		anonymousId: text("anonymous_id").notNull(),

		type: text("type").notNull(),
		status: text("status", { enum: ["open", "closed", "archived"] })
			.default("open")
			.notNull(),

		topic: text("topic"),

		createdAt: integer("created_at", { mode: "timestamp" })
			.default(sql`(unixepoch())`)
			.notNull(),
		closedAt: integer("closed_at", { mode: "timestamp" }),
	},
	(t) => [
		index("tickets_guild_idx").on(t.guildId),
		index("tickets_author_idx").on(t.authorId),
		index("tickets_status_idx").on(t.status),
	],
);

export const ticketMessages = sqliteTable(
	"ticket_messages",
	{
		id: integer("id").primaryKey({ autoIncrement: true }),
		ticketId: integer("ticket_id")
			.references(() => tickets.id, { onDelete: "cascade" })
			.notNull(),

		authorId: text("author_id").notNull(),

		authorType: text("author_type", { enum: ["user", "staff", "system"] })
			.default("user")
			.notNull(),

		content: text("content").notNull(),

		createdAt: integer("created_at", { mode: "timestamp" })
			.default(sql`(unixepoch())`)
			.notNull(),
	},
	(t) => [index("ticket_msgs_ticket_idx").on(t.ticketId)],
);

export const ticketAttachments = sqliteTable("ticket_attachments", {
	id: text("id").primaryKey(),
	ticketId: integer("ticket_id")
		.references(() => tickets.id, { onDelete: "cascade" })
		.notNull(),
	messageId: integer("message_id").references(() => ticketMessages.id, {
		onDelete: "cascade",
	}),

	fileName: text("file_name").notNull(),
	contentType: text("content_type").notNull(),
	size: integer("size").notNull(),

	bucket: text("bucket").notNull(),
	key: text("key").notNull(),
	url: text("url").notNull(),

	uploadedAt: integer("uploaded_at", { mode: "timestamp" })
		.default(sql`(unixepoch())`)
		.notNull(),
});

export const TicketRelations = defineRelations(
	{
		tickets,
		ticketMessages,
		ticketAttachments,
	},
	(r) => ({
		tickets: {
			messages: r.many.ticketMessages({
				from: r.tickets.id,
				to: r.ticketMessages.ticketId,
			}),
			attachments: r.many.ticketAttachments({
				from: r.tickets.id,
				to: r.ticketAttachments.ticketId,
			}),
		},
		ticketMessages: {
			ticket: r.one.tickets({
				from: r.ticketMessages.ticketId,
				to: r.tickets.id,
			}),
			attachments: r.many.ticketAttachments({
				from: r.ticketMessages.id,
				to: r.ticketAttachments.messageId,
			}),
		},
		ticketAttachments: {
			ticket: r.one.tickets({
				from: r.ticketAttachments.ticketId,
				to: r.tickets.id,
			}),
			message: r.one.ticketMessages({
				from: r.ticketAttachments.messageId,
				to: r.ticketMessages.id,
			}),
		},
	}),
);

export type Ticket = typeof tickets.$inferSelect;
export type TicketMessage = typeof ticketMessages.$inferSelect;