1
import { and, desc, eq, gt, inArray, or, sql } from "drizzle-orm";
2
 
3
import { randomHex } from "@/worker/auth/bytes";
4
import { addressbookHref as bookHref, addressObjectHref as objectHref } from "@/worker/carddav/paths";
5
import type { CardDavFilterTest, CardDavSearchFilter } from "@/worker/carddav/types";
6
import type { ParsedVCard } from "@/worker/carddav/vcard";
7
import {
8
  davSyncToken,
9
  objectNameFromCollectionHref,
10
  parseDavSyncToken,
11
  parseDavSyncTokenStrict,
12
  validDavCollectionName,
13
} from "@/worker/db/dav-collections";
14
import type { CardDavDoDb } from "@/worker/db/card-dav-do/client";
15
import {
16
  addressbooks,
17
  addressChanges,
18
  addressDeadProps,
19
  addressIndex,
20
  addressObjects,
21
  cardMeta,
22
  type AddressbookRow,
23
  type AddressDeadPropRow,
24
  type AddressIndexRow,
25
  type AddressObjectRow,
26
} from "@/worker/db/card-dav-do/schema";
27
import type { DavChangeType } from "@/worker/db/types";
28
 
29
export { createCardDavDoDb } from "./client";
30
 
31
export interface CardDavResource {
32
  kind: "home" | "addressbook" | "object";
33
  href: string;
34
  id: string;
35
  syncToken?: string;
36
  book?: AddressbookRow;
37
  object?: AddressObjectRow;
38
  index?: AddressIndexRow;
39
  deadProps: AddressDeadPropRow[];
40
}
41
 
42
function addressbookId(): string {
43
  return `abook_${randomHex(16)}`;
44
}
45
 
46
export function addressObjectId(): string {
47
  return `addr_${randomHex(16)}`;
48
}
49
 
50
export function validAddressbookName(name: string): boolean {
51
  return validDavCollectionName(name);
52
}
53
 
54
export function addressSyncToken(seq: number): string {
55
  return davSyncToken(seq);
56
}
57
 
58
export function addressIndexRow(
59
  objectId: string,
60
  addressbookId: string,
61
  parsed: ParsedVCard,
62
): typeof addressIndex.$inferInsert {
63
  return {
64
    objectId,
65
    addressbookId,
66
    uid: parsed.uid,
67
    fn: parsed.fn,
68
    nFamily: parsed.nFamily,
69
    nGiven: parsed.nGiven,
70
    org: parsed.org,
71
    emails: parsed.emails,
72
    tels: parsed.tels,
73
  };
74
}
75
 
76
export function ensureDefaultAddressbook(db: CardDavDoDb, subjectId: string, nowMs: number): AddressbookRow {
77
  const existing = addressbookByName(db, "default");
78
  if (existing) return existing;
79
  db.insert(cardMeta).values({ key: "subject_id", value: subjectId, updatedAtMs: nowMs }).onConflictDoNothing().run();
80
  db.insert(addressbooks)
81
    .values({
82
      id: addressbookId(),
83
      name: "default",
84
      displayName: "Default",
85
      description: null,
86
      createdAtMs: nowMs,
87
      modifiedAtMs: nowMs,
88
      syncSeq: 0,
89
    })
90
    .run();
91
  return addressbookByName(db, "default")!;
92
}
93
 
94
export function ensureAddressbook(
95
  db: CardDavDoDb,
96
  subjectId: string,
97
  nowMs: number,
98
  bookName: string,
99
): AddressbookRow | null {
100
  if (bookName === "default") return ensureDefaultAddressbook(db, subjectId, nowMs);
101
  return addressbookByName(db, bookName) ?? null;
102
}
103
 
104
export function listAddressbooks(db: CardDavDoDb, subjectId: string, nowMs: number): AddressbookRow[] {
105
  ensureDefaultAddressbook(db, subjectId, nowMs);
106
  return db.select().from(addressbooks).orderBy(addressbooks.createdAtMs).all();
107
}
108
 
109
export function addressbookById(db: CardDavDoDb, id: string): AddressbookRow | undefined {
110
  return db.query.addressbooks.findFirst({ where: eq(addressbooks.id, id) }).sync();
111
}
112
 
