D1 Reference (@thebookingkit/d1)

@thebookingkit/d1 bridges Cloudflare D1’s text-based date storage with @thebookingkit/core’s UTC Date object expectations. It also provides double-booking prevention for SQLite, which lacks Postgres’s EXCLUDE USING gist constraint — an atomic overlap-guarded insert, an optional schema-level unique index, and an advisory lock.

npm install @thebookingkit/d1

---

The Core Problem

SQLite stores dates as TEXT. String-range queries (>=, <=) only produce correct results when every stored value uses the same lexicographic format. @thebookingkit/d1 enforces a single canonical format — UTC-Z ("YYYY-MM-DDTHH:mm:ss.sssZ") — for all date columns.

---

D1DateCodec

The canonical date codec. All methods are pure functions with no side effects. Use D1DateCodec on every date value that crosses the D1 boundary.

import { D1DateCodec } from "@thebookingkit/d1";

D1DateCodec.encode()

Converts a date value to the canonical UTC-Z string for storage or query bounds.

// From a Date object (most common — slot returned by getAvailableSlots)
const stored = D1DateCodec.encode(slot.startTime);
// => "2026-03-09T14:00:00.000Z"

// From a UTC-Z string (pass-through, normalized)
D1DateCodec.encode("2026-03-09T14:00:00Z");
// => "2026-03-09T14:00:00.000Z"

// From a local ISO string — requires timezone
D1DateCodec.encode("2026-03-10T09:00:00", { timezone: "Australia/Sydney" });
// => "2026-03-09T22:00:00.000Z"

Parameters

Parameter	Type	Description
value	Date | string	Date object, UTC-Z string, or local ISO string
options.timezone	string	IANA timezone required when value is a local ISO string without a Z suffix

Returns string — canonical UTC-Z ISO string.

Throws

• D1DateEncodeError — local ISO string passed without options.timezone.
• RangeError — unrecognized format or invalid timezone.

D1DateCodec.decode()

Decodes a D1 text column value into a UTC Date object for use with @thebookingkit/core.

const date = D1DateCodec.decode(row.startsAt);
// date is a valid UTC Date object

Parameters

Parameter	Type	Description
raw	string	Raw string value from a D1 text column

Returns Date — a valid UTC Date object.

Throws D1DateDecodeError when the string cannot be parsed. Date-only strings ("2026-03-10") are explicitly rejected as ambiguous.

Legacy local-ISO rows written before this codec was adopted are handled transparently: they are parsed as UTC (appending "Z") and tagged with a _d1LegacyFormat property so you can detect and migrate them.

D1DateCodec.dayBounds()

Builds gte/lte string bounds for a single-day range query.

const { gte, lte } = D1DateCodec.dayBounds("2026-03-09");
// gte => "2026-03-09T00:00:00.000Z"
// lte => "2026-03-09T23:59:59.999Z"

const rows = await db.select().from(bookings)
  .where(and(
    eq(bookings.barberId, barberId),
    gte(bookings.startsAt, bounds.gte),
    lte(bookings.startsAt, bounds.lte),
  )).all();

Parameters

Parameter	Type	Description
dateStr	string	Date in "YYYY-MM-DD" format

D1DateCodec.rangeBounds()

Builds gte/lte bounds from a DateRange object for multi-day queries.

const { gte, lte } = D1DateCodec.rangeBounds({
  start: new Date("2026-03-09T00:00:00.000Z"),
  end: new Date("2026-03-15T23:59:59.999Z"),
});

D1DateCodec.toDateRange()

Builds a { start: Date; end: Date } range from a date string for passing to getAvailableSlots().

const range = D1DateCodec.toDateRange("2026-03-09");
getAvailableSlots(rules, overrides, bookings, range, timezone, opts);

D1DateCodec.isLegacyFormat()

Returns true if the string is in legacy local-ISO format (no Z suffix). Use during migration to identify rows that need updating.

D1DateCodec.isLegacyFormat("2026-03-09T14:00:00");   // true
D1DateCodec.isLegacyFormat("2026-03-09T14:00:00.000Z"); // false

---

Codec Errors

import { D1DateDecodeError, D1DateEncodeError } from "@thebookingkit/d1";

