1
import { index, integer, sqliteTable, text, uniqueIndex } from "drizzle-orm/sqlite-core";
2
 
3
import type { DavScope, JsonObject } from "@/worker/db/types";
4
 
5
export const controlPlaneMeta = sqliteTable("control_plane_meta", {
6
  key: text("key").primaryKey(),
7
  value: text("value").notNull(),
8
  updatedAtMs: integer("updated_at_ms").notNull(),
9
});
10
 
11
export const subjects = sqliteTable(
12
  "subjects",
13
  {
14
    id: text("id").primaryKey(),
15
    storageId: text("storage_id").notNull(),
16
    hostLabel: text("host_label").notNull(),
17
    email: text("email"),
18
    displayName: text("display_name"),
19
    createdAtMs: integer("created_at_ms").notNull(),
20
    lastLoginAtMs: integer("last_login_at_ms"),
21
    hostRotatedAtMs: integer("host_rotated_at_ms"),
22
    disabledAtMs: integer("disabled_at_ms"),
23
  },
24
  (table) => [
25
    uniqueIndex("subjects_storage_id_unique").on(table.storageId),
26
    uniqueIndex("subjects_host_label_unique").on(table.hostLabel),
27
    index("subjects_disabled_idx").on(table.disabledAtMs),
28
  ],
29
);
30
 
31
export const patProjections = sqliteTable(
32
  "pat_projections",
33
  {
34
    id: text("id").primaryKey(),
35
    subjectId: text("subject_id")
36
      .notNull()
37
      .references(() => subjects.id, { onDelete: "cascade" }),
38
    name: text("name").notNull(),
39
    scopes: text("scopes_json", { mode: "json" }).notNull().$type<DavScope[]>(),
40
    createdAtMs: integer("created_at_ms").notNull(),
41
    expiresAtMs: integer("expires_at_ms"),
42
    revokedAtMs: integer("revoked_at_ms"),
43
    lastUsedAtMs: integer("last_used_at_ms"),
44
  },
45
  (table) => [
46
    index("pat_projections_subject_created_idx").on(table.subjectId, table.createdAtMs),
47
    index("pat_projections_subject_active_idx").on(table.subjectId, table.revokedAtMs, table.expiresAtMs),
48
  ],
49
);
50
 
51
export const auditEvents = sqliteTable(
52
  "audit_events",
53
  {
54
    id: text("id").primaryKey(),
55
    subjectId: text("subject_id").references(() => subjects.id, { onDelete: "set null" }),
56
    eventType: text("event_type").notNull(),
57
    actorSubjectId: text("actor_subject_id"),
58
    createdAtMs: integer("created_at_ms").notNull(),
59
    data: text("data_json", { mode: "json" }).notNull().$type<JsonObject>(),
60
  },
61
  (table) => [
62
    index("audit_events_subject_created_idx").on(table.subjectId, table.createdAtMs),
63
    index("audit_events_type_created_idx").on(table.eventType, table.createdAtMs),
64
    index("audit_events_actor_created_idx").on(table.actorSubjectId, table.createdAtMs),
65
  ],
66
);
67
 
68
export type ControlPlaneMetaRow = typeof controlPlaneMeta.$inferSelect;
69
export type NewControlPlaneMetaRow = typeof controlPlaneMeta.$inferInsert;
70
export type SubjectRow = typeof subjects.$inferSelect;
71
export type NewSubjectRow = typeof subjects.$inferInsert;
72
export type PatProjectionRow = typeof patProjections.$inferSelect;
73
export type NewPatProjectionRow = typeof patProjections.$inferInsert;
74
export type AuditEventRow = typeof auditEvents.$inferSelect;
75
export type NewAuditEventRow = typeof auditEvents.$inferInsert;