113
export function addressbookByName(db: CardDavDoDb, name: string): AddressbookRow | undefined {
114
  return db.query.addressbooks.findFirst({ where: eq(addressbooks.name, name) }).sync();
115
}
116
 
117
export function createAddressbook(
118
  db: CardDavDoDb,
119
  input: {
120
    subjectId: string;
121
    name: string;
122
    displayName: string;
123
    description: string | null;
124
    nowMs: number;
125
  },
126
): AddressbookRow {
127
  ensureDefaultAddressbook(db, input.subjectId, input.nowMs);
128
  const id = addressbookId();
129
  db.insert(addressbooks)
130
    .values({
131
      id,
132
      name: input.name,
133
      displayName: input.displayName,
134
      description: input.description,
135
      createdAtMs: input.nowMs,
136
      modifiedAtMs: input.nowMs,
137
      syncSeq: 0,
138
    })
139
    .run();
140
  return addressbookById(db, id)!;
141
}
142
 
143
export function updateAddressbook(
144
  db: CardDavDoDb,
145
  input: { id: string; displayName?: string; description?: string | null; nowMs: number },
146
): AddressbookRow | undefined {
147
  const existing = addressbookById(db, input.id);
148
  if (!existing) return undefined;
149
  db.update(addressbooks)
150
    .set({
151
      displayName: input.displayName ?? existing.displayName,
152
      description: input.description === undefined ? existing.description : input.description,
153
      modifiedAtMs: input.nowMs,
154
      syncSeq: sql`${addressbooks.syncSeq} + 1`,
155
    })
156
    .where(eq(addressbooks.id, input.id))
157
    .run();
158
  return addressbookById(db, input.id);
159
}
160
 
161
export function deleteAddressbook(db: CardDavDoDb, id: string): boolean {
162
  const existing = addressbookById(db, id);
163
  if (!existing) return false;
164
  const objectIds = db
165
    .select({ id: addressObjects.id })
166
    .from(addressObjects)
167
    .where(eq(addressObjects.addressbookId, id))
168
    .all();
169
  db.transaction((tx) => {
170
    tx.delete(addressDeadProps)
171
      .where(and(eq(addressDeadProps.resourceKind, "addressbook"), eq(addressDeadProps.resourceId, id)))
172
      .run();
173
    if (objectIds.length > 0) {
174
      tx.delete(addressDeadProps)
175
        .where(
176
          and(
177
            eq(addressDeadProps.resourceKind, "object"),
178
            inArray(
179
              addressDeadProps.resourceId,
180
              objectIds.map((row) => row.id),
181
            ),
182
          ),
183
        )
184
        .run();
185
    }
186
    tx.delete(addressbooks).where(eq(addressbooks.id, id)).run();
187
  });
188
  return true;
189
}
190
 
191
export function homeResource(db: CardDavDoDb): CardDavResource {
192
  return { kind: "home", href: "/addressbooks/", id: "home", deadProps: deadProps(db, "home", "home") };
193
}
194
 
195
export function bookResource(db: CardDavDoDb, book: AddressbookRow): CardDavResource {
196
  return {
197
    kind: "addressbook",
198
    href: bookHref(book.name),
199
    id: book.id,
200
    syncToken: addressSyncToken(latestChangeSeq(db)),
201
    book,
202
    deadProps: deadProps(db, "addressbook", book.id),
203
  };
204
}
205
 
206
export function objectResource(db: CardDavDoDb, book: AddressbookRow, object: AddressObjectRow): CardDavResource {
207
  return {
208
    kind: "object",
209
    href: objectHref(book.name, object.name),
210
    id: object.id,
211
    book,
212
    object,
213
    index: db.query.addressIndex.findFirst({ where: eq(addressIndex.objectId, object.id) }).sync(),
214
    deadProps: deadProps(db, "object", object.id),
215
  };
216
}
217
 
218
export interface ObjectResourcesResult {
219
  resources: CardDavResource[];
220
  truncated: boolean;
221
}
222
 
223
export function objectResources(
224
  db: CardDavDoDb,
225
  book: AddressbookRow,
226
  maxResults: number,
227
  filters: CardDavSearchFilter[] = [],
228
  filterTest: CardDavFilterTest = "anyof",
229
): ObjectResourcesResult {
230
  const { rows, truncated } = filteredObjects(db, book.id, maxResults, filters, filterTest);
231
  return { resources: rows.map((row) => objectResource(db, book, row)), truncated };
232
}
233
 