Error class	Code	When thrown
D1DateDecodeError	D1_DATE_DECODE_ERROR	Raw string cannot be parsed to a valid UTC Date
D1DateEncodeError	D1_DATE_ENCODE_ERROR	Local ISO string passed to encode() without a timezone

Both expose a raw property with the original string and a descriptive message explaining the required format.

---

Booking Helpers

Functions that bridge raw D1 row data to the types expected by @thebookingkit/core.

import {
  d1BookingRowsToInputs,
  d1OverrideRowsToInputs,
  d1AvailabilityRuleRowsToInputs,
  encodeD1Date,
  d1DayBounds,
  d1DayQuery,
  d1LocalDayQuery,
  localToday,
} from "@thebookingkit/d1";

d1BookingRowsToInputs()

Converts raw D1 booking rows into BookingInput[] for getAvailableSlots() and isSlotAvailable(). Decodes all date strings through D1DateCodec.decode().

const rows = await db.select().from(bookings)
  .where(and(
    eq(bookings.barberId, barberId),
    gte(bookings.startsAt, bounds.gte),
    lte(bookings.startsAt, bounds.lte),
  )).all();

const inputs = d1BookingRowsToInputs(rows);
const slots = getAvailableSlots(rules, [], inputs, dateRange, timezone, opts);

Minimum row shape (D1BookingRow)

Field	Type	Description
startsAt	string	UTC-Z string (from D1DateCodec.encode())
endsAt	string	UTC-Z string
status	string	Booking status

Your Drizzle schema’s inferred type is a superset of this.

d1OverrideRowsToInputs()

Converts raw D1 availability override rows into AvailabilityOverrideInput[].

const overrideInputs = d1OverrideRowsToInputs(overrideRows);

Minimum row shape (D1AvailabilityOverrideRow)

Field	Type	Description
date	string	Date as UTC-Z string
startTime	string | null	Wall-clock time in "HH:mm" format, or null
endTime	string | null	Wall-clock time in "HH:mm" format, or null
isUnavailable	number | boolean	Whether the provider is blocked for this date

d1AvailabilityRuleRowsToInputs()

Converts raw D1 availability_rules rows into AvailabilityRuleInput[]. Handles validFrom/validUntil date decoding.

const ruleRows = await db.select()
  .from(availabilityRules)
  .where(eq(availabilityRules.providerId, providerId))
  .all();

const rules = d1AvailabilityRuleRowsToInputs(ruleRows);

Minimum row shape (D1AvailabilityRuleRow)

Field	Type	Description
rrule	string	RRULE string e.g. "RRULE:FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR"
startTime	string	Wall-clock start in "HH:mm" format
endTime	string	Wall-clock end in "HH:mm" format
timezone	string	IANA timezone identifier
validFrom	string | null	ISO date string or null
validUntil	string | null	ISO date string or null

encodeD1Date()

Convenience wrapper around D1DateCodec.encode() for use at INSERT/UPDATE time.

await db.insert(bookings).values({
  startsAt: encodeD1Date(slot.startTime),
  endsAt:   encodeD1Date(slot.endTime),
  // ...
});

// With local ISO string and explicit timezone:
encodeD1Date("2026-03-10T09:00:00", "Australia/Sydney");

d1DayBounds()

Returns { gte, lte } strings for a single-day query.

const bounds = d1DayBounds("2026-03-09");

d1DayQuery()

Returns both the D1 query bounds and the matching DateRange for the slot engine in one call. This guarantees the DB query and the slot engine use the same UTC boundary, eliminating a class of mixed-format bugs.

const { bounds, dateRange } = d1DayQuery("2026-03-09");

// 1. Query D1 with bounds
const rows = await db.select().from(bookings)
  .where(and(
    eq(bookings.barberId, barberId),
    gte(bookings.startsAt, bounds.gte),
    lte(bookings.startsAt, bounds.lte),
  )).all();

// 2. Feed into slot engine — same UTC boundary, no mismatch
const slots = getAvailableSlots(
  rules, [], d1BookingRowsToInputs(rows), dateRange, timezone, opts
);

d1LocalDayQuery()

