1
import { sql } from "drizzle-orm";
2
import {
3
  sqliteTable,
4
  text,
5
  integer,
6
  real,
7
  primaryKey,
8
  index,
9
  uniqueIndex,
10
  type AnySQLiteColumn,
11
} from "drizzle-orm/sqlite-core";
12
 
13
export const users = sqliteTable("users", {
14
  id: text("id").primaryKey(),
15
  email: text("email").unique().notNull(),
16
  name: text("name").notNull(),
17
  avatar_url: text("avatar_url"),
18
  created_at: text("created_at")
19
    .notNull()
20
    .default(sql`(datetime('now'))`),
21
  updated_at: text("updated_at")
22
    .notNull()
23
    .default(sql`(datetime('now'))`),
24
});
25
 
26
export const tesseraIdentities = sqliteTable(
27
  "tessera_identities",
28
  {
29
    sub: text("sub").primaryKey(),
30
    user_id: text("user_id")
31
      .notNull()
32
      .references(() => users.id, { onDelete: "cascade" }),
33
    created_at: text("created_at")
34
      .notNull()
35
      .default(sql`(datetime('now'))`),
36
    last_seen_at: text("last_seen_at"),
37
  },
38
  (table) => [uniqueIndex("idx_tessera_identities_user_id").on(table.user_id)],
39
);
40
 
41
export const workspaces = sqliteTable("workspaces", {
42
  id: text("id").primaryKey(),
43
  name: text("name").notNull(),
44
  slug: text("slug").unique().notNull(),
45
  icon: text("icon"),
46
  owner_id: text("owner_id")
47
    .notNull()
48
    .references(() => users.id),
49
  created_at: text("created_at")
50
    .notNull()
51
    .default(sql`(datetime('now'))`),
52
});
53
 
54
export const memberships = sqliteTable(
55
  "memberships",
56
  {
57
    user_id: text("user_id")
58
      .notNull()
59
      .references(() => users.id),
60
    workspace_id: text("workspace_id")
61
      .notNull()
62
      .references(() => workspaces.id),
63
    role: text("role", { enum: ["owner", "admin", "member", "guest"] }).notNull(),
64
    joined_at: text("joined_at")
65
      .notNull()
66
      .default(sql`(datetime('now'))`),
67
  },
68
  (table) => [primaryKey({ columns: [table.user_id, table.workspace_id] })],
69
);
70
 
71
export const invites = sqliteTable(
72
  "invites",
73
  {
74
    id: text("id").primaryKey(),
75
    email: text("email"),
76
    workspace_id: text("workspace_id")
77
      .notNull()
78
      .references(() => workspaces.id),
79
    invited_by: text("invited_by")
80
      .notNull()
81
      .references(() => users.id),
82
    role: text("role", { enum: ["admin", "member", "guest"] })
83
      .notNull()
84
      .default("member"),
85
    token: text("token").unique().notNull(),
86
    accepted_at: text("accepted_at"),
87
    accepted_by: text("accepted_by").references(() => users.id),
88
    revoked_at: text("revoked_at"),
89
    expires_at: text("expires_at").notNull(),
90
    created_at: text("created_at")
91
      .notNull()
92
      .default(sql`(datetime('now'))`),
93
  },
94
  (table) => [index("idx_invites_token").on(table.token), index("idx_invites_email").on(table.email)],
95
);
96
 
