Establishes the initial database schema using Drizzle ORM, replacing the prior placeholder. This migration introduces core entities like users, spaces, pages, access tokens, and their permissions, complete with relationships and constraints. Updates the authentication flow by securing the sign-in endpoint with JWT validation and extracting user details directly from the token via a new custom parameter decorator, improving user context handling. Integrates a new `Page` module, laying the groundwork for future page management capabilities.
113 lines
4.3 KiB
TypeScript
113 lines
4.3 KiB
TypeScript
import { pgTable, unique, uuid, varchar, boolean, timestamp, foreignKey, text, integer, check } from "drizzle-orm/pg-core"
|
|
import { sql } from "drizzle-orm"
|
|
|
|
|
|
|
|
export const users = pgTable("users", {
|
|
userId: uuid("user_id").defaultRandom().primaryKey().notNull(),
|
|
entraObjectId: varchar("entra_object_id", { length: 36 }).notNull(),
|
|
entraTenantId: varchar("entra_tenant_id", { length: 36 }).notNull(),
|
|
email: varchar({ length: 255 }).notNull(),
|
|
displayName: varchar("display_name", { length: 255 }).notNull(),
|
|
isSystemAdmin: boolean("is_system_admin").default(false),
|
|
isActive: boolean("is_active").default(true),
|
|
createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(),
|
|
updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(),
|
|
lastLoginAt: timestamp("last_login_at", { withTimezone: true, mode: 'string' }),
|
|
}, (table) => [
|
|
unique("users_entra_object_id_key").on(table.entraObjectId),
|
|
]);
|
|
|
|
export const spaces = pgTable("spaces", {
|
|
spaceId: uuid("space_id").defaultRandom().primaryKey().notNull(),
|
|
spaceKey: varchar("space_key", { length: 50 }).notNull(),
|
|
name: varchar({ length: 255 }).notNull(),
|
|
description: text(),
|
|
createdByUserId: uuid("created_by_user_id").notNull(),
|
|
createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(),
|
|
}, (table) => [
|
|
foreignKey({
|
|
columns: [table.createdByUserId],
|
|
foreignColumns: [users.userId],
|
|
name: "spaces_created_by_user_id_fkey"
|
|
}),
|
|
unique("spaces_space_key_key").on(table.spaceKey),
|
|
]);
|
|
|
|
export const pages = pgTable("pages", {
|
|
pageId: uuid("page_id").defaultRandom().primaryKey().notNull(),
|
|
spaceId: uuid("space_id").notNull(),
|
|
parentPageId: uuid("parent_page_id"),
|
|
sortOrder: integer("sort_order").default(0),
|
|
title: varchar({ length: 255 }).notNull(),
|
|
content: text(),
|
|
createdByUserId: uuid("created_by_user_id").notNull(),
|
|
createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(),
|
|
updatedByUserId: uuid("updated_by_user_id").notNull(),
|
|
updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(),
|
|
isDeleted: boolean("is_deleted").default(false),
|
|
}, (table) => [
|
|
foreignKey({
|
|
columns: [table.spaceId],
|
|
foreignColumns: [spaces.spaceId],
|
|
name: "pages_space_id_fkey"
|
|
}).onDelete("cascade"),
|
|
foreignKey({
|
|
columns: [table.parentPageId],
|
|
foreignColumns: [table.pageId],
|
|
name: "pages_parent_page_id_fkey"
|
|
}).onDelete("cascade"),
|
|
foreignKey({
|
|
columns: [table.createdByUserId],
|
|
foreignColumns: [users.userId],
|
|
name: "pages_created_by_user_id_fkey"
|
|
}),
|
|
foreignKey({
|
|
columns: [table.updatedByUserId],
|
|
foreignColumns: [users.userId],
|
|
name: "pages_updated_by_user_id_fkey"
|
|
}),
|
|
]);
|
|
|
|
export const accesstokens = pgTable("accesstokens", {
|
|
tokenId: uuid("token_id").defaultRandom().primaryKey().notNull(),
|
|
tokenValue: text("token_value").notNull(),
|
|
description: varchar({ length: 255 }),
|
|
expiresAt: timestamp("expires_at", { withTimezone: true, mode: 'string' }),
|
|
createdByUserId: uuid("created_by_user_id").notNull(),
|
|
createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(),
|
|
}, (table) => [
|
|
foreignKey({
|
|
columns: [table.createdByUserId],
|
|
foreignColumns: [users.userId],
|
|
name: "accesstokens_created_by_user_id_fkey"
|
|
}),
|
|
unique("accesstokens_token_value_key").on(table.tokenValue),
|
|
]);
|
|
|
|
export const tokenpermissions = pgTable("tokenpermissions", {
|
|
permissionId: uuid("permission_id").defaultRandom().primaryKey().notNull(),
|
|
tokenId: uuid("token_id").notNull(),
|
|
spaceId: uuid("space_id"),
|
|
pageId: uuid("page_id"),
|
|
}, (table) => [
|
|
foreignKey({
|
|
columns: [table.tokenId],
|
|
foreignColumns: [accesstokens.tokenId],
|
|
name: "tokenpermissions_token_id_fkey"
|
|
}).onDelete("cascade"),
|
|
foreignKey({
|
|
columns: [table.spaceId],
|
|
foreignColumns: [spaces.spaceId],
|
|
name: "tokenpermissions_space_id_fkey"
|
|
}).onDelete("cascade"),
|
|
foreignKey({
|
|
columns: [table.pageId],
|
|
foreignColumns: [pages.pageId],
|
|
name: "tokenpermissions_page_id_fkey"
|
|
}).onDelete("cascade"),
|
|
unique("tokenpermissions_token_id_space_id_key").on(table.tokenId, table.spaceId),
|
|
unique("tokenpermissions_token_id_page_id_key").on(table.tokenId, table.pageId),
|
|
check("tokenpermissions_check", sql`((space_id IS NOT NULL) AND (page_id IS NULL)) OR ((space_id IS NULL) AND (page_id IS NOT NULL))`),
|
|
]);
|