Timezone-aware variant of d1DayQuery(). Use when the provider’s timezone is far from UTC (e.g. "Australia/Sydney", "Asia/Tokyo"). The UTC-midnight bounds from d1DayQuery miss bookings that cross midnight UTC.

const { bounds, dateRange } = d1LocalDayQuery("2026-03-09", "Australia/Sydney");
// bounds.gte = "2026-03-08T13:00:00.000Z"  (March 9 midnight AEDT)
// bounds.lte = "2026-03-09T12:59:59.999Z"  (1ms before March 10 midnight AEDT)
// dateRange  = UTC midnight March 9 → 23:59:59.999Z (proven correct for RRULE expansion)

Parameters

Parameter	Type	Description
dateStr	string	Local calendar day in "YYYY-MM-DD" format
timezone	string	IANA timezone identifier for the provider

localToday()

Returns today’s date as a "YYYY-MM-DD" string in the given timezone. Essential for Cloudflare Workers, which run in UTC but need to know “today” relative to a location.

const today = localToday("Australia/Sydney");
// => "2026-03-10" even if UTC is still March 9

const { bounds, dateRange } = d1LocalDayQuery(today, "Australia/Sydney");

Parameters

Parameter	Type	Description
timezone	string	IANA timezone identifier
now	Date (optional)	Reference date, defaults to new Date(). Useful for testing.

---

Double-Booking Prevention

SQLite / D1 serializes writes at the statement level but does NOT make a read-then-write sequence atomic. Two concurrent requests can both read an empty slot, both pass the availability check, and both insert.

Postgres solves this with EXCLUDE USING gist. D1 has no range-exclusion constraint, so this package provides the equivalent guarantee in three layers:

Layer	Mechanism	Guarantee
insertBookingIfFree()	Overlap check + INSERT in one SQL statement	Authoritative. Concurrent requests cannot interleave, with or without a lock
BOOKINGS_UNIQUE_SLOT_DDL	Opt-in partial unique index	Schema-level backstop for code paths that bypass the guard
D1BookingLock	Advisory compare-and-swap lock	Reduces contention, returns friendly errors. Advisory only

The lock alone is not enough

D1BookingLock is advisory: it protects a slot only if every writer takes it, and its lease can expire mid-operation. Always perform the write itself with insertBookingIfFree() — that is what makes double booking impossible.

Building a GuardDb / LockDb

Neither a raw D1 binding nor Drizzle satisfies this interface

env.DB exposes prepare/batch/exec but no run. Drizzle’s run(query) takes a single argument, so passing it directly silently discards params and binds every ? as NULL — which the guard reports as a successful insert while writing an all-NULL row. Always go through a small adapter:

import type { GuardDb } from "@thebookingkit/d1";

// Raw D1 binding (Workers)
const db: GuardDb = {
  run: (sql, params = []) => env.DB.prepare(sql).bind(...params).run(),
};

// Drizzle (drizzle-orm/d1) — reach through to the underlying binding
const db: GuardDb = {
  run: (sql, params = []) => drizzle.$client.prepare(sql).bind(...params).run(),
};

The same object satisfies LockDb, so one adapter serves both the guard and D1BookingLock.

insertBookingIfFree()

Inserts a booking only if the slot is still free, atomically. Needs no lock and no retry loop.

import { insertBookingIfFree } from "@thebookingkit/d1";

const { inserted } = await insertBookingIfFree(db, {
  id:            crypto.randomUUID(),
  provider_id:   barberId,
  event_type_id: eventTypeId,
  customer_email: email,
  customer_name:  name,
  starts_at:     slot.startTime,   // Date — encoded to UTC-Z automatically
  ends_at:       slot.endTime,
  status:        "confirmed",
  created_at:    new Date(),
  updated_at:    new Date(),
});

if (!inserted) {
  return Response.json({ error: "Slot just taken" }, { status: 409 });
}

The generated statement is a single INSERT ... SELECT ... WHERE NOT EXISTS. Every value — including the inactive statuses — is a bound parameter:

INSERT INTO "bookings" ("id", "provider_id", "starts_at", "ends_at", "status")
SELECT ?, ?, ?, ?, ?
WHERE NOT EXISTS (
  SELECT 1 FROM "bookings"
  WHERE "provider_id" = ?
    AND ("status" IS NULL OR "status" NOT IN (?, ?, ?))
    AND "starts_at" < ?   -- new end
    AND "ends_at"   > ?   -- new start
)

