all repos — stealth-developers @ 1af6d44502a1ec892925954c311fb0a2560f9bba

pkgs/db/src/schemas/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
 120
 
import * as s from "drizzle-orm/sqlite-core";
import { actors } from "./actors";

export const ticket = s.sqliteTable("tickets", {
	id: s.integer("id").primaryKey({ autoIncrement: true }),
	anonymousId: s.text("anonymous_id").notNull(),

	status: s.text("status").notNull(),
	channelId: s.text("channel_id").notNull(),

	closeReason: s.text("close_reason"),
	privateNotes: s.text("private_notes"),

	openedAt: s.integer("opened_at", { mode: "timestamp" }).notNull(),
	closedAt: s.integer("closed_at", { mode: "timestamp" }),

	/** the actor that opened the ticket, may be the same as the subject */
	openedBy: s
		.integer("opened_by")
		.notNull()
		.references(() => actors.id),
	openReason: s.text("open_reason"),

	/** the actor who is the subject of the ticket */
	subject: s
		.integer("subject")
		.notNull()
		.references(() => actors.id),

	claimedBy: s.integer("claimed_by").references(() => actors.id),
	claimedAt: s.integer("claimed_at", { mode: "timestamp" }),

	thankedAt: s.integer("thanked_at", { mode: "timestamp" }),
	warnedAt: s.integer("warned_at", { mode: "timestamp" }),
	lastMessageAt: s.integer("last_message_at", { mode: "timestamp" }),

	hasModeratorMessage: s.integer("has_moderator_message", { mode: "boolean" }),
	hasAuthorMessage: s.integer("has_author_message", { mode: "boolean" }),
});

export const ticketParticipants = s.sqliteTable(
	"ticket_participants",
	{
		id: s.integer("id").primaryKey({ autoIncrement: true }),
		ticketId: s
			.integer("ticket_id")
			.notNull()
			.references(() => ticket.id, { onDelete: "cascade" }),
		actorId: s
			.integer("actor_id")
			.notNull()
			.references(() => actors.id, { onDelete: "cascade" }),

		role: s.text("role", { enum: ["participant", "moderator", "subject"] }).notNull(),
		reason: s.text("reason"),

		createdAt: s.integer("created_at", { mode: "timestamp" }).notNull(),
	},
	(table) => [
		s.index("ticket_participants_ticket_idx").on(table.ticketId),
		s.index("ticket_participants_actor_idx").on(table.actorId),
		s.index("ticket_participants_role_idx").on(table.role),
	],
);

export const ticketMessages = s.sqliteTable(
	"ticket_messages",
	{
		id: s.integer("id").primaryKey({ autoIncrement: true }),
		publicId: s.text("public_id").notNull(),
		messageId: s.text("message_id"),

		ticketId: s
			.integer("ticket_id")
			.notNull()
			.references(() => ticket.id, { onDelete: "cascade" }),
		actorId: s
			.integer("actor_id")
			.notNull()
			.references(() => actors.id, { onDelete: "cascade" }),

		content: s.text("content").notNull(),
		actorRole: s.text("actor_role", { enum: ["participant", "moderator", "subject"] }).notNull(),

		createdAt: s.integer("created_at", { mode: "timestamp" }).notNull(),
		editedAt: s.integer("edited_at", { mode: "timestamp" }),
		deletedAt: s.integer("deleted_at", { mode: "timestamp" }),
	},
	(table) => [
		s.index("ticket_messages_ticket_idx").on(table.ticketId),
		s.index("ticket_messages_actor_idx").on(table.actorId),
		s.index("ticket_messages_public_id_idx").on(table.publicId),
	],
);

export const ticketAttachments = s.sqliteTable(
	"ticket_attachments",
	{
		id: s.integer("id").primaryKey({ autoIncrement: true }),
		publicId: s.text("public_id").notNull(),
		messageId: s.integer("message_id").references(() => ticketMessages.id, { onDelete: "cascade" }),
		ticketId: s
			.integer("ticket_id")
			.notNull()
			.references(() => ticket.id, { onDelete: "cascade" }),
		actorId: s
			.integer("actor_id")
			.notNull()
			.references(() => actors.id, { onDelete: "cascade" }),

		fileName: s.text("file_name").notNull(),
		fileType: s.text("file_type").notNull(),
		fileSize: s.integer("file_size").notNull(),
	},
	(table) => [
		s.index("ticket_attachments_ticket_idx").on(table.ticketId),
		s.index("ticket_attachments_actor_idx").on(table.actorId),
		s.index("ticket_attachments_public_id_idx").on(table.publicId),
	],
);