Your favorite shift management application!
import {
sqliteTable,
text,
integer,
index,
uniqueIndex,
} from "drizzle-orm/sqlite-core";
import { sql, relations } from "drizzle-orm";
import type {
CalendarViewSettings,
PersonalViewSettings,
} from "../view-settings";
import type { Capability } from "../permission-bundles";
import type { CustomFieldOption, CustomFieldType } from "../custom-fields";
// =====================================================
// Better Auth Tables (generated by @better-auth/cli)
// =====================================================
export const user = sqliteTable("user", {
id: text("id").primaryKey(),
name: text("name").notNull(),
email: text("email").notNull().unique(),
emailVerified: integer("email_verified", { mode: "boolean" })
.default(false)
.notNull(),
image: text("image"),
createdAt: integer("created_at", { mode: "timestamp_ms" })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.$onUpdate(() => new Date())
.notNull(),
// Better Auth Admin Plugin fields
role: text("role"),
banned: integer("banned", { mode: "boolean" }).default(false),
banReason: text("ban_reason"),
banExpires: integer("ban_expires", { mode: "timestamp_ms" }),
// Set on admin-created accounts; cleared once the user changes their password
mustChangePassword: integer("must_change_password", { mode: "boolean" })
.notNull()
.default(false),
});
export const session = sqliteTable(
"session",
{
id: text("id").primaryKey(),
expiresAt: integer("expires_at", { mode: "timestamp_ms" }).notNull(),
token: text("token").notNull().unique(),
createdAt: integer("created_at", { mode: "timestamp_ms" })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
.$onUpdate(() => new Date())
.notNull(),
ipAddress: text("ip_address"),
userAgent: text("user_agent"),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
// Better Auth Admin Plugin field
impersonatedBy: text("impersonated_by"),
},
(table) => [index("session_userId_idx").on(table.userId)]
);
export const account = sqliteTable(
"account",
{
id: text("id").primaryKey(),
accountId: text("account_id").notNull(),
providerId: text("provider_id").notNull(),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
accessToken: text("access_token"),
refreshToken: text("refresh_token"),
idToken: text("id_token"),
accessTokenExpiresAt: integer("access_token_expires_at", {
mode: "timestamp_ms",
}),
refreshTokenExpiresAt: integer("refresh_token_expires_at", {
mode: "timestamp_ms",
}),
scope: text("scope"),
password: text("password"),
createdAt: integer("created_at", { mode: "timestamp_ms" })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
.$onUpdate(() => new Date())
.notNull(),
},
(table) => [index("account_userId_idx").on(table.userId)]
);
export const verification = sqliteTable("verification", {
id: text("id").primaryKey(),
identifier: text("identifier").notNull(),
value: text("value").notNull(),
expiresAt: integer("expires_at", { mode: "timestamp_ms" }).notNull(),
createdAt: integer("created_at", { mode: "timestamp_ms" })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer("updated_at", { mode: "timestamp_ms" }).$onUpdate(
() => new Date()
),
});
// =====================================================
// BetterShift Application Tables
// =====================================================
export const calendars = sqliteTable(
"calendars",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
name: text("name").notNull(),
color: text("color").notNull().default("#3b82f6"),
ownerId: text("owner_id").references(() => user.id, {
onDelete: "set null",
}),
// Bundle granted to guests/subscribed users via the public-access link;
// null = no guest access (replaces the old guestPermission enum below).
// Not a typed FK — calendarPermissionBundles.calendarId already points
// back at this table, and Drizzle/TS can't infer a two-table reference
// cycle. Bundles can't be deleted while assigned (enforced at the app
// layer), so integrity holds without a DB-level constraint here.
guestBundleId: text("guest_bundle_id"),
// Master switch for the whole shift-signup feature on this calendar.
// Owner/admin only; off hides capacity, signup lists and badges everywhere.
signupsEnabled: integer("signups_enabled", { mode: "boolean" })
.notNull()
.default(true),
// Master switch for split shifts (multiple time ranges per shift/preset)
// on this calendar. Owner/admin only; off hides extra ranges everywhere,
// same contract as signupsEnabled above.
splitShiftsEnabled: integer("split_shifts_enabled", { mode: "boolean" })
.notNull()
.default(false),
// null = no own view; every user sees their personal view
viewSettings: text("view_settings", {
mode: "json",
}).$type<CalendarViewSettings>(),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
},
(table) => [index("calendars_ownerId_idx").on(table.ownerId)]
);
// Owner-defined, per-calendar capability bundle. Replaces the old fixed
// read/write/admin ladder — see lib/permission-bundles.ts for the catalog.
export const calendarPermissionBundles = sqliteTable(
"calendar_permission_bundles",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
name: text("name").notNull(),
// Set for the four seeded bundles (read/contribute/manage/admin), null
// once renamed. While set, the UI shows the translated standard name
// instead of `name` (which stays as an English fallback).
seedKey: text("seed_key", {
enum: ["read", "contribute", "manage", "admin"],
}),
capabilities: text("capabilities", { mode: "json" })
.$type<Capability[]>()
.notNull(),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`)
.$onUpdate(() => new Date()),
},
(table) => [
index("calendar_permission_bundles_calendarId_idx").on(table.calendarId),
uniqueIndex("calendar_permission_bundles_calendarId_seedKey_idx")
.on(table.calendarId, table.seedKey)
.where(sql`${table.seedKey} is not null`),
]
);
export const calendarShares = sqliteTable(
"calendar_shares",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
// restrict, not cascade: deleting a bundle must never silently delete
// shares — the app checks usage first and rejects deletion (see plan).
bundleId: text("bundle_id")
.notNull()
.references(() => calendarPermissionBundles.id, {
onDelete: "restrict",
}),
sharedBy: text("shared_by")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
},
(table) => [
index("calendar_shares_calendarId_idx").on(table.calendarId),
index("calendar_shares_userId_idx").on(table.userId),
index("calendar_shares_bundleId_idx").on(table.bundleId),
]
);
export const userCalendarSubscriptions = sqliteTable(
"user_calendar_subscriptions",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
status: text("status", { enum: ["subscribed", "dismissed"] })
.notNull()
.default("subscribed"),
source: text("source", { enum: ["guest", "shared"] })
.notNull()
.default("guest"),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`)
.$onUpdate(() => new Date()),
},
(table) => [
index("user_calendar_subscriptions_userId_idx").on(table.userId),
index("user_calendar_subscriptions_calendarId_idx").on(table.calendarId),
index("user_calendar_subscriptions_status_idx").on(table.status),
]
);
export const userPreferences = sqliteTable("user_preferences", {
userId: text("user_id")
.primaryKey()
.references(() => user.id, { onDelete: "cascade" }),
viewSettings: text("view_settings", {
mode: "json",
}).$type<PersonalViewSettings>(),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`)
.$onUpdate(() => new Date()),
});
// Single-row table (id is always "default") for instance-wide toggles.
export const systemSettings = sqliteTable("system_settings", {
id: text("id").primaryKey().default("default"),
updateCheckEnabled: integer("update_check_enabled", { mode: "boolean" })
.notNull()
.default(true),
updateBannerVisibility: text("update_banner_visibility", {
enum: ["all", "admins"],
})
.notNull()
.default("all"),
// Nullable: existing rows from before this column existed must fall back
// to ALLOW_GUEST_ACCESS (see lib/system-settings.ts), not silently to
// false -- a NOT NULL DEFAULT would force that on every upgrade.
allowGuestAccess: integer("allow_guest_access", { mode: "boolean" }),
// Nullable without default: telemetryEnabled null means "not decided yet",
// and no instance id exists until telemetry is actually on.
telemetryEnabled: integer("telemetry_enabled", { mode: "boolean" }),
telemetryInstanceId: text("telemetry_instance_id"),
telemetryConsentedSchema: integer("telemetry_consented_schema"),
telemetryDecidedAt: integer("telemetry_decided_at", { mode: "timestamp" }),
// Drives the daily send: the scheduler compares it against now instead of
// counting 24h from server start, so restarts do not reset the rhythm.
telemetryLastSentAt: integer("telemetry_last_sent_at", { mode: "timestamp" }),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`)
.$onUpdate(() => new Date()),
});
export const externalSyncs = sqliteTable("external_syncs", {
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
name: text("name").notNull(),
syncType: text("sync_type").notNull().default("icloud"), // icloud, google, custom
calendarUrl: text("calendar_url").notNull(),
color: text("color").notNull().default("#3b82f6"),
displayMode: text("display_mode").notNull().default("normal"),
isHidden: integer("is_hidden", { mode: "boolean" }).notNull().default(false),
hideFromStats: integer("hide_from_stats", { mode: "boolean" })
.notNull()
.default(false),
autoSyncInterval: integer("auto_sync_interval").notNull().default(0), // 0 = manual, otherwise minutes
isOneTimeImport: integer("is_one_time_import", { mode: "boolean" })
.notNull()
.default(false), // true for uploaded files (no sync), false for URLs (can sync)
lastSyncedAt: integer("last_synced_at", { mode: "timestamp" }),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
});
export const shifts = sqliteTable("shifts", {
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
presetId: text("preset_id").references(() => shiftPresets.id, {
onDelete: "set null",
}),
date: integer("date", { mode: "timestamp" }).notNull(),
startTime: text("start_time").notNull(),
endTime: text("end_time").notNull(),
title: text("title").notNull(),
color: text("color").notNull().default("#3b82f6"),
notes: text("notes"),
isAllDay: integer("is_all_day", { mode: "boolean" }).notNull().default(false),
isSecondary: integer("is_secondary", { mode: "boolean" })
.notNull()
.default(false),
// Target headcount for shift signups; null = unlimited
signupCapacity: integer("signup_capacity"),
externalEventId: text("external_event_id"),
externalSyncId: text("external_sync_id").references(() => externalSyncs.id, {
onDelete: "cascade",
}),
syncedFromExternal: integer("synced_from_external", { mode: "boolean" })
.notNull()
.default(false),
// null = no known creator (pre-migration data, anonymous guest, or the
// creating user was later deleted) — counts as "own" for anyone with the
// Own capability, see hasOwnedCapability().
createdBy: text("created_by").references(() => user.id, {
onDelete: "set null",
}),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
});
export const shiftPresets = sqliteTable("shift_presets", {
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
title: text("title").notNull(),
startTime: text("start_time").notNull(),
endTime: text("end_time").notNull(),
color: text("color").notNull().default("#3b82f6"),
notes: text("notes"),
groupName: text("group_name"),
isSecondary: integer("is_secondary", { mode: "boolean" })
.notNull()
.default(false),
isAllDay: integer("is_all_day", { mode: "boolean" }).notNull().default(false),
hideFromStats: integer("hide_from_stats", { mode: "boolean" })
.notNull()
.default(false),
// Copied into signupCapacity on shifts created from this preset
defaultSignupCapacity: integer("default_signup_capacity"),
order: integer("order").notNull().default(0),
// null = active. Archived presets can no longer create shifts, but shifts
// already stamped from them stay untouched.
archivedAt: integer("archived_at", { mode: "timestamp" }),
// null = no known creator, see shifts.createdBy above.
createdBy: text("created_by").references(() => user.id, {
onDelete: "set null",
}),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
});
export const shiftTimeSegments = sqliteTable(
"shift_time_segments",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
shiftId: text("shift_id")
.notNull()
.references(() => shifts.id, { onDelete: "cascade" }),
startTime: text("start_time").notNull(),
endTime: text("end_time").notNull(),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
},
(table) => [index("shift_time_segments_shiftId_idx").on(table.shiftId)]
);
export const presetTimeSegments = sqliteTable(
"preset_time_segments",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
presetId: text("preset_id")
.notNull()
.references(() => shiftPresets.id, { onDelete: "cascade" }),
startTime: text("start_time").notNull(),
endTime: text("end_time").notNull(),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
},
(table) => [index("preset_time_segments_presetId_idx").on(table.presetId)]
);
export const calendarCustomFields = sqliteTable(
"calendar_custom_fields",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
// Stable API identifier: `shift.customFields[key]`. Immutable once created,
// because renaming it would break every consumer reading that key.
key: text("key").notNull(),
label: text("label").notNull(),
type: text("type").notNull().$type<CustomFieldType>(),
// Only set for type "select"; null for every other type.
options: text("options", { mode: "json" }).$type<CustomFieldOption[]>(),
required: integer("required", { mode: "boolean" }).notNull().default(false),
showInCalendar: integer("show_in_calendar", { mode: "boolean" })
.notNull()
.default(false),
order: integer("order").notNull().default(0),
// null = no known creator, see shifts.createdBy above.
createdBy: text("created_by").references(() => user.id, {
onDelete: "set null",
}),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`)
.$onUpdate(() => new Date()),
},
(table) => [
index("calendar_custom_fields_calendarId_idx").on(table.calendarId),
uniqueIndex("calendar_custom_fields_calendarId_key_idx").on(
table.calendarId,
table.key
),
]
);
export const shiftCustomFieldValues = sqliteTable(
"shift_custom_field_values",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
shiftId: text("shift_id")
.notNull()
.references(() => shifts.id, { onDelete: "cascade" }),
fieldId: text("field_id")
.notNull()
.references(() => calendarCustomFields.id, { onDelete: "cascade" }),
value: text("value").notNull(),
},
(table) => [
index("shift_custom_field_values_shiftId_idx").on(table.shiftId),
uniqueIndex("shift_custom_field_values_shiftId_fieldId_idx").on(
table.shiftId,
table.fieldId
),
]
);
export const presetCustomFieldValues = sqliteTable(
"preset_custom_field_values",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
presetId: text("preset_id")
.notNull()
.references(() => shiftPresets.id, { onDelete: "cascade" }),
fieldId: text("field_id")
.notNull()
.references(() => calendarCustomFields.id, { onDelete: "cascade" }),
value: text("value").notNull(),
},
(table) => [
index("preset_custom_field_values_presetId_idx").on(table.presetId),
uniqueIndex("preset_custom_field_values_presetId_fieldId_idx").on(
table.presetId,
table.fieldId
),
]
);
export const calendarNotes = sqliteTable("calendar_notes", {
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
date: integer("date", { mode: "timestamp" }).notNull(),
note: text("note").notNull(),
type: text("type").notNull().default("note"), // note or event
color: text("color"), // for events only
recurringPattern: text("recurring_pattern").notNull().default("none"), // none, custom-weeks, custom-months
recurringInterval: integer("recurring_interval"), // for custom pattern (e.g., every 3 months)
// null = no known creator, see shifts.createdBy above.
createdBy: text("created_by").references(() => user.id, {
onDelete: "set null",
}),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
});
export const announcements = sqliteTable("announcements", {
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
title: text("title").notNull(),
body: text("body"),
tone: text("tone", { enum: ["info", "warning", "danger"] })
.notNull()
.default("info"),
showOnAuth: integer("show_on_auth", { mode: "boolean" }).notNull().default(true),
showOnDashboard: integer("show_on_dashboard", { mode: "boolean" })
.notNull()
.default(true),
enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
// Both null means the enabled flag alone decides visibility.
startsAt: integer("starts_at", { mode: "timestamp" }),
endsAt: integer("ends_at", { mode: "timestamp" }),
// null = no known creator, see calendarNotes.createdBy above.
createdBy: text("created_by").references(() => user.id, {
onDelete: "set null",
}),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
updatedAt: integer("updated_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`)
.$onUpdate(() => new Date()),
});
export const syncLogs = sqliteTable("sync_logs", {
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
externalSyncId: text("external_sync_id")
.notNull()
.references(() => externalSyncs.id, { onDelete: "cascade" }),
externalSyncName: text("external_sync_name").notNull(), // Denormalized for history
status: text("status").notNull(), // success, error
errorMessage: text("error_message"),
shiftsCreated: integer("shifts_created").notNull().default(0),
shiftsUpdated: integer("shifts_updated").notNull().default(0),
shiftsDeleted: integer("shifts_deleted").notNull().default(0),
syncType: text("sync_type").notNull().default("auto"), // auto, manual
isRead: integer("is_read", { mode: "boolean" }).notNull().default(false),
syncedAt: integer("synced_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
});
export const auditLogs = sqliteTable(
"audit_logs",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: text("user_id").references(() => user.id, {
onDelete: "set null",
}),
action: text("action").notNull(),
resourceType: text("resource_type"),
resourceId: text("resource_id"),
metadata: text("metadata"), // JSON string
ipAddress: text("ip_address"),
userAgent: text("user_agent"),
severity: text("severity").notNull().default("info"), // info, warning, error, critical
isUserVisible: integer("is_user_visible", { mode: "boolean" })
.notNull()
.default(false),
timestamp: integer("timestamp", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
},
(table) => [
index("audit_logs_userId_timestamp_idx").on(table.userId, table.timestamp),
index("audit_logs_action_timestamp_idx").on(table.action, table.timestamp),
index("audit_logs_userVisible_userId_timestamp_idx").on(
table.isUserVisible,
table.userId,
table.timestamp
),
]
);
export const calendarAccessTokens = sqliteTable(
"calendar_access_tokens",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
token: text("token").notNull().unique(), // base64url encoded, 43 chars
name: text("name"), // Optional label (e.g., "Family Link")
// restrict, not cascade: see calendarShares.bundleId above.
bundleId: text("bundle_id")
.notNull()
.references(() => calendarPermissionBundles.id, {
onDelete: "restrict",
}),
expiresAt: integer("expires_at", { mode: "timestamp" }), // null = never expires
createdBy: text("created_by")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
lastUsedAt: integer("last_used_at", { mode: "timestamp" }),
usageCount: integer("usage_count").notNull().default(0),
isActive: integer("is_active", { mode: "boolean" }).notNull().default(true),
},
(table) => [
index("calendar_access_tokens_token_idx").on(table.token),
index("calendar_access_tokens_calendarId_isActive_idx").on(
table.calendarId,
table.isActive
),
index("calendar_access_tokens_createdBy_idx").on(table.createdBy),
index("calendar_access_tokens_bundleId_idx").on(table.bundleId),
]
);
export const calendarFeedTokens = sqliteTable(
"calendar_feed_tokens",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
calendarId: text("calendar_id")
.notNull()
.references(() => calendars.id, { onDelete: "cascade" }),
// null only while AUTH_ENABLED=false, where there are no accounts
userId: text("user_id").references(() => user.id, { onDelete: "cascade" }),
token: text("token").notNull().unique(),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
lastUsedAt: integer("last_used_at", { mode: "timestamp" }),
},
(table) => [
index("calendar_feed_tokens_calendarId_userId_idx").on(
table.calendarId,
table.userId
),
]
);
export const shiftSignups = sqliteTable(
"shift_signups",
{
id: text("id")
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
shiftId: text("shift_id")
.notNull()
.references(() => shifts.id, { onDelete: "cascade" }),
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
// Who created this signup: the user themself, or an admin/write-permission
// user who assigned someone else. Distinct from userId for that reason.
signedUpBy: text("signed_up_by")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
createdAt: integer("created_at", { mode: "timestamp" })
.notNull()
.default(sql`CURRENT_TIMESTAMP`),
},
(table) => [
index("shift_signups_shiftId_idx").on(table.shiftId),
index("shift_signups_userId_idx").on(table.userId),
uniqueIndex("shift_signups_shiftId_userId_idx").on(
table.shiftId,
table.userId
),
]
);
export type Calendar = typeof calendars.$inferSelect;
export type NewCalendar = typeof calendars.$inferInsert;
export type CalendarAccessToken = typeof calendarAccessTokens.$inferSelect;
export type NewCalendarAccessToken = typeof calendarAccessTokens.$inferInsert;
export type CalendarFeedToken = typeof calendarFeedTokens.$inferSelect;
export type CalendarPermissionBundle =
typeof calendarPermissionBundles.$inferSelect;
export type NewCalendarPermissionBundle =
typeof calendarPermissionBundles.$inferInsert;
export type ExternalSync = typeof externalSyncs.$inferSelect;
export type NewExternalSync = typeof externalSyncs.$inferInsert;
export type AuditLog = typeof auditLogs.$inferSelect;
export type NewAuditLog = typeof auditLogs.$inferInsert;
export type Shift = typeof shifts.$inferSelect;
export type NewShift = typeof shifts.$inferInsert;
export type ShiftPreset = typeof shiftPresets.$inferSelect;
export type NewShiftPreset = typeof shiftPresets.$inferInsert;
export type ShiftTimeSegment = typeof shiftTimeSegments.$inferSelect;
export type NewShiftTimeSegment = typeof shiftTimeSegments.$inferInsert;
export type PresetTimeSegment = typeof presetTimeSegments.$inferSelect;
export type NewPresetTimeSegment = typeof presetTimeSegments.$inferInsert;
export type CalendarNote = typeof calendarNotes.$inferSelect;
export type NewCalendarNote = typeof calendarNotes.$inferInsert;
export type SyncLog = typeof syncLogs.$inferSelect;
export type NewSyncLog = typeof syncLogs.$inferInsert;
export type ShiftSignup = typeof shiftSignups.$inferSelect;
export type NewShiftSignup = typeof shiftSignups.$inferInsert;
// Better Auth types
export type User = typeof user.$inferSelect;
export type NewUser = typeof user.$inferInsert;
export type Session = typeof session.$inferSelect;
export type Account = typeof account.$inferSelect;
export type CalendarShare = typeof calendarShares.$inferSelect;
export type NewCalendarShare = typeof calendarShares.$inferInsert;
export type UserPreferences = typeof userPreferences.$inferSelect;
export type NewUserPreferences = typeof userPreferences.$inferInsert;
export type UserCalendarSubscription =
typeof userCalendarSubscriptions.$inferSelect;
export type NewUserCalendarSubscription =
typeof userCalendarSubscriptions.$inferInsert;
// =====================================================
// Drizzle Relations (for experimental joins)
// =====================================================
export const userRelations = relations(user, ({ one, many }) => ({
preferences: one(userPreferences),
sessions: many(session),
accounts: many(account),
ownedCalendars: many(calendars),
calendarShares: many(calendarShares),
calendarSubscriptions: many(userCalendarSubscriptions),
feedTokens: many(calendarFeedTokens),
auditLogs: many(auditLogs),
}));
export const sessionRelations = relations(session, ({ one }) => ({
user: one(user, {
fields: [session.userId],
references: [user.id],
}),
}));
export const accountRelations = relations(account, ({ one }) => ({
user: one(user, {
fields: [account.userId],
references: [user.id],
}),
}));
export const calendarsRelations = relations(calendars, ({ one, many }) => ({
owner: one(user, {
fields: [calendars.ownerId],
references: [user.id],
}),
shares: many(calendarShares),
subscriptions: many(userCalendarSubscriptions),
accessTokens: many(calendarAccessTokens),
feedTokens: many(calendarFeedTokens),
permissionBundles: many(calendarPermissionBundles),
guestBundle: one(calendarPermissionBundles, {
fields: [calendars.guestBundleId],
references: [calendarPermissionBundles.id],
}),
shifts: many(shifts),
presets: many(shiftPresets),
notes: many(calendarNotes),
externalSyncs: many(externalSyncs),
syncLogs: many(syncLogs),
}));
export const calendarPermissionBundlesRelations = relations(
calendarPermissionBundles,
({ one, many }) => ({
calendar: one(calendars, {
fields: [calendarPermissionBundles.calendarId],
references: [calendars.id],
}),
shares: many(calendarShares),
accessTokens: many(calendarAccessTokens),
})
);
export const shiftsRelations = relations(shifts, ({ one, many }) => ({
calendar: one(calendars, {
fields: [shifts.calendarId],
references: [calendars.id],
}),
preset: one(shiftPresets, {
fields: [shifts.presetId],
references: [shiftPresets.id],
}),
signups: many(shiftSignups),
segments: many(shiftTimeSegments),
creator: one(user, {
fields: [shifts.createdBy],
references: [user.id],
}),
}));
export const shiftPresetsRelations = relations(shiftPresets, ({ one, many }) => ({
calendar: one(calendars, {
fields: [shiftPresets.calendarId],
references: [calendars.id],
}),
segments: many(presetTimeSegments),
creator: one(user, {
fields: [shiftPresets.createdBy],
references: [user.id],
}),
}));
export const shiftTimeSegmentsRelations = relations(
shiftTimeSegments,
({ one }) => ({
shift: one(shifts, {
fields: [shiftTimeSegments.shiftId],
references: [shifts.id],
}),
})
);
export const presetTimeSegmentsRelations = relations(
presetTimeSegments,
({ one }) => ({
preset: one(shiftPresets, {
fields: [presetTimeSegments.presetId],
references: [shiftPresets.id],
}),
})
);
export const calendarNotesRelations = relations(calendarNotes, ({ one }) => ({
calendar: one(calendars, {
fields: [calendarNotes.calendarId],
references: [calendars.id],
}),
creator: one(user, {
fields: [calendarNotes.createdBy],
references: [user.id],
}),
}));
export const shiftSignupsRelations = relations(shiftSignups, ({ one }) => ({
shift: one(shifts, {
fields: [shiftSignups.shiftId],
references: [shifts.id],
}),
user: one(user, {
fields: [shiftSignups.userId],
references: [user.id],
}),
signedUpByUser: one(user, {
fields: [shiftSignups.signedUpBy],
references: [user.id],
}),
}));
export const calendarSharesRelations = relations(calendarShares, ({ one }) => ({
calendar: one(calendars, {
fields: [calendarShares.calendarId],
references: [calendars.id],
}),
user: one(user, {
fields: [calendarShares.userId],
references: [user.id],
}),
sharedByUser: one(user, {
fields: [calendarShares.sharedBy],
references: [user.id],
}),
bundle: one(calendarPermissionBundles, {
fields: [calendarShares.bundleId],
references: [calendarPermissionBundles.id],
}),
}));
export const userCalendarSubscriptionsRelations = relations(
userCalendarSubscriptions,
({ one }) => ({
user: one(user, {
fields: [userCalendarSubscriptions.userId],
references: [user.id],
}),
calendar: one(calendars, {
fields: [userCalendarSubscriptions.calendarId],
references: [calendars.id],
}),
})
);
export const userPreferencesRelations = relations(
userPreferences,
({ one }) => ({
user: one(user, {
fields: [userPreferences.userId],
references: [user.id],
}),
})
);
export const auditLogsRelations = relations(auditLogs, ({ one }) => ({
user: one(user, {
fields: [auditLogs.userId],
references: [user.id],
}),
}));
export const announcementsRelations = relations(announcements, ({ one }) => ({
createdByUser: one(user, {
fields: [announcements.createdBy],
references: [user.id],
}),
}));
export const calendarAccessTokensRelations = relations(
calendarAccessTokens,
({ one }) => ({
calendar: one(calendars, {
fields: [calendarAccessTokens.calendarId],
references: [calendars.id],
}),
createdByUser: one(user, {
fields: [calendarAccessTokens.createdBy],
references: [user.id],
}),
bundle: one(calendarPermissionBundles, {
fields: [calendarAccessTokens.bundleId],
references: [calendarPermissionBundles.id],
}),
})
);
export const calendarFeedTokensRelations = relations(
calendarFeedTokens,
({ one }) => ({
calendar: one(calendars, {
fields: [calendarFeedTokens.calendarId],
references: [calendars.id],
}),
user: one(user, {
fields: [calendarFeedTokens.userId],
references: [user.id],
}),
})
);