statement.params carries 11 entries in this example, in order: the 5 inserted values, the scope value, the 3 inactive statuses, the window end, then the window start.

SQLite runs a single statement inside an implicit transaction holding the write lock, and D1 serializes all writes against one primary — so the NOT EXISTS check cannot be invalidated between evaluation and insertion.

Parameters

Parameter	Type	Description
db	GuardDb	DB client with a run(sql, params) method returning the driver result
values	Record<string, unknown>	Columns to insert. Must include the scope, start, and end columns
options.columns	BookingGuardColumns	Override table/column names. Defaults: bookings, provider_id, starts_at, ends_at, status, id
options.inactiveStatuses	readonly string[]	Statuses that do NOT block. Default ["cancelled", "rejected", "no_show"]
options.conflictWindow	{ startsAt, endsAt }	Widen the conflict check without changing inserted values — use for buffer time
options.excludeId	string	Ignore this row id when checking. Required when rescheduling a booking into an overlapping window

Returns { inserted: boolean, changes: number, statement: { sql, params } }.

Throws RangeError for malformed input (missing/null scope or interval, inverted interval, invalid identifier), and GuardResultError when the driver reports no affected-row count.

Overlap semantics

Intervals are half-open [startsAt, endsAt), matching the slot engine. Back-to-back bookings do not conflict:

Existing	Candidate	Result
09:00–09:30	09:00–09:30	Blocked
09:00–09:30	09:15–09:45	Blocked
09:00–09:30	08:00–10:00	Blocked
09:00–09:30	09:30–10:00	Allowed
09:00–09:30	08:30–09:00	Allowed

A row blocks unless its status is cancelled, rejected, or no_show — identical to getActiveBookings() in @thebookingkit/core. The predicate is NOT IN, so any status added in future blocks by default.

Requires canonical dates

The guard compares date columns lexicographically as TEXT, which is exact only for the fixed-width UTC-Z format from D1DateCodec.encode(). Run findLegacyRows() and migrateRowDates() before relying on it if your table predates the codec.

Buffer time

Pass a widened conflictWindow while values carries the true appointment times:

await insertBookingIfFree(db, values, {
  conflictWindow: {
    startsAt: subMinutes(slot.startTime, 15),
    endsAt:   addMinutes(slot.endTime, 15),
  },
});

Rescheduling

Rescheduling writes a new row and retires the old one. Without excludeId the new row conflicts with the booking it is replacing:

// 1. Retire the old booking so it stops blocking the slot.
await db.run(`UPDATE bookings SET status = 'rescheduled' WHERE id = ?`, [originalId]);

// 2. Insert the replacement, ignoring the row being moved.
const { inserted } = await insertBookingIfFree(db, newValues, { excludeId: originalId });
if (!inserted) {
  await db.run(`UPDATE bookings SET status = 'confirmed' WHERE id = ?`, [originalId]);
  throw new BookingConflictError();
}

excludeId must be a non-empty string. A non-string binds as SQL NULL, and "id" <> NULL is NULL for every row — which would null the whole predicate and disable the guard, so it is rejected with a RangeError.

Note 'rescheduled' still blocks on D1 (it is not in D1_INACTIVE_STATUSES), which is why step 1 alone is not enough and excludeId is required.

insertBookingOrThrow()

Identical to insertBookingIfFree() but throws BookingConflictError (from @thebookingkit/core, code BOOKING_CONFLICT) instead of returning inserted: false. Use it when your handler already maps that error to HTTP 409, so the D1 and Postgres paths raise the same error type.

Blocking statuses differ between backends

The D1 guard mirrors core’s INACTIVE_STATUSES (cancelled, rejected, no_show), while the Postgres EXCLUDE constraint uses cancelled, rejected, rescheduled. So a no_show blocks the slot in Postgres but not on D1, and a rescheduled blocks on D1 but not in Postgres. Align them before relying on identical behaviour across backends.

insertResourceBookingIfFree()

insertBookingIfFree() pre-scoped to resource_id — the D1 counterpart of the Postgres EXCLUDE constraint on (resource_id, tstzrange(starts_at, ends_at)).