97
export const pages = sqliteTable(
98
  "pages",
99
  {
100
    id: text("id").primaryKey(),
101
    workspace_id: text("workspace_id")
102
      .notNull()
103
      .references(() => workspaces.id),
104
    parent_id: text("parent_id").references((): AnySQLiteColumn => pages.id, {
105
      onDelete: "set null",
106
    }),
107
    kind: text("kind", { enum: ["doc", "canvas"] })
108
      .notNull()
109
      .default("doc"),
110
    title: text("title").notNull().default("Untitled"),
111
    icon: text("icon"),
112
    cover_url: text("cover_url"),
113
    position: real("position").notNull(),
114
    created_by: text("created_by")
115
      .notNull()
116
      .references(() => users.id),
117
    created_at: text("created_at")
118
      .notNull()
119
      .default(sql`(datetime('now'))`),
120
    updated_at: text("updated_at")
121
      .notNull()
122
      .default(sql`(datetime('now'))`),
123
    archived_at: text("archived_at"),
124
    archive_root_id: text("archive_root_id"),
125
  },
126
  (table) => [
127
    index("idx_pages_parent").on(table.workspace_id, table.parent_id, table.position),
128
    index("idx_pages_workspace").on(table.workspace_id, table.archived_at),
129
    index("idx_pages_archive_operation").on(table.workspace_id, table.archive_root_id, table.archived_at),
130
    index("idx_pages_trash_roots")
131
      .on(table.workspace_id, table.archived_at)
132
      .where(sql`${table.archived_at} IS NOT NULL AND ${table.archive_root_id} = ${table.id}`),
133
  ],
134
);
135
 
136
export const pageShares = sqliteTable(
137
  "page_shares",
138
  {
139
    id: text("id").primaryKey(),
140
    page_id: text("page_id")
141
      .notNull()
142
      .references(() => pages.id),
143
    grantee_type: text("grantee_type", {
144
      enum: ["user", "link"],
145
    }).notNull(),
146
    grantee_id: text("grantee_id"),
147
    permission: text("permission", {
148
      enum: ["view", "edit"],
149
    }).notNull(),
150
    link_token: text("link_token").unique(),
151
    created_by: text("created_by")
152
      .notNull()
153
      .references(() => users.id),
154
    created_at: text("created_at")
155
      .notNull()
156
      .default(sql`(datetime('now'))`),
157
  },
158
  (table) => [
159
    index("idx_page_shares_page").on(table.page_id),
160
    index("idx_page_shares_grantee").on(table.grantee_type, table.grantee_id),
161
  ],
162
);
163
 
164
export const workspaceSites = sqliteTable("workspace_sites", {
165
  workspace_id: text("workspace_id")
166
    .primaryKey()
167
    .references(() => workspaces.id, { onDelete: "cascade" }),
168
  slug: text("slug").notNull().unique(),
169
  home_page_id: text("home_page_id").references((): AnySQLiteColumn => pages.id, {
170
    onDelete: "set null",
171
  }),
172
  published_at: text("published_at"),
173
  created_at: text("created_at")
174
    .notNull()
175
    .default(sql`(datetime('now'))`),
176
  updated_at: text("updated_at")
177
    .notNull()
178
    .default(sql`(datetime('now'))`),
179
});
180
 
181
export const publishedPages = sqliteTable(
182
  "published_pages",
183
  {
184
    workspace_id: text("workspace_id")
185
      .notNull()
186
      .references(() => workspaces.id, { onDelete: "cascade" }),
187
    page_id: text("page_id")
188
      .notNull()
189
      .references(() => pages.id, { onDelete: "cascade" }),
190
    published_by: text("published_by")
191
      .notNull()
192
      .references(() => users.id),
193
    published_at: text("published_at")
194
      .notNull()
195
      .default(sql`(datetime('now'))`),
196
  },
197
  (table) => [
198
    primaryKey({ columns: [table.workspace_id, table.page_id] }),
199
    index("idx_published_pages_page").on(table.page_id),
200
  ],
201
);
202
 
203
export const uploads = sqliteTable("uploads", {
204
  id: text("id").primaryKey(),
205
  workspace_id: text("workspace_id")
206
    .notNull()
207
    .references(() => workspaces.id),
208
  page_id: text("page_id").references(() => pages.id),
209
  uploaded_by: text("uploaded_by")
210
    .notNull()
211
    .references(() => users.id),
212
  filename: text("filename").notNull(),
213
  content_type: text("content_type").notNull(),
214
  size_bytes: integer("size_bytes").notNull(),
215
  r2_key: text("r2_key").notNull(),
216
  created_at: text("created_at")
217
    .notNull()
218
    .default(sql`(datetime('now'))`),
219
});