import { createId } from "@paralleldrive/cuid2"; import { sql } from "drizzle-orm"; import { index, integer, sqliteTable, text, uniqueIndex, } from "drizzle-orm/sqlite-core"; import type { PaymentMethod } from "../invoice/types"; import type { TimelineEventMetadata } from "../types/timeline"; // ============================================================================= // BETTER AUTH TABLES — Generated by `bunx @better-auth/cli generate` // NOTE: timestamp columns use timestamp_ms (milliseconds) to match Better Auth internals. // Existing data stored as seconds will need migration (multiply by 1000). // ============================================================================= 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(), pubkey: text("pubkey").unique(), bapId: text("bap_id").unique(), githubLogin: text("github_login"), stripeCustomerId: text("stripe_customer_id"), tourCompleted: integer("tour_completed", { mode: "boolean" }).default(false), onboardingCompleted: integer("onboarding_completed", { mode: "boolean", }).default(false), // Better Auth admin plugin fields roles: text("roles").default("user"), role: text("role"), banned: integer("banned", { mode: "boolean" }).default(false), banReason: text("ban_reason"), banExpires: integer("ban_expires", { mode: "timestamp_ms" }), }); 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" }) .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`) .$onUpdate(() => new Date()) .notNull(), ipAddress: text("ip_address"), userAgent: text("user_agent"), userId: text("user_id") .notNull() .references(() => user.id, { onDelete: "cascade" }), activeOrganizationId: text("active_organization_id"), // Better Auth admin plugin fields roles: text("roles"), impersonatedBy: text("impersonated_by"), }, (table) => [index("session_userId_idx").on(table.userId)], ); export const apikey = sqliteTable( "apikey", { id: text("id").primaryKey(), configId: text("config_id").default("default").notNull(), name: text("name"), start: text("start"), referenceId: text("reference_id").notNull(), prefix: text("prefix"), key: text("key").notNull(), refillInterval: integer("refill_interval"), refillAmount: integer("refill_amount"), lastRefillAt: integer("last_refill_at", { mode: "timestamp_ms" }), enabled: integer("enabled", { mode: "boolean" }).default(true), rateLimitEnabled: integer("rate_limit_enabled", { mode: "boolean", }).default(true), rateLimitTimeWindow: integer("rate_limit_time_window").default(86400000), rateLimitMax: integer("rate_limit_max").default(10), requestCount: integer("request_count").default(0), remaining: integer("remaining"), lastRequest: integer("last_request", { mode: "timestamp_ms" }), expiresAt: integer("expires_at", { mode: "timestamp_ms" }), createdAt: integer("created_at", { mode: "timestamp_ms" }).notNull(), updatedAt: integer("updated_at", { mode: "timestamp_ms" }).notNull(), permissions: text("permissions"), metadata: text("metadata"), }, (table) => [ index("apikey_configId_idx").on(table.configId), index("apikey_referenceId_idx").on(table.referenceId), index("apikey_key_idx").on(table.key), ], ); 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" }) .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`) .$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" }) .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`) .$onUpdate(() => new Date()) .notNull(), }, (table) => [index("verification_identifier_idx").on(table.identifier)], ); export const organization = sqliteTable( "organization", { id: text("id").primaryKey(), stripeCustomerId: text("stripe_customer_id"), name: text("name").notNull(), slug: text("slug").notNull().unique(), logo: text("logo"), // Team accent/theme color (strict hex or numeric oklch — validated with // isValidProjectColor at the write boundary). Drives the active team's // primary/accent color in app chrome (OPL-3011). Nullable: a team with no // stored color falls back to a deterministic per-team default. themeColor: text("theme_color"), createdAt: integer("created_at", { mode: "timestamp_ms" }).notNull(), metadata: text("metadata"), // True for the org auto-provisioned at signup (the user's default/initial // team). It's a normal team in every other respect — this flag just marks // which one was created for the user, so entitlement logic doesn't have to // reverse-engineer it from the (mutable) slug. isDefault: integer("is_default", { mode: "boolean" }) .notNull() .default(false), }, (table) => [uniqueIndex("organization_slug_uidx").on(table.slug)], ); export const member = sqliteTable( "member", { id: text("id").primaryKey(), organizationId: text("organization_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), userId: text("user_id") .notNull() .references(() => user.id, { onDelete: "cascade" }), role: text("role").default("member").notNull(), createdAt: integer("created_at", { mode: "timestamp_ms" }).notNull(), }, (table) => [ index("member_organizationId_idx").on(table.organizationId), index("member_userId_idx").on(table.userId), ], ); /** * Per-organization custom role definitions. * * Managed by Better Auth's organization plugin when `dynamicAccessControl` * is enabled (see `apps/web/src/lib/hosted/auth-server.ts`). Default * owner/admin/member rows are seeded on org creation via the * `organization.create.after` hook; custom named roles can be created via * `auth.api.createOrgRole`. * * `permission` is JSON-serialized `Record` (statement → * actions), e.g. `{"invoices":["view","create"],"timeline":["view"]}`. */ export const organizationRole = sqliteTable( "organization_role", { id: text("id").primaryKey(), organizationId: text("organization_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), role: text("role").notNull(), permission: text("permission").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(), ), }, (table) => [ index("organization_role_organizationId_idx").on(table.organizationId), index("organization_role_role_idx").on(table.role), ], ); export const invitation = sqliteTable( "invitation", { id: text("id").primaryKey(), organizationId: text("organization_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), email: text("email").notNull(), role: text("role"), status: text("status").default("pending").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(), inviterId: text("inviter_id") .notNull() .references(() => user.id, { onDelete: "cascade" }), }, (table) => [ index("invitation_organizationId_idx").on(table.organizationId), index("invitation_email_idx").on(table.email), ], ); // NOTE: the real `oauth_client` table (Better Auth OAuth Provider plugin) is // defined in the OAUTH PROVIDER section at the end of this file. A legacy // 3-column stub ({ ownerBapId, memberPubkey }) previously lived here; it was // unused dead code (no source references, empty table) and was replaced by // the plugin's schema. export const deviceCode = sqliteTable("device_code", { id: text("id").primaryKey(), deviceCode: text("device_code").notNull(), userCode: text("user_code").notNull(), userId: text("user_id"), expiresAt: integer("expires_at", { mode: "timestamp_ms" }).notNull(), status: text("status").notNull(), lastPolledAt: integer("last_polled_at", { mode: "timestamp_ms" }), pollingInterval: integer("polling_interval"), clientId: text("client_id"), scope: text("scope"), }); // Link-based invites (custom shareable invite links for organizations) // This is separate from Better Auth's email-based invitation table export const linkInvites = sqliteTable( "link_invites", { id: text("id") .primaryKey() .$defaultFn(() => createId()), organizationId: text("organization_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), token: text("token") .notNull() .unique() .$defaultFn(() => createId()), createdBy: text("created_by").notNull(), expiresAt: integer("expires_at", { mode: "timestamp" }), maxUses: integer("max_uses"), useCount: integer("use_count").notNull().default(0), revoked: integer("revoked", { mode: "boolean" }).notNull().default(false), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("link_invites_organization_idx").on(table.organizationId), index("link_invites_token_idx").on(table.token), ], ); // Waitlist entries (pre-user, not tied to any organization) // Captures emails before users exist for Phase 2 of OPL-2127 waitlist gating export const waitlistEntries = sqliteTable( "waitlist_entries", { id: text("id") .primaryKey() .$defaultFn(() => createId()), email: text("email").notNull().unique(), status: text("status", { enum: ["pending", "invited", "admitted", "signed_up"], }) .notNull() .default("pending"), source: text("source"), // utm_source, referrer, or hardcoded "marketing-hero" etc. useCase: text("use_case"), // self-reported "how do you plan to use Scribe?" from the post-signup dialog notes: text("notes"), // admin-visible internal notes inviteTokenHash: text("invite_token_hash"), // SHA-256 of last-issued invite token inviteExpiresAt: integer("invite_expires_at", { mode: "timestamp" }), createdAt: integer("created_at", { mode: "timestamp" }) .notNull() .$defaultFn(() => new Date()), invitedAt: integer("invited_at", { mode: "timestamp" }), admittedAt: integer("admitted_at", { mode: "timestamp" }), signedUpAt: integer("signed_up_at", { mode: "timestamp" }), }, (table) => [ index("waitlist_status_idx").on(table.status), index("waitlist_created_idx").on(table.createdAt), index("waitlist_email_idx").on(table.email), // already unique but explicit index helps lookups ], ); // Devices (for hosted mode multi-device support) export const devices = sqliteTable( "devices", { id: text("id") .primaryKey() .$defaultFn(() => createId()), name: text("name").notNull(), // User-friendly device name userId: text("user_id").notNull(), // Owner's user ID organizationId: text("organization_id").references(() => organization.id, { onDelete: "set null", }), // Which org this device syncs to machineId: text("machine_id").unique(), // Hardware identifier - unique per device lastSyncAt: integer("last_sync_at", { mode: "timestamp" }), // Heartbeat metadata - tracks local device state localEventCount: integer("local_event_count"), // Event count from last heartbeat localLatestTs: integer("local_latest_ts", { mode: "timestamp" }), // Latest event timestamp from local // Sync request flag - set by hub, cleared after sync syncRequested: integer("sync_requested", { mode: "boolean" }).default( false, ), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("devices_user_idx").on(table.userId), index("devices_organization_idx").on(table.organizationId), ], ); // Root folders to scan export const scanRoots = sqliteTable( "scan_roots", { id: text("id") .primaryKey() .$defaultFn(() => createId()), path: text("path").notNull(), type: text("type", { enum: [ "git", "files", "claude", "claude_desktop", "gemini", "codex", "opencode", "cursor", "cline", "amp", "copilot", "grok", "linear", ], }).notNull(), // New provider-based ingestion fields (kept alongside `type` for compatibility) providerId: text("provider_id"), rootType: text("root_type").default("path"), providerConfig: text("provider_config", { mode: "json" }), lastCursor: text("last_cursor"), capabilities: text("capabilities", { mode: "json" }), enabled: integer("enabled", { mode: "boolean" }).default(true), lastScanned: integer("last_scanned", { mode: "timestamp" }), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [index("scan_roots_path_type_idx").on(table.path, table.type)], ); // Clients table (for invoicing) export const clients = sqliteTable( "clients", { id: text("id") .primaryKey() .$defaultFn(() => createId()), userId: text("user_id").notNull(), organizationId: text("organization_id").references(() => organization.id, { onDelete: "cascade", }), name: text("name").notNull(), // Short uppercase code used to prefix invoice numbers, e.g. "CJM" -> // CJM-2026-05-01. Unique per user; nullable so existing clients // (and inline-created ones) keep the legacy INV-{year}-{seq} scheme until // an abbreviation is assigned. abbreviation: text("abbreviation"), email: text("email"), phone: text("phone"), address: text("address"), // Full address block taxId: text("tax_id"), // VAT/Tax ID notes: text("notes"), defaultRate: integer("default_rate"), // Cents per hour currency: text("currency").default("USD"), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("clients_user_idx").on(table.userId), index("clients_org_idx").on(table.organizationId), uniqueIndex("clients_user_abbreviation_uidx").on( table.userId, table.abbreviation, ), ], ); // Logical project groupings (can contain multiple project roots) export const projects = sqliteTable( "projects", { id: text("id") .primaryKey() .$defaultFn(() => createId()), name: text("name").notNull(), color: text("color"), // Hex color for timeline dots icon: text("icon"), // Lucide icon name (e.g., "code", "folder") or emoji summary: text("summary"), // AI-generated project summary summaryGeneratedAt: integer("summary_generated_at", { mode: "timestamp" }), // When summary was last generated organizationId: text("organization_id").references(() => organization.id, { onDelete: "cascade", }), // Invoicing fields clientId: text("client_id").references(() => clients.id, { onDelete: "set null", }), hourlyRate: integer("hourly_rate"), // Cents per hour, overrides client default billable: integer("billable", { mode: "boolean" }).default(true), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("projects_name_idx").on(table.name), index("projects_org_idx").on(table.organizationId), ], ); // Discovered project roots (git repos, package.json locations, etc.) export const projectRoots = sqliteTable( "project_roots", { id: text("id") .primaryKey() .$defaultFn(() => createId()), scanRootId: text("scan_root_id").references(() => scanRoots.id, { onDelete: "cascade", }), projectId: text("project_id").references(() => projects.id, { onDelete: "set null", }), // True when a hub user manually assigned/reassigned this root to a project // (AI cleanup merge or a manual move). The push-only desktop sync must then // NOT overwrite that assignment from the device's local grouping — the // human's cloud decision wins. Git-backed roots already stay durable via // remote matching; this is what makes NON-git cloud reorg survive sync too. manuallyAssigned: integer("manually_assigned", { mode: "boolean" }) .notNull() .default(false), deviceId: text("device_id").references(() => devices.id, { onDelete: "cascade", }), // Hosted mode: which device this root came from path: text("path").notNull(), detectedName: text("detected_name"), // From package.json, folder name, etc. gitRemote: text("git_remote"), // Git remote origin URL (e.g., github.com/user/repo) peacockColor: text("peacock_color"), // VS Code Peacock color from .vscode/settings.json createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("project_roots_path_device_idx").on(table.path, table.deviceId), index("project_roots_project_id_idx").on(table.projectId), ], ); // GitHub App installations connected by a hosted organization for ingestion. // This stores only non-secret installation metadata; installation access tokens // are short-lived and generated on demand. export const githubInstallations = sqliteTable( "github_installations", { id: text("id") .primaryKey() .$defaultFn(() => createId()), organizationId: text("organization_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), installedByUserId: text("installed_by_user_id").references(() => user.id, { onDelete: "set null", }), installationId: text("installation_id").notNull(), accountLogin: text("account_login"), accountId: integer("account_id"), accountType: text("account_type"), repositorySelection: text("repository_selection"), permissions: text("permissions", { mode: "json" }), installedAt: integer("installed_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ uniqueIndex("github_installations_org_uidx").on(table.organizationId), uniqueIndex("github_installations_installation_uidx").on( table.installationId, ), index("github_installations_org_idx").on(table.organizationId), ], ); // GitHub repositories selected by a hosted organization for ingestion. // This stores non-secret repository metadata; GitHub App installation tokens // are intentionally not persisted here. export const githubRepositories = sqliteTable( "github_repositories", { id: text("id") .primaryKey() .$defaultFn(() => createId()), organizationId: text("organization_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), projectId: text("project_id").references(() => projects.id, { onDelete: "set null", }), installationId: text("installation_id"), githubRepoId: integer("github_repo_id").notNull(), owner: text("owner").notNull(), name: text("name").notNull(), fullName: text("full_name").notNull(), private: integer("private", { mode: "boolean" }).default(false).notNull(), defaultBranch: text("default_branch"), htmlUrl: text("html_url"), selected: integer("selected", { mode: "boolean" }).default(true).notNull(), lastIngestedAt: integer("last_ingested_at", { mode: "timestamp" }), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ uniqueIndex("github_repositories_org_repo_uidx").on( table.organizationId, table.githubRepoId, ), index("github_repositories_org_idx").on(table.organizationId), index("github_repositories_project_idx").on(table.projectId), index("github_repositories_full_name_idx").on(table.fullName), ], ); // Canonical git commit store — one row per commit per organization, regardless // of how many devices/users synced it or whether it was pulled from GitHub. // `timeline_events.commitKey` references `commitKey` here; the heavy commit data // lives here once instead of being duplicated on every event row. export const gitCommits = sqliteTable( "git_commits", { id: text("id") .primaryKey() .$defaultFn(() => createId()), organizationId: text("organization_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), // `:` — stable across ingesters. commitKey: text("commit_key").notNull(), sha: text("sha").notNull(), gitRemote: text("git_remote"), repoFullName: text("repo_full_name"), message: text("message"), authorName: text("author_name"), authorEmail: text("author_email"), authoredAt: integer("authored_at", { mode: "timestamp" }), additions: integer("additions"), deletions: integer("deletions"), filesChanged: text("files_changed", { mode: "json" }).$type(), htmlUrl: text("html_url"), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ uniqueIndex("git_commits_org_key_uidx").on( table.organizationId, table.commitKey, ), index("git_commits_org_idx").on(table.organizationId), index("git_commits_key_idx").on(table.commitKey), ], ); // Provenance for a canonical commit: a thin record per ingestion source so a // commit "tied together" across teammates (local sync) and the hub (GitHub // pull) without duplicating the commit itself. `source` is "local" | "github". export const commitProvenance = sqliteTable( "commit_provenance", { // Deterministic id (`sha256(commitId:source:deviceId)`) so re-ingesting the // same source updates attribution instead of duplicating provenance. id: text("id").primaryKey(), commitId: text("commit_id") .notNull() .references(() => gitCommits.id, { onDelete: "cascade" }), source: text("source").notNull(), deviceId: text("device_id"), userId: text("user_id"), ingestedAt: integer("ingested_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [index("commit_provenance_commit_idx").on(table.commitId)], ); export type GitCommitRecord = typeof gitCommits.$inferSelect; export type NewGitCommitRecord = typeof gitCommits.$inferInsert; export type CommitProvenanceRecord = typeof commitProvenance.$inferSelect; export type NewCommitProvenanceRecord = typeof commitProvenance.$inferInsert; // Project associations (path patterns to project mapping) export const projectAssociations = sqliteTable("project_associations", { id: text("id") .primaryKey() .$defaultFn(() => createId()), projectRootId: text("project_root_id").references(() => projectRoots.id, { onDelete: "cascade", }), pattern: text("pattern").notNull(), // Glob or path prefix priority: integer("priority").default(0), // Higher = takes precedence }); // Explicit Linear team/project -> Scribe project links. // // Linear is an API source with no local folder, so its timeline events cannot // be attributed to a project by path/folder matching like every git-backed // source. Instead a Linear project name is linked to a Scribe project here, and // timeline/report project filters resolve mapped Linear events through this // table (see OPL-2675). `linearProjectName` matches `LinearMetadata.projectName` // on a `source = "linear"` timeline row (case-insensitive at resolve time). export const linearProjectMappings = sqliteTable( "linear_project_mappings", { id: text("id") .primaryKey() .$defaultFn(() => createId()), // Null on single-tenant desktop; scoped to the org on the hub. organizationId: text("organization_id").references(() => organization.id, { onDelete: "cascade", }), linearProjectName: text("linear_project_name").notNull(), projectId: text("project_id") .notNull() .references(() => projects.id, { onDelete: "cascade" }), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ // One Linear project name maps to at most one Scribe project per tenant. uniqueIndex("linear_project_mappings_org_name_idx").on( table.organizationId, table.linearProjectName, ), index("linear_project_mappings_project_idx").on(table.projectId), ], ); export type LinearProjectMapping = typeof linearProjectMappings.$inferSelect; export type NewLinearProjectMapping = typeof linearProjectMappings.$inferInsert; // Timeline events (the dots) export const timelineEvents = sqliteTable( "timeline_events", { id: text("id") .primaryKey() .$defaultFn(() => createId()), timestamp: integer("timestamp", { mode: "timestamp" }).notNull(), source: text("source", { enum: [ "git", "file", "claude", "claude_desktop", "gemini", "codex", "opencode", "cursor", "cline", "amp", "copilot", "grok", "linear", "manual", ], }).notNull(), // New provider/event model fields (kept alongside `source` for compatibility) providerId: text("provider_id"), eventType: text("event_type").default("message"), externalThreadId: text("external_thread_id"), externalMessageId: text("external_message_id"), projectRootId: text("project_root_id").references(() => projectRoots.id, { onDelete: "set null", }), deviceId: text("device_id").references(() => devices.id, { onDelete: "cascade", }), // Hosted mode: which device this event came from // Source-specific identifier for deduplication sourceId: text("source_id"), // Commit hash, file path, conversation ID // Canonical, ingester-independent grouping key for git commits // (`:`). The same commit synced from multiple // devices/users or pulled from GitHub shares this key, so reads can collapse // duplicate dots into one. Null for non-git events. See `git_commits`. commitKey: text("commit_key"), // Flexible metadata as JSON (typed per source) metadata: text("metadata", { mode: "json" }).$type(), // Full-text searchable content (populated from metadata) content: text("content"), contentHash: text("content_hash"), rawRef: text("raw_ref"), // Computed/cached duration estimate estimatedMinutes: integer("estimated_minutes").default(15), // Attestation/auth metadata for remote agent ingestion authMode: text("auth_mode"), attestedBapId: text("attested_bap_id"), attestedSig: text("attested_sig"), ingestNonce: text("ingest_nonce"), // User annotations (preserves data integrity while allowing notes) annotation: text("annotation"), annotatedAt: integer("annotated_at", { mode: "timestamp" }), // For manual entries: track edits editedAt: integer("edited_at", { mode: "timestamp" }), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("timestamp_idx").on(table.timestamp), index("source_idx").on(table.source), index("device_idx").on(table.deviceId), // Composite index for deduplication lookups (sourceId + deviceId) index("events_source_device_idx").on(table.sourceId, table.deviceId), // Composite index for hosted mode queries (filter by device, sort by time) index("events_device_timestamp_idx").on(table.deviceId, table.timestamp), index("events_provider_idx").on(table.providerId), index("events_event_type_idx").on(table.eventType), index("events_external_thread_idx").on(table.externalThreadId), index("events_external_message_idx").on(table.externalMessageId), index("events_commit_key_idx").on(table.commitKey), index("events_content_hash_idx").on(table.contentHash), index("events_ingest_nonce_idx").on(table.ingestNonce), // Composite index for project stats queries (filter by project, sort by time) index("events_project_timestamp_idx").on( table.projectRootId, table.timestamp, ), // Covering index for the hub projects overview aggregates: groups by // (root, device), counts per source, and bounds/MAXes timestamp without // touching table rows. Subsumes the old single-column project_idx. index("events_root_device_source_ts_idx").on( table.projectRootId, table.deviceId, table.source, table.timestamp, ), ], ); // Normalized evidence thread model for provider-agnostic retrieval export const evidenceThreads = sqliteTable( "evidence_threads", { id: text("id") .primaryKey() .$defaultFn(() => createId()), providerId: text("provider_id").notNull(), externalThreadId: text("external_thread_id").notNull(), workspacePath: text("workspace_path"), workspaceHash: text("workspace_hash"), title: text("title"), organizationId: text("organization_id"), startedAt: integer("started_at", { mode: "timestamp" }), endedAt: integer("ended_at", { mode: "timestamp" }), providerMetadata: text("provider_metadata", { mode: "json" }), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), // Shareable conversation links shareToken: text("share_token").unique(), shareExpiresAt: integer("share_expires_at"), shareVisibility: text("share_visibility").default("public"), // "public" | "unlisted" shareViewCount: integer("share_view_count").default(0), }, (table) => [ index("evidence_threads_provider_idx").on(table.providerId), index("evidence_threads_external_idx").on( table.providerId, table.externalThreadId, ), index("evidence_threads_workspace_hash_idx").on(table.workspaceHash), index("evidence_threads_org_idx").on(table.organizationId), index("evidence_threads_share_token_idx").on(table.shareToken), ], ); // Normalized evidence message model for search and full replay export const evidenceMessages = sqliteTable( "evidence_messages", { id: text("id") .primaryKey() .$defaultFn(() => createId()), threadId: text("thread_id") .notNull() .references(() => evidenceThreads.id, { onDelete: "cascade" }), providerId: text("provider_id").notNull(), externalMessageId: text("external_message_id"), role: text("role").notNull(), // user | assistant | tool | system timestamp: integer("timestamp", { mode: "timestamp" }).notNull(), contentText: text("content_text"), contentHash: text("content_hash"), toolName: text("tool_name"), toolStatus: text("tool_status"), tokenIn: integer("token_in"), tokenOut: integer("token_out"), rawJson: text("raw_json", { mode: "json" }), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("evidence_messages_thread_idx").on(table.threadId), index("evidence_messages_provider_idx").on(table.providerId), uniqueIndex("evidence_messages_external_uidx").on( table.providerId, table.externalMessageId, ), index("evidence_messages_timestamp_idx").on(table.timestamp), index("evidence_messages_content_hash_idx").on(table.contentHash), ], ); export const liveActivitySessions = sqliteTable( "live_activity_sessions", { id: text("id") .primaryKey() .$defaultFn(() => createId()), providerId: text("provider_id").notNull(), externalThreadId: text("external_thread_id").notNull(), title: text("title"), projectRootId: text("project_root_id").references(() => projectRoots.id, { onDelete: "set null", }), projectId: text("project_id").references(() => projects.id, { onDelete: "set null", }), projectName: text("project_name"), projectPath: text("project_path"), conversationFile: text("conversation_file"), lastMessagePreview: text("last_message_preview"), lastMessageRole: text("last_message_role"), messageCount: integer("message_count").default(0), lastActivityAt: integer("last_activity_at", { mode: "timestamp", }).notNull(), lastIngestAt: integer("last_ingest_at", { mode: "timestamp" }), state: text("state").default("idle"), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("live_activity_provider_thread_idx").on( table.providerId, table.externalThreadId, ), index("live_activity_last_activity_idx").on(table.lastActivityAt), index("live_activity_state_idx").on(table.state), index("live_activity_project_idx").on(table.projectId), ], ); export const observerState = sqliteTable("observer_state", { id: text("id").primaryKey(), status: text("status").notNull().default("starting"), watchedRootCount: integer("watched_root_count").default(0), watchedRoots: text("watched_roots", { mode: "json" }), recentProjectActivity: text("recent_project_activity", { mode: "json" }), lastHeartbeatAt: integer("last_heartbeat_at", { mode: "timestamp" }), lastError: text("last_error"), lastErrorAt: integer("last_error_at", { mode: "timestamp" }), metadata: text("metadata", { mode: "json" }), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }); // Scan history for incremental processing export const scanHistory = sqliteTable("scan_history", { id: text("id") .primaryKey() .$defaultFn(() => createId()), scanRootId: text("scan_root_id").references(() => scanRoots.id, { onDelete: "cascade", }), startedAt: integer("started_at", { mode: "timestamp" }).notNull(), completedAt: integer("completed_at", { mode: "timestamp" }), eventsFound: integer("events_found").default(0), status: text("status", { enum: ["running", "completed", "failed"], }).default("running"), error: text("error"), }); export const subscription = sqliteTable( "subscription", { id: text("id").primaryKey(), plan: text("plan").notNull(), referenceId: text("reference_id").notNull(), stripeCustomerId: text("stripe_customer_id"), stripeSubscriptionId: text("stripe_subscription_id"), status: text("status").default("incomplete"), periodStart: integer("period_start", { mode: "timestamp_ms" }), periodEnd: integer("period_end", { mode: "timestamp_ms" }), trialStart: integer("trial_start", { mode: "timestamp_ms" }), trialEnd: integer("trial_end", { mode: "timestamp_ms" }), cancelAtPeriodEnd: integer("cancel_at_period_end", { mode: "boolean", }).default(false), cancelAt: integer("cancel_at", { mode: "timestamp_ms" }), canceledAt: integer("canceled_at", { mode: "timestamp_ms" }), endedAt: integer("ended_at", { mode: "timestamp_ms" }), seats: integer("seats"), billingInterval: text("billing_interval"), stripeScheduleId: text("stripe_schedule_id"), }, (table) => [ index("subscription_reference_idx").on(table.referenceId), index("subscription_stripe_customer_idx").on(table.stripeCustomerId), ], ); // Scheduled-job run log (hosted only). Each cron route records its runs here // so the admin dashboard can show per-job history — without this the only // visibility into cron health is Vercel's request logs, which is how four // jobs 401'd daily for weeks unnoticed (missing CRON_SECRET). export const cronRuns = sqliteTable( "cron_runs", { id: text("id") .primaryKey() .$defaultFn(() => createId()), /** Job id, e.g. "email-digest" — matches CRON_JOBS in the web app. */ job: text("job").notNull(), /** "schedule" (Vercel cron) or "manual" (admin Run-now). */ trigger: text("trigger").notNull().default("schedule"), startedAt: integer("started_at", { mode: "timestamp_ms" }).notNull(), finishedAt: integer("finished_at", { mode: "timestamp_ms" }), status: text("status", { enum: ["running", "success", "error"], }) .notNull() .default("running"), /** JSON summary the job returned (sent counts, reconciled counts...). */ result: text("result"), error: text("error"), }, (table) => [ index("cron_runs_job_started_idx").on(table.job, table.startedAt), ], ); // Admin-authored overrides for the canned lifecycle emails (hosted only). // One row per template key; when enabled, the send path interpolates the // stored subject/html with the template's placeholder values instead of // rendering the react-email component. The code template is always the // fallback — deleting a row (or disabling it) reverts to the default. export const emailTemplateOverrides = sqliteTable("email_template_overrides", { /** Template key from the web app's email template registry. */ templateKey: text("template_key").primaryKey(), subject: text("subject").notNull(), html: text("html").notNull(), enabled: integer("enabled", { mode: "boolean" }).notNull().default(false), updatedAt: integer("updated_at", { mode: "timestamp_ms" }).notNull(), /** Admin user id who last saved it. */ updatedBy: text("updated_by"), }); // Append-only history of email template edits. Every save that replaces an // existing custom version archives the prior subject/html here, so an admin // can recover a hand-tuned email after a bad save — the code default is not // the only fallback. Pruned to the most recent versions per template. export const emailTemplateVersions = sqliteTable( "email_template_versions", { id: text("id") .primaryKey() .$defaultFn(() => createId()), templateKey: text("template_key").notNull(), subject: text("subject").notNull(), html: text("html").notNull(), /** Whether this version was live (enabled) when it was replaced. */ wasEnabled: integer("was_enabled", { mode: "boolean" }) .notNull() .default(false), savedAt: integer("saved_at", { mode: "timestamp_ms" }).notNull(), savedBy: text("saved_by"), }, (table) => [ index("email_template_versions_key_saved_idx").on( table.templateKey, table.savedAt, ), ], ); // Sync history for tracking uploads to hosted instance export const syncHistory = sqliteTable("sync_history", { id: text("id") .primaryKey() .$defaultFn(() => createId()), hostUrl: text("host_url").notNull(), deviceId: text("device_id").notNull(), startedAt: integer("started_at", { mode: "timestamp" }).notNull(), completedAt: integer("completed_at", { mode: "timestamp" }), eventsUploaded: integer("events_uploaded").default(0), projectsUploaded: integer("projects_uploaded").default(0), totalBytes: integer("total_bytes").default(0), status: text("status", { enum: ["running", "completed", "failed"], }).default("running"), error: text("error"), }); // Invoice template configuration type export interface InvoiceTemplateConfig { // Branding logo?: string; // Base64 or URL companyName: string; companyAddress?: string; companyEmail?: string; companyPhone?: string; // Colors primaryColor: string; accentColor: string; textColor: string; // Layout headerStyle: "classic" | "modern" | "minimal"; showLogo: boolean; showCompanyDetails: boolean; // Content showHours: boolean; showRate: boolean; showProjectBreakdown: boolean; // Footer footerText?: string; paymentInstructions?: string; // Structured payment methods (rendered by all templates). Coexists with the // free-text paymentInstructions above, which stays for back-compat. paymentMethods?: PaymentMethod[]; } // Invoice templates (user-customizable) export const invoiceTemplates = sqliteTable( "invoice_templates", { id: text("id") .primaryKey() .$defaultFn(() => createId()), userId: text("user_id").notNull(), organizationId: text("organization_id").references(() => organization.id, { onDelete: "cascade", }), name: text("name").notNull(), isDefault: integer("is_default", { mode: "boolean" }).default(false), config: text("config", { mode: "json" }).$type(), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("invoice_templates_user_idx").on(table.userId), index("invoice_templates_org_idx").on(table.organizationId), ], ); // Invoices export const invoices = sqliteTable( "invoices", { id: text("id") .primaryKey() .$defaultFn(() => createId()), userId: text("user_id").notNull(), organizationId: text("organization_id").references(() => organization.id, { onDelete: "cascade", }), clientId: text("client_id").references(() => clients.id, { onDelete: "set null", }), templateId: text("template_id").references(() => invoiceTemplates.id, { onDelete: "set null", }), // Invoice details invoiceNumber: text("invoice_number").notNull(), status: text("status", { enum: ["draft", "sent", "viewed", "paid", "overdue", "cancelled"], }).default("draft"), // Dates issueDate: integer("issue_date", { mode: "timestamp" }).notNull(), dueDate: integer("due_date", { mode: "timestamp" }), paidDate: integer("paid_date", { mode: "timestamp" }), // Financial subtotal: integer("subtotal").notNull(), // Cents taxRate: integer("tax_rate").default(0), // Percentage * 100 (e.g., 2000 = 20%) taxAmount: integer("tax_amount").default(0), total: integer("total").notNull(), currency: text("currency").default("USD"), // Period covered periodStart: integer("period_start", { mode: "timestamp" }), periodEnd: integer("period_end", { mode: "timestamp" }), // Sharing (Pro feature) shareToken: text("share_token").unique(), shareUrl: text("share_url"), shareExpiresAt: integer("share_expires_at", { mode: "timestamp" }), viewedAt: integer("viewed_at", { mode: "timestamp" }), // Notes notes: text("notes"), paymentNotes: text("payment_notes"), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("invoices_user_idx").on(table.userId), index("invoices_org_idx").on(table.organizationId), index("invoices_client_idx").on(table.clientId), index("invoices_status_idx").on(table.status), index("invoices_share_token_idx").on(table.shareToken), // Guards invoice-number uniqueness within an org against concurrent // creates. NOTE: organizationId is nullable (legacy personal invoices); // SQLite treats NULLs as distinct, so for org-less rows uniqueness falls // back to the max+1 generation in /api/invoices. See OPL-2368. uniqueIndex("invoices_org_number_unique").on( table.organizationId, table.invoiceNumber, ), ], ); // Invoice line items export const invoiceLineItems = sqliteTable( "invoice_line_items", { id: text("id") .primaryKey() .$defaultFn(() => createId()), invoiceId: text("invoice_id") .notNull() .references(() => invoices.id, { onDelete: "cascade" }), // What's being billed projectId: text("project_id").references(() => projects.id, { onDelete: "set null", }), description: text("description").notNull(), // Quantity & pricing quantity: integer("quantity").notNull(), // Minutes or units quantityType: text("quantity_type", { enum: ["minutes", "hours", "units"], }).default("hours"), rate: integer("rate").notNull(), // Cents per unit amount: integer("amount").notNull(), // Total cents // Source tracking (for audit trail) eventIds: text("event_ids", { mode: "json" }).$type(), // Timeline event IDs sortOrder: integer("sort_order").default(0), }, (table) => [index("invoice_line_items_invoice_idx").on(table.invoiceId)], ); // Secret alerts (detected secrets audit trail) export const secretAlerts = sqliteTable( "secret_alerts", { id: text("id") .primaryKey() .$defaultFn(() => createId()), eventId: text("event_id").references(() => timelineEvents.id, { onDelete: "cascade", }), deviceId: text("device_id"), type: text("type", { enum: ["wif_key", "hex_key", "mnemonic", "api_key", "connection_string"], }).notNull(), field: text("field").notNull(), preview: text("preview").notNull(), redacted: integer("redacted", { mode: "boolean" }).notNull().default(true), acknowledged: integer("acknowledged", { mode: "boolean" }) .notNull() .default(false), detectedAt: integer("detected_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), acknowledgedAt: integer("acknowledged_at", { mode: "timestamp" }), }, (table) => [ index("secret_alerts_event_idx").on(table.eventId), index("secret_alerts_type_idx").on(table.type), index("secret_alerts_detected_idx").on(table.detectedAt), ], ); // App settings (key-value store for user preferences and API keys) export const appSettings = sqliteTable("app_settings", { key: text("key").primaryKey(), value: text("value"), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }); // ============================================================================= // ENVIRONMENT MANAGEMENT TABLES // Postman-inspired: initial_value is safe to sync, current_value stays local // ============================================================================= // Local environments (stored in desktop SQLite only) export const environments = sqliteTable("environments", { id: text("id") .primaryKey() .$defaultFn(() => crypto.randomUUID()), name: text("name").notNull(), syncToHub: integer("sync_to_hub", { mode: "boolean" }) .notNull() .default(false), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }); // Local environment variables (stored in desktop SQLite only) export const environmentVariables = sqliteTable( "environment_variables", { id: text("id") .primaryKey() .$defaultFn(() => crypto.randomUUID()), environmentId: text("environment_id") .notNull() .references(() => environments.id, { onDelete: "cascade" }), key: text("key").notNull(), initialValue: text("initial_value").notNull().default(""), // safe to sync currentValue: text("current_value").notNull().default(""), // local-only, NEVER synced secret: integer("secret", { mode: "boolean" }).notNull().default(false), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("env_vars_environment_idx").on(table.environmentId), index("env_vars_key_idx").on(table.key), ], ); // Hub org environments (initial_values only — current_values NEVER stored here) export const orgEnvironments = sqliteTable("org_environments", { id: text("id") .primaryKey() .$defaultFn(() => crypto.randomUUID()), organizationId: text("organization_id").notNull(), name: text("name").notNull(), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }); // Hub org environment variables (initial_values only — current_values NEVER stored) export const orgEnvironmentVariables = sqliteTable( "org_environment_variables", { id: text("id") .primaryKey() .$defaultFn(() => crypto.randomUUID()), environmentId: text("environment_id") .notNull() .references(() => orgEnvironments.id, { onDelete: "cascade" }), key: text("key").notNull(), initialValue: text("initial_value").notNull().default(""), secret: integer("secret", { mode: "boolean" }).notNull().default(false), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [index("org_env_vars_environment_idx").on(table.environmentId)], ); // Type exports for use in application code export type AppSetting = typeof appSettings.$inferSelect; export type NewAppSetting = typeof appSettings.$inferInsert; export type DeviceCode = typeof deviceCode.$inferSelect; export type NewDeviceCode = typeof deviceCode.$inferInsert; export type ApiKeyRecord = typeof apikey.$inferSelect; export type NewApiKeyRecord = typeof apikey.$inferInsert; export type LinkInvite = typeof linkInvites.$inferSelect; export type NewLinkInvite = typeof linkInvites.$inferInsert; export type WaitlistEntry = typeof waitlistEntries.$inferSelect; export type NewWaitlistEntry = typeof waitlistEntries.$inferInsert; export type Device = typeof devices.$inferSelect; export type NewDevice = typeof devices.$inferInsert; export type ScanRoot = typeof scanRoots.$inferSelect; export type NewScanRoot = typeof scanRoots.$inferInsert; export type Project = typeof projects.$inferSelect; export type NewProject = typeof projects.$inferInsert; export type ProjectRoot = typeof projectRoots.$inferSelect; export type NewProjectRoot = typeof projectRoots.$inferInsert; export type GitHubInstallation = typeof githubInstallations.$inferSelect; export type NewGitHubInstallation = typeof githubInstallations.$inferInsert; export type GitHubRepository = typeof githubRepositories.$inferSelect; export type NewGitHubRepository = typeof githubRepositories.$inferInsert; export type ProjectAssociation = typeof projectAssociations.$inferSelect; export type NewProjectAssociation = typeof projectAssociations.$inferInsert; export type TimelineEvent = typeof timelineEvents.$inferSelect; export type NewTimelineEvent = typeof timelineEvents.$inferInsert; export type ScanHistoryRecord = typeof scanHistory.$inferSelect; export type NewScanHistoryRecord = typeof scanHistory.$inferInsert; export type SyncHistoryRecord = typeof syncHistory.$inferSelect; export type NewSyncHistoryRecord = typeof syncHistory.$inferInsert; export type SubscriptionRecord = typeof subscription.$inferSelect; export type NewSubscriptionRecord = typeof subscription.$inferInsert; export type Client = typeof clients.$inferSelect; export type NewClient = typeof clients.$inferInsert; export type InvoiceTemplate = typeof invoiceTemplates.$inferSelect; export type NewInvoiceTemplate = typeof invoiceTemplates.$inferInsert; export type Invoice = typeof invoices.$inferSelect; export type NewInvoice = typeof invoices.$inferInsert; export type InvoiceLineItem = typeof invoiceLineItems.$inferSelect; export type NewInvoiceLineItem = typeof invoiceLineItems.$inferInsert; export type SecretAlert = typeof secretAlerts.$inferSelect; export type NewSecretAlert = typeof secretAlerts.$inferInsert; export type Environment = typeof environments.$inferSelect; export type NewEnvironment = typeof environments.$inferInsert; export type EnvironmentVariable = typeof environmentVariables.$inferSelect; export type NewEnvironmentVariable = typeof environmentVariables.$inferInsert; export type OrgEnvironment = typeof orgEnvironments.$inferSelect; export type NewOrgEnvironment = typeof orgEnvironments.$inferInsert; export type OrgEnvironmentVariable = typeof orgEnvironmentVariables.$inferSelect; export type NewOrgEnvironmentVariable = typeof orgEnvironmentVariables.$inferInsert; export type EvidenceThread = typeof evidenceThreads.$inferSelect; export type NewEvidenceThread = typeof evidenceThreads.$inferInsert; export type EvidenceMessage = typeof evidenceMessages.$inferSelect; export type NewEvidenceMessage = typeof evidenceMessages.$inferInsert; // ============================================================================= // TIMELINE ROLLUPS — Pre-computed aggregations for fast chart rendering (LOD) // ============================================================================= export const UNASSIGNED_PROJECT_ID = "__unassigned__"; export const timelineRollups = sqliteTable( "timeline_rollups", { id: text("id") .primaryKey() .$defaultFn(() => createId()), organizationId: text("organization_id").notNull(), granularity: text("granularity", { enum: ["hour", "day", "week", "month"], }).notNull(), bucketStart: integer("bucket_start").notNull(), bucketEnd: integer("bucket_end").notNull(), projectId: text("project_id").notNull().default(UNASSIGNED_PROJECT_ID), projectName: text("project_name").notNull().default("Unassigned"), projectColor: text("project_color") .notNull() .default("oklch(0.55 0.02 260)"), eventCount: integer("event_count").notNull().default(0), estimatedMinutes: integer("estimated_minutes").notNull().default(0), sourceCounts: text("source_counts", { mode: "json" }) .$type>() .notNull(), updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("rollups_org_gran_start_idx").on( table.organizationId, table.granularity, table.bucketStart, ), uniqueIndex("rollups_upsert_uidx").on( table.organizationId, table.granularity, table.bucketStart, table.projectId, ), index("rollups_project_idx").on(table.projectId), ], ); export type TimelineRollup = typeof timelineRollups.$inferSelect; export type NewTimelineRollup = typeof timelineRollups.$inferInsert; // ============================================================================= // OAUTH PROVIDER + JWT PLUGIN TABLES // Generated by `bunx @better-auth/cli generate` for the @better-auth/oauth-provider // + jwt() plugins (Scribe as an OAuth 2.1 / OIDC provider for AI agents + MCP). // Do not edit field shapes by hand — regenerate from the auth config if the // plugins change. timestamp columns use timestamp_ms to match Better Auth. // ============================================================================= export const jwks = sqliteTable("jwks", { id: text("id").primaryKey(), publicKey: text("public_key").notNull(), privateKey: text("private_key").notNull(), createdAt: integer("created_at", { mode: "timestamp_ms" }).notNull(), expiresAt: integer("expires_at", { mode: "timestamp_ms" }), }); export const oauthClient = sqliteTable( "oauth_client", { id: text("id").primaryKey(), clientId: text("client_id").notNull().unique(), clientSecret: text("client_secret"), disabled: integer("disabled", { mode: "boolean" }).default(false), skipConsent: integer("skip_consent", { mode: "boolean" }), enableEndSession: integer("enable_end_session", { mode: "boolean" }), subjectType: text("subject_type"), scopes: text("scopes", { mode: "json" }), userId: text("user_id").references(() => user.id, { onDelete: "cascade" }), createdAt: integer("created_at", { mode: "timestamp_ms" }), updatedAt: integer("updated_at", { mode: "timestamp_ms" }), name: text("name"), uri: text("uri"), icon: text("icon"), contacts: text("contacts", { mode: "json" }), tos: text("tos"), policy: text("policy"), softwareId: text("software_id"), softwareVersion: text("software_version"), softwareStatement: text("software_statement"), redirectUris: text("redirect_uris", { mode: "json" }).notNull(), postLogoutRedirectUris: text("post_logout_redirect_uris", { mode: "json" }), tokenEndpointAuthMethod: text("token_endpoint_auth_method"), grantTypes: text("grant_types", { mode: "json" }), responseTypes: text("response_types", { mode: "json" }), public: integer("public", { mode: "boolean" }), type: text("type"), requirePKCE: integer("require_pkce", { mode: "boolean" }), referenceId: text("reference_id"), metadata: text("metadata", { mode: "json" }), }, (table) => [index("oauthClient_userId_idx").on(table.userId)], ); export const oauthRefreshToken = sqliteTable( "oauth_refresh_token", { id: text("id").primaryKey(), token: text("token").notNull().unique(), clientId: text("client_id") .notNull() .references(() => oauthClient.clientId, { onDelete: "cascade" }), sessionId: text("session_id").references(() => session.id, { onDelete: "set null", }), userId: text("user_id") .notNull() .references(() => user.id, { onDelete: "cascade" }), referenceId: text("reference_id"), expiresAt: integer("expires_at", { mode: "timestamp_ms" }), createdAt: integer("created_at", { mode: "timestamp_ms" }), revoked: integer("revoked", { mode: "timestamp_ms" }), authTime: integer("auth_time", { mode: "timestamp_ms" }), scopes: text("scopes", { mode: "json" }).notNull(), }, (table) => [ index("oauthRefreshToken_clientId_idx").on(table.clientId), index("oauthRefreshToken_sessionId_idx").on(table.sessionId), index("oauthRefreshToken_userId_idx").on(table.userId), ], ); export const oauthAccessToken = sqliteTable( "oauth_access_token", { id: text("id").primaryKey(), token: text("token").unique(), clientId: text("client_id") .notNull() .references(() => oauthClient.clientId, { onDelete: "cascade" }), sessionId: text("session_id").references(() => session.id, { onDelete: "set null", }), userId: text("user_id").references(() => user.id, { onDelete: "cascade" }), referenceId: text("reference_id"), refreshId: text("refresh_id").references(() => oauthRefreshToken.id, { onDelete: "cascade", }), expiresAt: integer("expires_at", { mode: "timestamp_ms" }), createdAt: integer("created_at", { mode: "timestamp_ms" }), scopes: text("scopes", { mode: "json" }).notNull(), }, (table) => [ index("oauthAccessToken_clientId_idx").on(table.clientId), index("oauthAccessToken_sessionId_idx").on(table.sessionId), index("oauthAccessToken_userId_idx").on(table.userId), index("oauthAccessToken_refreshId_idx").on(table.refreshId), ], ); export const oauthConsent = sqliteTable( "oauth_consent", { id: text("id").primaryKey(), clientId: text("client_id") .notNull() .references(() => oauthClient.clientId, { onDelete: "cascade" }), userId: text("user_id").references(() => user.id, { onDelete: "cascade" }), referenceId: text("reference_id"), scopes: text("scopes", { mode: "json" }).notNull(), createdAt: integer("created_at", { mode: "timestamp_ms" }), updatedAt: integer("updated_at", { mode: "timestamp_ms" }), }, (table) => [ index("oauthConsent_clientId_idx").on(table.clientId), index("oauthConsent_userId_idx").on(table.userId), ], ); // ============================================================================= // LIFECYCLE EMAIL (hub-only) — OPL-2643 // Foundation for the drip/lifecycle email program (parent OPL-2642). These // tables live in the shared schema but are HUB-ONLY: only apps/web (Turso) // runs the email crons and references them. Desktop migrations intentionally // do not include them (same boundary as `subscription`, `jwks`, oauth_*). // ============================================================================= /** * Idempotency ledger for lifecycle email. One row per (user, emailType, * periodKey) that has been sent, written with INSERT OR IGNORE before each * send so a cron retry (or overlapping invocation) can never double-send. * * `periodKey` is the send-window identifier: ISO week `2026-W26` for weekly * digests, date `2026-06-28` for daily nudges/onboarding. The unique index is * the dedup key. */ export const emailLog = sqliteTable( "email_log", { id: text("id") .primaryKey() .$defaultFn(() => createId()), userId: text("user_id") .notNull() .references(() => user.id, { onDelete: "cascade" }), emailType: text("email_type").notNull(), periodKey: text("period_key").notNull(), sentAt: integer("sent_at", { mode: "timestamp_ms" }) .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`) .notNull(), }, (table) => [ uniqueIndex("email_log_dedup_idx").on( table.userId, table.emailType, table.periodKey, ), index("email_log_user_idx").on(table.userId), ], ); /** * Per-user lifecycle email preferences. A dedicated table (rather than a JSON * column on the shared `user` auth row) keeps the opt-in/out state queryable * and the hub-only concern out of the better-auth contract. Booleans default * opted-in; the one-click unsubscribe (P3, OPL-2646) flips them. `digestDayOfWeek` * is 0–6 (0 = Sunday), default 1 (Monday) to match the weekly digest cron. */ export const emailPreferences = sqliteTable("email_preferences", { userId: text("user_id") .primaryKey() .references(() => user.id, { onDelete: "cascade" }), weeklyDigest: integer("weekly_digest", { mode: "boolean" }) .default(true) .notNull(), inactivityNudges: integer("inactivity_nudges", { mode: "boolean" }) .default(true) .notNull(), productUpdates: integer("product_updates", { mode: "boolean" }) .default(true) .notNull(), onboardingEmails: integer("onboarding_emails", { mode: "boolean" }) .default(true) .notNull(), digestDayOfWeek: integer("digest_day_of_week").default(1).notNull(), updatedAt: integer("updated_at", { mode: "timestamp_ms" }) .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`) .$onUpdate(() => new Date()) .notNull(), }); /** * Generated activity-report documents (hub library). * * Reports are point-in-time documents: criteria + full JSON payload are stored * when generation completes. The `/reports` UI lists these; open a row for the * document view / PDF. Not a live query cache. */ export const generatedReports = sqliteTable( "generated_reports", { id: text("id") .primaryKey() .$defaultFn(() => createId()), organizationId: text("organization_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), createdByUserId: text("created_by_user_id") .notNull() .references(() => user.id, { onDelete: "cascade" }), title: text("title").notNull(), status: text("status", { enum: ["pending", "ready", "failed"], }) .notNull() .default("pending"), error: text("error"), /** Immutable criteria snapshot (JSON): days/start/end, scope, filter, depth, projectIds, etc. */ criteria: text("criteria").notNull(), rangeStart: integer("range_start", { mode: "timestamp" }), rangeEnd: integer("range_end", { mode: "timestamp" }), scope: text("scope", { enum: ["me", "team"] }).notNull(), filter: text("filter", { enum: ["client", "internal", "both"], }).notNull(), depth: text("depth", { enum: ["summary", "exhaustive"], }).notNull(), /** Card summary fields (denormalized for the library list). */ totalEvents: integer("total_events").default(0), totalEstimatedMinutes: integer("total_estimated_minutes").default(0), activeDays: integer("active_days").default(0), /** Full generateReport payload as JSON text when status=ready. */ payload: text("payload"), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), completedAt: integer("completed_at", { mode: "timestamp" }), }, (table) => [ index("generated_reports_org_created_idx").on( table.organizationId, table.createdAt, ), index("generated_reports_org_user_idx").on( table.organizationId, table.createdByUserId, ), index("generated_reports_status_idx").on(table.status), ], ); /** * Persistent AI-chat conversations (OPL-3076, Option A — AI SDK native). * * One row per conversation, owned by a single user. On the hub, * `organizationId` scopes the conversation to the workspace it was held in * (chat context is workspace-scoped, so history is too); on the desktop's * local single-tenant DB it is null. Per-user history: conversations are * listed and readable only by their creator, never org-wide. */ export const chatConversations = sqliteTable( "chat_conversations", { id: text("id") .primaryKey() .$defaultFn(() => createId()), userId: text("user_id").notNull(), organizationId: text("organization_id"), /** Derived from the first user message; renameable. */ title: text("title"), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), /** Bumped on every completed exchange — sidebar sorts by this. */ updatedAt: integer("updated_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("chat_conversations_owner_idx").on( table.userId, table.organizationId, table.updatedAt, ), ], ); /** * Messages of a persistent chat conversation. Each row stores one AI SDK * UIMessage verbatim as JSON (`message`) — parts, tool calls, and all — so a * conversation reloads into `useChat` losslessly. `position` is the message's * index in the conversation; the save path rewrites the whole conversation on * stream end, so positions are always dense and ordered. */ export const chatMessages = sqliteTable( "chat_messages", { id: text("id") .primaryKey() .$defaultFn(() => createId()), conversationId: text("conversation_id") .notNull() .references(() => chatConversations.id, { onDelete: "cascade" }), position: integer("position").notNull(), message: text("message", { mode: "json" }).notNull(), createdAt: integer("created_at", { mode: "timestamp" }).default( sql`(unixepoch())`, ), }, (table) => [ index("chat_messages_conversation_idx").on( table.conversationId, table.position, ), ], );