This guards the resource only. It does not check the provider, so a provider can still be double-booked across two different resources. A single statement can guard one scope atomically; see the JSDoc on insertResourceBookingIfFree for the two-step pattern when both must hold, or apply BOOKINGS_UNIQUE_SLOT_DDL, whose two indexes cover provider and resource independently.

It also enforces one booking per resource at a time, matching Postgres. A resource’s capacity is the party size it seats, not a number of concurrent bookings.

import { insertResourceBookingIfFree } from "@thebookingkit/d1";

const { inserted } = await insertResourceBookingIfFree(db, {
  id:             crypto.randomUUID(),
  resource_id:    tableId,
  provider_id:    providerId,      // NOT NULL in BOOKINGS_DDL
  event_type_id:  eventTypeId,     // NOT NULL
  customer_email: email,           // NOT NULL
  customer_name:  name,            // NOT NULL
  starts_at:      slot.startTime,
  ends_at:        slot.endTime,
  status:         "confirmed",
  created_at:     new Date(),      // NOT NULL
  updated_at:     new Date(),      // NOT NULL
});

buildInsertIfFree()

Builds the statement without executing it — for db.batch([...]), a custom driver, or inspection.

const { sql, params } = buildInsertIfFree(values);
const res = await env.DB.prepare(sql).bind(...params).run();
if (res.meta.changes === 0) throw new BookingConflictError();

BOOKINGS_UNIQUE_SLOT_DDL

Opt-in schema-level backstop. Creates partial unique indexes so a provider — and a resource — cannot hold two active bookings starting at the same instant, enforced by SQLite itself. This catches any code path that bypasses the guard, including manual SQL.

import { BOOKINGS_UNIQUE_SLOT_DDL } from "@thebookingkit/d1";
await db.exec(BOOKINGS_UNIQUE_SLOT_DDL); // two statements — exec, not run

Scope and limits:

• Catches identical start times only. Partial overlaps are not covered — a unique index cannot express range exclusion. insertBookingIfFree() remains the authoritative overlap guard.
• Cancelled, rejected, and no-show rows are excluded, so a cancelled slot can be rebooked.
• Do not apply this if your deployment writes one booking row per attendee for group events. The standard schema models group bookings as one bookings row with N booking_seats children, which this index supports.
• Creation fails if the table already contains violating rows. Find them first:

SELECT provider_id, starts_at, COUNT(*) FROM bookings
WHERE status NOT IN ('cancelled', 'rejected', 'no_show')
GROUP BY provider_id, starts_at HAVING COUNT(*) > 1;

---

Advisory Lock

D1BookingLock implements an advisory lock via a dedicated booking_locks table using Compare-And-Swap semantics. Use it to serialize expensive work and return friendly errors — not as your only defence against double booking.

Required schema

Add this table to your D1 schema:

CREATE TABLE IF NOT EXISTS booking_locks (
  lock_key   TEXT PRIMARY KEY,
  expires_at TEXT NOT NULL,
  created_at TEXT NOT NULL,
  holder     TEXT
);

The DDL constant BOOKING_LOCKS_DDL is also exported from @thebookingkit/d1 for programmatic use.

Upgrading an existing table. The holder column carries the fencing token. Tables created before it existed are upgraded automatically on first use; set autoMigrate: false to forbid runtime DDL and apply BOOKING_LOCKS_HOLDER_MIGRATION_SQL yourself instead.

Fencing token

Every acquisition writes a random holder token, and release is scoped to it:

DELETE FROM booking_locks WHERE lock_key = ? AND holder = ?

Without this, a holder whose lease expired — and whose lock was therefore reclaimed by another request — would delete the new holder’s lock row on cleanup, silently unlocking a slot that request was actively using.

A lease can still expire while its holder is mid-callback. That is detected: withLock throws LockLeaseExpiredError rather than hiding the race. Pair with insertBookingIfFree() and the racing write is rejected too.

D1BookingLock

import { D1BookingLock } from "@thebookingkit/d1";

const lock = new D1BookingLock(rawDb, {
  tableName:     "booking_locks", // default
  lockTtlMs:     10_000,          // 10 seconds (safety valve for crashed workers)
  maxRetries:    5,               // default
  baseDelayMs:   100,             // default
  onLeaseExpiry: "throw",         // default
  autoMigrate:   true,            // default
});

