spb/autoscholar

1import { boolean, index, inet, integer, jsonb, pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";23export const adminUsers = pgTable("admin_users", {4  id: uuid("id").primaryKey().defaultRandom(),5  username: text("username").notNull().unique(),6  passwordHash: text("password_hash").notNull(),7  mustChangePassword: boolean("must_change_password").notNull().default(true),8  bootstrapped: boolean("bootstrapped").notNull().default(false),9  totpSecret: text("totp_secret"),10  failedAttempts: integer("failed_attempts").notNull().default(0),11  lockedUntil: timestamp("locked_until", { withTimezone: true }),12  lastLoginAt: timestamp("last_login_at", { withTimezone: true }),13  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),14  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),15});1617export const sessions = pgTable(18  "sessions",19  {20    id: uuid("id").primaryKey().defaultRandom(),21    tokenHash: text("token_hash").notNull().unique(),22    userId: uuid("user_id")23      .notNull()24      .references(() => adminUsers.id, { onDelete: "cascade" }),25    csrfSecret: text("csrf_secret").notNull(),26    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),27    expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),28    lastSeenAt: timestamp("last_seen_at", { withTimezone: true }).notNull().defaultNow(),29    rotatedFrom: uuid("rotated_from"),30    ip: inet("ip"),31    userAgent: text("user_agent"),32    revokedAt: timestamp("revoked_at", { withTimezone: true }),33  },34  (t) => [index("sessions_user_idx").on(t.userId), index("sessions_expires_idx").on(t.expiresAt)],35);3637export const passwordChanges = pgTable("password_changes", {38  id: uuid("id").primaryKey().defaultRandom(),39  userId: uuid("user_id")40    .notNull()41    .references(() => adminUsers.id, { onDelete: "cascade" }),42  previousHash: text("previous_hash").notNull(),43  reason: text("reason").notNull(),44  ip: inet("ip"),45  changedAt: timestamp("changed_at", { withTimezone: true }).notNull().defaultNow(),46});4748export const securityEvents = pgTable(49  "security_events",50  {51    id: uuid("id").primaryKey().defaultRandom(),52    userId: uuid("user_id"),53    kind: text("kind").notNull(),54    ip: inet("ip"),55    detail: jsonb("detail").$type<Record<string, unknown>>().notNull().default({}),56    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),57  },58  (t) => [index("security_events_kind_idx").on(t.kind, t.createdAt)],59);6061export const auditEvents = pgTable(62  "audit_events",63  {64    id: uuid("id").primaryKey().defaultRandom(),65    actorType: text("actor_type").notNull(), // admin | worker | system66    actorId: text("actor_id"),67    action: text("action").notNull(),68    targetType: text("target_type"),69    targetId: text("target_id"),70    detail: jsonb("detail").$type<Record<string, unknown>>().notNull().default({}),71    ip: inet("ip"),72    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),73  },74  (t) => [75    index("audit_events_target_idx").on(t.targetType, t.targetId),76    index("audit_events_created_idx").on(t.createdAt),77  ],78);7980export const loginAttempts = pgTable(81  "login_attempts",82  {83    id: uuid("id").primaryKey().defaultRandom(),84    username: text("username").notNull(),85    ip: inet("ip"),86    success: boolean("success").notNull(),87    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),88  },89  (t) => [90    index("login_attempts_ip_idx").on(t.ip, t.createdAt),91    index("login_attempts_user_idx").on(t.username, t.createdAt),92  ],93);94