234
export function objectByName(db: CardDavDoDb, bookId: string, name: string): AddressObjectRow | undefined {
235
  return db.query.addressObjects
236
    .findFirst({ where: and(eq(addressObjects.addressbookId, bookId), eq(addressObjects.name, name)) })
237
    .sync();
238
}
239
 
240
export function objectByUid(db: CardDavDoDb, bookId: string, uid: string): AddressObjectRow | undefined {
241
  return db.query.addressObjects
242
    .findFirst({ where: and(eq(addressObjects.addressbookId, bookId), eq(addressObjects.uid, uid)) })
243
    .sync();
244
}
245
 
246
export function objectNameFromHref(book: AddressbookRow, href: string, acceptedOrigins?: string[]): string | null {
247
  return objectNameFromCollectionHref(bookHref(book.name), href, acceptedOrigins);
248
}
249
 
250
export function objectResourceFromHref(
251
  db: CardDavDoDb,
252
  book: AddressbookRow,
253
  href: string,
254
  acceptedOrigins?: string[],
255
): CardDavResource | null {
256
  const name = objectNameFromHref(book, href, acceptedOrigins);
257
  if (!name) return null;
258
  const object = objectByName(db, book.id, name);
259
  return object ? objectResource(db, book, object) : null;
260
}
261
 
262
export function latestChangeSeq(db: CardDavDoDb): number {
263
  return (
264
    db.select({ seq: addressChanges.seq }).from(addressChanges).orderBy(desc(addressChanges.seq)).limit(1).all()[0]
265
      ?.seq ?? 0
266
  );
267
}
268
 
269
export interface ChangesSinceResult {
270
  changes: {
271
    seq: number;
272
    addressbookId: string | null;
273
    href: string;
274
    changeType: DavChangeType;
275
    changedAtMs: number;
276
  }[];
277
  truncated: boolean;
278
}
279
 
280
export function changesSince(
281
  db: CardDavDoDb,
282
  book: AddressbookRow,
283
  token: string | null,
284
  maxResults: number,
285
): ChangesSinceResult {
286
  const since = parseDavSyncToken(token);
287
  const rows = db
288
    .select()
289
    .from(addressChanges)
290
    .where(and(eq(addressChanges.addressbookId, book.id), gt(addressChanges.seq, since)))
291
    .orderBy(addressChanges.seq)
292
    .limit(maxResults + 1)
293
    .all();
294
  const truncated = rows.length > maxResults;
295
  return { changes: truncated ? rows.slice(0, maxResults) : rows, truncated };
296
}
297
 
298
export function syncTokenSeq(token: string | null): number | null {
299
  return parseDavSyncTokenStrict(token);
300
}
301
 
302
export function recordChange(
303
  db: CardDavDoDb,
304
  bookId: string,
305
  href: string,
306
  changeType: DavChangeType,
307
  nowMs: number,
308
): void {
309
  db.insert(addressChanges).values({ addressbookId: bookId, href, changeType, changedAtMs: nowMs }).run();
310
  db.update(addressbooks)
311
    .set({ modifiedAtMs: nowMs, syncSeq: sql`${addressbooks.syncSeq} + 1` })
312
    .where(eq(addressbooks.id, bookId))
313
    .run();
314
}
315
 
316
function deadProps(db: CardDavDoDb, kind: "home" | "addressbook" | "object", resourceId: string): AddressDeadPropRow[] {
317
  return db
318
    .select()
319
    .from(addressDeadProps)
320
    .where(and(eq(addressDeadProps.resourceKind, kind), eq(addressDeadProps.resourceId, resourceId)))
321
    .all();
322
}
323
 