await lock.withLock(`${barberId}:${dateStr}`, async () => {
  // This block is serialized per lockKey
  const existing = await db.select().from(bookings).where(...).all();
  const available = isSlotAvailable(rules, [], d1BookingRowsToInputs(existing), start, end);
  if (!available.available) throw new BookingConflictError();

  // Authoritative write — rejects the race even if the lease lapsed
  await insertBookingOrThrow(db, values);
});

Constructor parameters

Parameter	Type	Default	Description
db	LockDb	—	DB client with a run(sql, params) method
options.tableName	string	"booking_locks"	Name of the lock table
options.lockTtlMs	number	10_000	Lock TTL in ms (prevents stale locks from crashed workers)
options.maxRetries	number	5	Maximum acquire attempts before throwing. Must be ≥ 1
options.baseDelayMs	number	100	Base delay for jittered exponential backoff
options.onLeaseExpiry	"throw" | "ignore"	"throw"	Whether a lease that expires mid-callback raises LockLeaseExpiredError
options.autoMigrate	boolean	true	Add the holder column in place when a legacy lock table lacks it
options.generateHolder	() => string	crypto.randomUUID	Override the fencing-token generator (for deterministic tests)

Invalid options throw RangeError at construction — a non-positive lockTtlMs, a maxRetries below 1 or non-integer, a negative baseDelayMs, or a tableName that is not a plain SQL identifier.

withLock() parameters

Parameter	Type	Description
lockKey	string	Unique non-empty key for the resource. Convention: "${providerId}:${dateStr}"
fn	(handle: LockHandle) => Promise<T>	Async callback containing the availability check and insert

Returns the return value of fn. Throws LockAcquisitionError if all retries are exhausted, LockLeaseExpiredError if the lease lapsed mid-callback, LockSchemaError if the table lacks holder and autoMigrate is off, RangeError for an empty lockKey. Propagates any error thrown by fn — the callback’s own error always takes precedence over a lease-expiry error.

Backoff formula: min(baseDelayMs * 2^attempt + jitter, 5000ms).

Only uniqueness violations are treated as contention. Any other failure — a missing table, a network error, a NOT NULL violation — surfaces immediately instead of being masked as lock contention or burning the retry budget.

LockHandle

Passed to the withLock callback:

interface LockHandle {
  readonly lockKey: string;
  readonly holder: string;      // fencing token
  readonly expiresAt: number;   // epoch ms; updated by extend()
  isExpired(): boolean;
  extend(ttlMs?: number): Promise<boolean>;
}

extend() pushes the lease expiry further out for long-running work. It returns false when the lock has already been lost — the critical section is no longer protected and should be aborted:

await lock.withLock(key, async (handle) => {
  await chargeCard();                       // slow
  if (!await handle.extend()) throw new Error("lost lock");
  await insertBookingOrThrow(db, values);
});

createD1BookingLock()

Factory function alternative to new D1BookingLock():

import { createD1BookingLock } from "@thebookingkit/d1";

const lock = createD1BookingLock(db, { lockTtlMs: 15_000 });

LockAcquisitionError

Thrown when the lock cannot be acquired after all retry attempts.

import { LockAcquisitionError } from "@thebookingkit/d1";

try {
  await lock.withLock(key, fn);
} catch (err) {
  if (err instanceof LockAcquisitionError) {
    // err.code === "LOCK_ACQUISITION_EXHAUSTED"
    return Response.json({ error: "Slot busy, please retry" }, { status: 503 });
  }
  throw err;
}

LockLeaseExpiredError

Thrown when the lock was no longer held by the time the critical section finished. This is decided by the release itself — the DELETE is scoped to the holder token, so matching no row proves the lease was lost. A slow release does not trigger it. The callback already ran to completion, so any writes it performed may have raced with a request that reclaimed the expired lock. The callback’s return value is preserved on .result.

try {
  await lock.withLock(key, fn);
} catch (err) {
  if (err instanceof LockLeaseExpiredError) {
    // err.code === "LOCK_LEASE_EXPIRED"
    // err.result, err.heldForMs, err.lockTtlMs
  }
}

Remedies, in order of preference:

1. Perform the write with insertBookingIfFree() — the database then rejects the racing insert and this error is purely informational.
2. Raise lockTtlMs above your worst-case critical-section duration.
3. Call handle.extend() from inside a long-running callback.

LockSchemaError

Thrown when the lock table lacks the holder column and autoMigrate is disabled (or the automatic upgrade failed). The message contains the exact ALTER TABLE statement to run.

LockDriverError

Thrown by handle.extend() when the driver’s result exposes no affected-row count, so the renewal cannot be confirmed. Reporting success without evidence would defeat the purpose of the lease.

LockDb interface

Minimum interface the D1BookingLock requires from the database client:

interface LockDb {
  run(sql: string, params?: unknown[]): Promise<unknown>;
}

GuardResultError

Thrown by insertBookingIfFree() when the driver’s result exposes no affected-row count (meta.changes, changes, or rowsAffected), so the guard cannot tell whether the insert succeeded. Assuming success would reintroduce double bookings, so this fails loudly. Use buildInsertIfFree() and inspect your driver’s result directly if it reports row counts in a non-standard shape.

---

Schedule Adapter

Converts day-of-week JSON schedule objects (common in simpler D1 schemas) into AvailabilityRuleInput[] for the slot engine.

import {
  weeklyScheduleToRules,
  intersectSchedulesToRules,
  type WeeklySchedule,
  type DaySchedule,
  type DayOfWeek,
} from "@thebookingkit/d1";

weeklyScheduleToRules()

Converts a WeeklySchedule object into AvailabilityRuleInput[]. Days with the same startTime/endTime pair are grouped into a single FREQ=WEEKLY;BYDAY=... rule to minimize RRULE expansion cost.

const rules = weeklyScheduleToRules(barber.weeklySchedule, "Australia/Sydney");

Parameters

Parameter	Type	Description
schedule	WeeklySchedule | null | undefined	Weekly schedule, or null/undefined (returns [])
timezone	string	IANA timezone identifier

intersectSchedulesToRules()

Intersects two WeeklySchedule objects (e.g. barber + location) into rules representing the overlapping available windows. The intersection enforces that the provider is only available when both schedules have them working.

const rules = intersectSchedulesToRules(
  barber.weeklySchedule,
  location.weeklySchedule,
  location.timezone,
);

Schedule types

WeeklySchedule — Record<DayOfWeek, DaySchedule>

DaySchedule

Field	Type	Description
startTime	string | null	Wall-clock start in "HH:mm" format, or null when isOff is true
endTime	string | null	Wall-clock end in "HH:mm" format, or null when isOff is true
isOff	boolean	true means the provider is closed for the full day

DayOfWeek — "monday" | "tuesday" | "wednesday" | "thursday" | "friday" | "saturday" | "sunday"

---

Resource Helpers

Parallel helpers for the resource-capacity booking feature (E-22).

import {
  d1ResourceAvailabilityRowsToInputs,
  d1ResourceOverrideRowsToInputs,
  D1ResourceBookingLock,
  createD1ResourceBookingLock,
  type D1ResourceRow,
  type D1ResourceAvailabilityRuleRow,
  type D1ResourceAvailabilityOverrideRow,
} from "@thebookingkit/d1";

These functions follow the same patterns as their booking counterparts. D1ResourceBookingLock uses the same advisory lock table with a resource-scoped key (e.g. "resource:${resourceId}:${dateStr}").

---

Migration Utilities

Tools for the one-time data migration from legacy local-ISO date storage to canonical UTC-Z format.

import {
  findLegacyRows,
  migrateRowDates,
  buildMigrationSql,
  type MigrationColumn,
  type TableMigrationPlan,
} from "@thebookingkit/d1";

findLegacyRows()

Filters an in-memory array of rows, returning those whose date columns are in legacy local-ISO format. It does not query the database — fetch the rows yourself first.

migrateRowDates()

Returns the canonical UTC-Z replacements for one row’s legacy date columns. It computes an updates object; it does not write anything. Uses D1DateCodec.isLegacyFormat() to detect and D1DateCodec.encode() to normalize.

buildMigrationSql()