324
function filteredObjects(
325
  db: CardDavDoDb,
326
  bookId: string,
327
  maxResults: number,
328
  filters: CardDavSearchFilter[],
329
  filterTest: CardDavFilterTest = "anyof",
330
): { rows: AddressObjectRow[]; truncated: boolean } {
331
  if (filters.length === 0) {
332
    const rows = db
333
      .select()
334
      .from(addressObjects)
335
      .where(eq(addressObjects.addressbookId, bookId))
336
      .limit(maxResults + 1)
337
      .all();
338
    const truncated = rows.length > maxResults;
339
    return { rows: truncated ? rows.slice(0, maxResults) : rows, truncated };
340
  }
341
  const clauses = filters.map((filter) => filterClause(filter));
342
  // RFC 6352 10.5: combine prop-filter clauses with anyof (OR) or allof (AND).
343
  const combined = filterTest === "anyof" ? or(...clauses) : and(...clauses);
344
  const indexes = db
345
    .select()
346
    .from(addressIndex)
347
    .where(and(eq(addressIndex.addressbookId, bookId), combined))
348
    .limit(maxResults + 1)
349
    .all();
350
  const truncated = indexes.length > maxResults;
351
  const limited = truncated ? indexes.slice(0, maxResults) : indexes;
352
  if (limited.length === 0) return { rows: [], truncated };
353
  const rows = db
354
    .select()
355
    .from(addressObjects)
356
    .where(
357
      inArray(
358
        addressObjects.id,
359
        limited.map((row) => row.objectId),
360
      ),
361
    )
362
    .all();
363
  return { rows, truncated };
364
}
365
 
366
function containsCaseInsensitive(column: unknown, text: string) {
367
  return sql`instr(lower(coalesce(${column}, '')), ${text.toLowerCase()}) > 0`;
368
}
369
 
370
function notNullAndNotEmpty(column: unknown) {
371
  return sql`coalesce(${column}, '') != ''`;
372
}
373
 
374
function isNullOrEmpty(column: unknown) {
375
  return sql`coalesce(${column}, '') = ''`;
376
}
377
 
378
function jsonArrayHasValues(column: unknown) {
379
  return sql`coalesce(${column}, '[]') not in ('[]', '')`;
380
}
381
 
382
function jsonArrayIsEmpty(column: unknown) {
383
  return sql`coalesce(${column}, '[]') in ('[]', '')`;
384
}
385
 
386
function filterClause(filter: CardDavSearchFilter) {
387
  if (filter.kind === "exists") return existsClause(filter.prop, filter.defined);
388
  return textClause(filter.prop, filter.text);
389
}
390
 
391
function textClause(prop: CardDavSearchFilter["prop"], text: string) {
392
  switch (prop) {
393
    case "UID":
394
      return containsCaseInsensitive(addressIndex.uid, text);
395
    case "FN":
396
      return containsCaseInsensitive(addressIndex.fn, text);
397
    case "N":
398
      return or(
399
        containsCaseInsensitive(addressIndex.nFamily, text),
400
        containsCaseInsensitive(addressIndex.nGiven, text),
401
      );
402
    case "ORG":
403
      return containsCaseInsensitive(addressIndex.org, text);
404
    case "EMAIL":
405
      return containsCaseInsensitive(addressIndex.emails, text);
406
    case "TEL":
407
      return containsCaseInsensitive(addressIndex.tels, text);
408
  }
409
}
410
 
411
function existsClause(prop: CardDavSearchFilter["prop"], defined: boolean) {
412
  switch (prop) {
413
    case "UID":
414
      return defined ? notNullAndNotEmpty(addressIndex.uid) : isNullOrEmpty(addressIndex.uid);
415
    case "FN":
416
      return defined ? notNullAndNotEmpty(addressIndex.fn) : isNullOrEmpty(addressIndex.fn);
417
    case "N":
418
      return defined
419
        ? or(notNullAndNotEmpty(addressIndex.nFamily), notNullAndNotEmpty(addressIndex.nGiven))
420
        : and(isNullOrEmpty(addressIndex.nFamily), isNullOrEmpty(addressIndex.nGiven));
421
    case "ORG":
422
      return defined ? notNullAndNotEmpty(addressIndex.org) : isNullOrEmpty(addressIndex.org);
423
    case "EMAIL":
424
      return defined ? jsonArrayHasValues(addressIndex.emails) : jsonArrayIsEmpty(addressIndex.emails);
425
    case "TEL":
426
      return defined ? jsonArrayHasValues(addressIndex.tels) : jsonArrayIsEmpty(addressIndex.tels);
427
  }
428
}