Generates the single SQL UPDATE statement needed to migrate one row. Useful for reviewing before applying.

TableMigrationPlan

Field	Type	Description
tableName	string	Table to migrate
primaryKey	string (optional)	Primary key column for UPDATE WHERE clause
columns	MigrationColumn[]	Columns containing date strings

MigrationColumn

Field	Type	Default	Description
name	string	—	Column name in the SQL table
legacyInterpretation	"utc" | "tz"	"utc"	How to interpret legacy local-ISO values. Use "utc" for Cloudflare Workers (always UTC), "tz" for servers with a non-UTC local timezone
timezone	string	—	Required when legacyInterpretation is "tz"

---

DDL Constants

Pre-built CREATE TABLE SQL statements for all BookingKit tables. Import and execute them in your D1 migration scripts.

import {
  BOOKING_LOCKS_DDL,
  RESOURCE_DDL,
  ORGANIZATIONS_DDL,
  TEAMS_DDL,
  PROVIDERS_DDL,
  EVENT_TYPES_DDL,
  AVAILABILITY_DDL,
  BOOKINGS_DDL,
  RECURRING_DDL,
  PAYMENTS_DDL,
  ROUTING_DDL,
  WORKFLOWS_DDL,
  WEBHOOKS_DDL,
  EMAIL_DDL,
  CUSTOMER_DDL,
  WALK_IN_DDL,
  ALL_DDL,       // All of the above concatenated
} from "@thebookingkit/d1";

// Apply all tables in one migration:
await db.exec(ALL_DDL);

---

Complete Booking Flow Example

import {
  D1BookingLock,
  insertBookingOrThrow,
  d1DayQuery,
  d1LocalDayQuery,
  d1BookingRowsToInputs,
  d1AvailabilityRuleRowsToInputs,
  weeklyScheduleToRules,
  encodeD1Date,
  localToday,
} from "@thebookingkit/d1";
import { getAvailableSlots, isSlotAvailable } from "@thebookingkit/core";
import { BookingConflictError } from "@thebookingkit/server";

// ── Step 1: Get today's date in the provider's timezone ──────────────────────
const timezone = "Australia/Sydney";
const today = localToday(timezone);

// ── Step 2: Build query bounds + DateRange together ──────────────────────────
const { bounds, dateRange } = d1LocalDayQuery(today, timezone);

// ── Step 3: Fetch data from D1 ───────────────────────────────────────────────
const [ruleRows, bookingRows] = await Promise.all([
  db.select().from(availabilityRules).where(eq(availabilityRules.barberId, barberId)).all(),
  db.select().from(bookings).where(
    and(
      eq(bookings.barberId, barberId),
      gte(bookings.startsAt, bounds.gte),
      lte(bookings.startsAt, bounds.lte),
    )
  ).all(),
]);

// ── Step 4: Convert to core types ────────────────────────────────────────────
const rules = d1AvailabilityRuleRowsToInputs(ruleRows);
const existingBookings = d1BookingRowsToInputs(bookingRows);

// ── Step 5: Compute available slots ─────────────────────────────────────────
const slots = getAvailableSlots(rules, [], existingBookings, dateRange, timezone, {
  duration: 30,
  bufferAfter: 10,
});

// ── Step 6: Book a slot with double-booking protection ───────────────────────
// The lock keeps contention low and produces friendly errors; the guarded
// insert is what actually makes a double booking impossible.
const lock = new D1BookingLock(rawDb);

await lock.withLock(`${barberId}:${today}`, async () => {
  // Re-fetch inside the lock to reject obvious conflicts early
  const freshRows = await db.select().from(bookings).where(...).all();
  const fresh = d1BookingRowsToInputs(freshRows);

  const available = isSlotAvailable(rules, [], fresh, selectedStart, selectedEnd);
  if (!available.available) throw new BookingConflictError();

  // Atomic: the overlap check and the INSERT are a single SQL statement
  await insertBookingOrThrow(rawDb, {
    id:          crypto.randomUUID(),
    provider_id: barberId,
    starts_at:   selectedStart,
    ends_at:     selectedEnd,
    status:      "confirmed",
    created_at:  new Date(),
    updated_at:  new Date(),
  });
});