mirror of
https://github.com/nicotsx/zerobyte.git
synced 2026-06-03 21:59:36 -04:00
577 lines
22 KiB
TypeScript
577 lines
22 KiB
TypeScript
import { sql } from "drizzle-orm";
|
|
import { index, int, integer, sqliteTable, text, real, primaryKey, unique, uniqueIndex } from "drizzle-orm/sqlite-core";
|
|
import type {
|
|
CompressionMode,
|
|
RepositoryBackend,
|
|
RepositoryConfig,
|
|
RepositoryStatus,
|
|
BandwidthUnit,
|
|
DoctorResult,
|
|
ResticStatsDto,
|
|
} from "@zerobyte/core/restic";
|
|
import type { BackupWebhooks } from "@zerobyte/core/backup-hooks";
|
|
import type { BackendConfig, BackendStatus, BackendType } from "@zerobyte/contracts/volumes";
|
|
import type { NotificationConfig, NotificationType } from "~/schemas/notifications";
|
|
import type { ShortId } from "~/server/utils/branded";
|
|
import { LOCAL_AGENT_ID } from "../modules/agents/constants";
|
|
|
|
/**
|
|
* Users Table
|
|
*/
|
|
export const usersTable = sqliteTable("users_table", {
|
|
id: text("id").primaryKey(),
|
|
username: text().notNull().unique(),
|
|
passwordHash: text("password_hash"),
|
|
hasDownloadedResticPassword: int("has_downloaded_restic_password", { mode: "boolean" }).notNull().default(false),
|
|
dateFormat: text("date_format").notNull().default("MM/DD/YYYY"),
|
|
timeFormat: text("time_format").notNull().default("12h"),
|
|
createdAt: int("created_at", { mode: "timestamp_ms" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: int("updated_at", { mode: "timestamp_ms" })
|
|
.notNull()
|
|
.$onUpdate(() => new Date())
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
name: text("name").notNull(),
|
|
email: text("email").notNull().unique(),
|
|
emailVerified: integer("email_verified", { mode: "boolean" }).default(false).notNull(),
|
|
image: text("image"),
|
|
displayUsername: text("display_username"),
|
|
twoFactorEnabled: integer("two_factor_enabled", { mode: "boolean" }).notNull().default(false),
|
|
role: text("role").notNull().default("user"),
|
|
banned: integer("banned", { mode: "boolean" }).notNull().default(false),
|
|
banReason: text("ban_reason"),
|
|
banExpires: integer("ban_expires", { mode: "timestamp_ms" }),
|
|
});
|
|
|
|
export type User = typeof usersTable.$inferSelect;
|
|
export const sessionsTable = sqliteTable(
|
|
"sessions_table",
|
|
{
|
|
id: text().primaryKey(),
|
|
userId: text("user_id")
|
|
.notNull()
|
|
.references(() => usersTable.id, { onDelete: "cascade" }),
|
|
token: text("token").notNull().unique(),
|
|
expiresAt: int("expires_at", { mode: "timestamp_ms" }).notNull(),
|
|
createdAt: int("created_at", { mode: "timestamp_ms" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
|
|
.notNull()
|
|
.$onUpdate(() => new Date())
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
ipAddress: text("ip_address"),
|
|
userAgent: text("user_agent"),
|
|
impersonatedBy: text("impersonated_by"),
|
|
activeOrganizationId: text("active_organization_id"),
|
|
},
|
|
(table) => [index("sessionsTable_userId_idx").on(table.userId)],
|
|
);
|
|
export type Session = typeof sessionsTable.$inferSelect;
|
|
|
|
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(() => usersTable.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" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
|
|
.$onUpdate(() => new Date())
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
},
|
|
(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" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
|
|
.$onUpdate(() => new Date())
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
},
|
|
(table) => [index("verification_identifier_idx").on(table.identifier)],
|
|
);
|
|
|
|
export type OrganizationMetadata = {
|
|
resticPassword: string;
|
|
};
|
|
|
|
export const organization = sqliteTable(
|
|
"organization",
|
|
{
|
|
id: text("id").primaryKey(),
|
|
name: text("name").notNull(),
|
|
slug: text("slug").notNull().unique(),
|
|
logo: text("logo"),
|
|
createdAt: integer("created_at", { mode: "timestamp_ms" }).notNull(),
|
|
metadata: text("metadata", { mode: "json" }).$type<OrganizationMetadata>(),
|
|
},
|
|
(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(() => usersTable.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),
|
|
uniqueIndex("member_org_user_uidx").on(table.organizationId, table.userId),
|
|
],
|
|
);
|
|
|
|
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" }).notNull(),
|
|
inviterId: text("inviter_id")
|
|
.notNull()
|
|
.references(() => usersTable.id, { onDelete: "cascade" }),
|
|
},
|
|
(table) => [
|
|
index("invitation_organizationId_idx").on(table.organizationId),
|
|
index("invitation_email_idx").on(table.email),
|
|
],
|
|
);
|
|
|
|
export const ssoProvider = sqliteTable("sso_provider", {
|
|
id: text("id").primaryKey(),
|
|
providerId: text("provider_id").notNull().unique(),
|
|
organizationId: text("organization_id")
|
|
.notNull()
|
|
.references(() => organization.id, { onDelete: "cascade" }),
|
|
userId: text("user_id").references(() => usersTable.id, { onDelete: "set null" }),
|
|
issuer: text("issuer").notNull(),
|
|
domain: text("domain").notNull(),
|
|
autoLinkMatchingEmails: int("auto_link_matching_emails", { mode: "boolean" }).notNull().default(false),
|
|
oidcConfig: text("oidc_config", { mode: "json" }).$type<Record<string, unknown> | null>(),
|
|
samlConfig: text("saml_config", { mode: "json" }).$type<Record<string, unknown> | null>(),
|
|
createdAt: integer("created_at", { mode: "timestamp_ms" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
|
|
.notNull()
|
|
.$onUpdate(() => new Date())
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
});
|
|
|
|
export type AgentKind = "local" | "remote";
|
|
export type AgentStatus = "offline" | "connecting" | "online" | "degraded";
|
|
export type AgentCapabilities = Record<string, unknown>;
|
|
|
|
export const agentsTable = sqliteTable(
|
|
"agents_table",
|
|
{
|
|
id: text("id").primaryKey(),
|
|
organizationId: text("organization_id").references(() => organization.id, { onDelete: "cascade" }),
|
|
name: text("name").notNull(),
|
|
kind: text("kind").$type<AgentKind>().notNull(),
|
|
status: text("status").$type<AgentStatus>().notNull().default("offline"),
|
|
capabilities: text("capabilities", { mode: "json" }).$type<AgentCapabilities>().notNull().default({}),
|
|
lastSeenAt: int("last_seen_at", { mode: "number" }),
|
|
lastReadyAt: int("last_ready_at", { mode: "number" }),
|
|
createdAt: int("created_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: int("updated_at", { mode: "number" })
|
|
.notNull()
|
|
.$onUpdate(() => Date.now())
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
},
|
|
(table) => [
|
|
index("agents_table_organization_id_idx").on(table.organizationId),
|
|
index("agents_table_status_idx").on(table.status),
|
|
],
|
|
);
|
|
export type Agent = typeof agentsTable.$inferSelect;
|
|
|
|
/**
|
|
* Volumes Table
|
|
*/
|
|
export const volumesTable = sqliteTable(
|
|
"volumes_table",
|
|
{
|
|
id: int().primaryKey({ autoIncrement: true }),
|
|
shortId: text("short_id").$type<ShortId>().notNull().unique(),
|
|
provisioningId: text("provisioning_id"),
|
|
name: text().notNull(),
|
|
type: text().$type<BackendType>().notNull(),
|
|
status: text().$type<BackendStatus>().notNull().default("unmounted"),
|
|
lastError: text("last_error"),
|
|
lastHealthCheck: integer("last_health_check", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
createdAt: integer("created_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: integer("updated_at", { mode: "number" })
|
|
.notNull()
|
|
.$onUpdate(() => Date.now())
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
config: text("config", { mode: "json" }).$type<BackendConfig>().notNull(),
|
|
autoRemount: int("auto_remount", { mode: "boolean" }).notNull().default(true),
|
|
agentId: text("agent_id").notNull().default(LOCAL_AGENT_ID),
|
|
organizationId: text("organization_id")
|
|
.notNull()
|
|
.references(() => organization.id, { onDelete: "cascade" }),
|
|
},
|
|
(table) => [
|
|
unique().on(table.name, table.organizationId),
|
|
index("volumes_table_agent_id_idx").on(table.agentId),
|
|
uniqueIndex("volumes_table_org_provisioning_id_uidx").on(table.organizationId, table.provisioningId),
|
|
],
|
|
);
|
|
export type Volume = typeof volumesTable.$inferSelect;
|
|
export type VolumeInsert = typeof volumesTable.$inferInsert;
|
|
|
|
/**
|
|
* Repositories Table
|
|
*/
|
|
export const repositoriesTable = sqliteTable(
|
|
"repositories_table",
|
|
{
|
|
id: text().primaryKey(),
|
|
shortId: text("short_id").$type<ShortId>().notNull().unique(),
|
|
provisioningId: text("provisioning_id"),
|
|
name: text().notNull(),
|
|
type: text().$type<RepositoryBackend>().notNull(),
|
|
config: text("config", { mode: "json" }).$type<RepositoryConfig>().notNull(),
|
|
compressionMode: text("compression_mode").$type<CompressionMode>().default("auto"),
|
|
status: text().$type<RepositoryStatus>().default("unknown"),
|
|
lastChecked: int("last_checked", { mode: "number" }),
|
|
lastError: text("last_error"),
|
|
doctorResult: text("doctor_result", { mode: "json" }).$type<DoctorResult>(),
|
|
stats: text("stats", { mode: "json" }).$type<ResticStatsDto | null>(),
|
|
statsUpdatedAt: int("stats_updated_at", { mode: "number" }),
|
|
uploadLimitEnabled: int("upload_limit_enabled", { mode: "boolean" }).notNull().default(false),
|
|
uploadLimitValue: real("upload_limit_value").notNull().default(1),
|
|
uploadLimitUnit: text("upload_limit_unit").$type<BandwidthUnit>().notNull().default("Mbps"),
|
|
downloadLimitEnabled: int("download_limit_enabled", { mode: "boolean" }).notNull().default(false),
|
|
downloadLimitValue: real("download_limit_value").notNull().default(1),
|
|
downloadLimitUnit: text("download_limit_unit").$type<BandwidthUnit>().notNull().default("Mbps"),
|
|
createdAt: int("created_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: int("updated_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
organizationId: text("organization_id")
|
|
.notNull()
|
|
.references(() => organization.id, { onDelete: "cascade" }),
|
|
},
|
|
(table) => [
|
|
uniqueIndex("repositories_table_org_provisioning_id_uidx").on(table.organizationId, table.provisioningId),
|
|
],
|
|
);
|
|
export type Repository = typeof repositoriesTable.$inferSelect;
|
|
export type RepositoryInsert = typeof repositoriesTable.$inferInsert;
|
|
|
|
export type RepositoryLockType = "shared" | "exclusive";
|
|
|
|
export const repositoryLocksTable = sqliteTable(
|
|
"repository_locks",
|
|
{
|
|
id: text("id").primaryKey(),
|
|
repositoryId: text("repository_id").notNull(),
|
|
type: text("type").$type<RepositoryLockType>().notNull(),
|
|
operation: text("operation").notNull(),
|
|
ownerId: text("owner_id").notNull(),
|
|
acquiredAt: int("acquired_at", { mode: "number" }).notNull(),
|
|
expiresAt: int("expires_at", { mode: "number" }).notNull(),
|
|
heartbeatAt: int("heartbeat_at", { mode: "number" }).notNull(),
|
|
},
|
|
(table) => [
|
|
index("repository_locks_repository_id_idx").on(table.repositoryId),
|
|
index("repository_locks_expires_at_idx").on(table.expiresAt),
|
|
index("repository_locks_owner_id_idx").on(table.ownerId),
|
|
],
|
|
);
|
|
export type RepositoryLock = typeof repositoryLocksTable.$inferSelect;
|
|
|
|
export const repositoryLockWaitersTable = sqliteTable(
|
|
"repository_lock_waiters",
|
|
{
|
|
id: text("id").primaryKey(),
|
|
repositoryId: text("repository_id").notNull(),
|
|
type: text("type").$type<RepositoryLockType>().notNull(),
|
|
operation: text("operation").notNull(),
|
|
ownerId: text("owner_id").notNull(),
|
|
requestedAt: int("requested_at", { mode: "number" }).notNull(),
|
|
expiresAt: int("expires_at", { mode: "number" }).notNull(),
|
|
heartbeatAt: int("heartbeat_at", { mode: "number" }).notNull(),
|
|
},
|
|
(table) => [
|
|
index("repository_lock_waiters_repository_id_idx").on(table.repositoryId),
|
|
index("repository_lock_waiters_expires_at_idx").on(table.expiresAt),
|
|
index("repository_lock_waiters_owner_id_idx").on(table.ownerId),
|
|
],
|
|
);
|
|
export type RepositoryLockWaiter = typeof repositoryLockWaitersTable.$inferSelect;
|
|
|
|
type TaskJson = Record<string, unknown>;
|
|
|
|
export const tasksTable = sqliteTable(
|
|
"tasks",
|
|
{
|
|
id: text("id").primaryKey(),
|
|
organizationId: text("organization_id")
|
|
.notNull()
|
|
.references(() => organization.id, { onDelete: "cascade" }),
|
|
kind: text("kind").notNull(),
|
|
status: text("status").notNull(),
|
|
resourceType: text("resource_type").notNull(),
|
|
resourceId: text("resource_id").notNull(),
|
|
targetAgentId: text("target_agent_id"),
|
|
input: text("input", { mode: "json" }).$type<TaskJson>().notNull(),
|
|
progress: text("progress", { mode: "json" }).$type<TaskJson | null>(),
|
|
result: text("result", { mode: "json" }).$type<TaskJson | null>(),
|
|
error: text("error"),
|
|
cancellationRequested: int("cancellation_requested", { mode: "boolean" }).notNull().default(false),
|
|
createdAt: int("created_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
startedAt: int("started_at", { mode: "number" }),
|
|
updatedAt: int("updated_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
finishedAt: int("finished_at", { mode: "number" }),
|
|
},
|
|
(table) => [
|
|
index("tasks_org_kind_resource_status_idx").on(
|
|
table.organizationId,
|
|
table.kind,
|
|
table.resourceType,
|
|
table.resourceId,
|
|
table.status,
|
|
),
|
|
index("tasks_org_status_updated_at_idx").on(table.organizationId, table.status, table.updatedAt),
|
|
],
|
|
);
|
|
export type Task = typeof tasksTable.$inferSelect;
|
|
export type TaskInsert = typeof tasksTable.$inferInsert;
|
|
|
|
/**
|
|
* Backup Schedules Table
|
|
*/
|
|
export const backupSchedulesTable = sqliteTable("backup_schedules_table", {
|
|
id: int().primaryKey({ autoIncrement: true }),
|
|
shortId: text("short_id").$type<ShortId>().notNull().unique(),
|
|
name: text().notNull(),
|
|
volumeId: int("volume_id")
|
|
.notNull()
|
|
.references(() => volumesTable.id, { onDelete: "cascade" }),
|
|
repositoryId: text("repository_id")
|
|
.notNull()
|
|
.references(() => repositoriesTable.id, { onDelete: "cascade" }),
|
|
enabled: int("enabled", { mode: "boolean" }).notNull().default(true),
|
|
cronExpression: text("cron_expression").notNull(),
|
|
retentionPolicy: text("retention_policy", { mode: "json" }).$type<{
|
|
keepLast?: number;
|
|
keepHourly?: number;
|
|
keepDaily?: number;
|
|
keepWeekly?: number;
|
|
keepMonthly?: number;
|
|
keepYearly?: number;
|
|
keepWithinDuration?: string;
|
|
}>(),
|
|
excludePatterns: text("exclude_patterns", { mode: "json" }).$type<string[]>().default([]),
|
|
excludeIfPresent: text("exclude_if_present", { mode: "json" }).$type<string[]>().default([]),
|
|
includePaths: text("include_paths", { mode: "json" }).$type<string[]>().default([]),
|
|
includePatterns: text("include_patterns", { mode: "json" }).$type<string[]>().default([]),
|
|
lastBackupAt: int("last_backup_at", { mode: "number" }),
|
|
lastBackupStatus: text("last_backup_status").$type<"success" | "error" | "in_progress" | "warning" | null>(),
|
|
lastBackupError: text("last_backup_error"),
|
|
nextBackupAt: int("next_backup_at", { mode: "number" }),
|
|
oneFileSystem: int("one_file_system", { mode: "boolean" }).notNull().default(false),
|
|
customResticParams: text("custom_restic_params", { mode: "json" }).$type<string[]>().default([]),
|
|
backupWebhooks: text("backup_webhooks", { mode: "json" }).$type<BackupWebhooks | null>(),
|
|
sortOrder: int("sort_order", { mode: "number" }).notNull().default(0),
|
|
failureRetryCount: int("failure_retry_count").notNull().default(0),
|
|
maxRetries: int("max_retries").notNull().default(2),
|
|
retryDelay: int("retry_delay").notNull().default(900000),
|
|
createdAt: int("created_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: int("updated_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
organizationId: text("organization_id")
|
|
.notNull()
|
|
.references(() => organization.id, { onDelete: "cascade" }),
|
|
});
|
|
export type BackupScheduleInsert = typeof backupSchedulesTable.$inferInsert;
|
|
|
|
export type BackupSchedule = typeof backupSchedulesTable.$inferSelect;
|
|
|
|
/**
|
|
* Notification Destinations Table
|
|
*/
|
|
export const notificationDestinationsTable = sqliteTable("notification_destinations_table", {
|
|
id: int().primaryKey({ autoIncrement: true }),
|
|
name: text().notNull(),
|
|
enabled: int("enabled", { mode: "boolean" }).notNull().default(true),
|
|
status: text().$type<"healthy" | "error" | "unknown">().notNull().default("unknown"),
|
|
lastChecked: int("last_checked", { mode: "number" }),
|
|
lastError: text("last_error"),
|
|
type: text().$type<NotificationType>().notNull(),
|
|
config: text("config", { mode: "json" }).$type<NotificationConfig>().notNull(),
|
|
createdAt: int("created_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: int("updated_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
organizationId: text("organization_id")
|
|
.notNull()
|
|
.references(() => organization.id, { onDelete: "cascade" }),
|
|
});
|
|
export type NotificationDestination = typeof notificationDestinationsTable.$inferSelect;
|
|
|
|
/**
|
|
* Backup Schedule Notifications Junction Table (Many-to-Many)
|
|
*/
|
|
export const backupScheduleNotificationsTable = sqliteTable(
|
|
"backup_schedule_notifications_table",
|
|
{
|
|
scheduleId: int("schedule_id")
|
|
.notNull()
|
|
.references(() => backupSchedulesTable.id, { onDelete: "cascade" }),
|
|
destinationId: int("destination_id")
|
|
.notNull()
|
|
.references(() => notificationDestinationsTable.id, { onDelete: "cascade" }),
|
|
notifyOnStart: int("notify_on_start", { mode: "boolean" }).notNull().default(false),
|
|
notifyOnSuccess: int("notify_on_success", { mode: "boolean" }).notNull().default(false),
|
|
notifyOnWarning: int("notify_on_warning", { mode: "boolean" }).notNull().default(true),
|
|
notifyOnFailure: int("notify_on_failure", { mode: "boolean" }).notNull().default(true),
|
|
createdAt: int("created_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
},
|
|
(table) => [primaryKey({ columns: [table.scheduleId, table.destinationId] })],
|
|
);
|
|
export type BackupScheduleNotification = typeof backupScheduleNotificationsTable.$inferSelect;
|
|
|
|
/**
|
|
* Backup Schedule Mirrors Junction Table (Many-to-Many)
|
|
* Allows copying snapshots to secondary repositories after backup completes
|
|
*/
|
|
export const backupScheduleMirrorsTable = sqliteTable(
|
|
"backup_schedule_mirrors_table",
|
|
{
|
|
id: int().primaryKey({ autoIncrement: true }),
|
|
scheduleId: int("schedule_id")
|
|
.notNull()
|
|
.references(() => backupSchedulesTable.id, { onDelete: "cascade" }),
|
|
repositoryId: text("repository_id")
|
|
.notNull()
|
|
.references(() => repositoriesTable.id, { onDelete: "cascade" }),
|
|
enabled: int("enabled", { mode: "boolean" }).notNull().default(true),
|
|
lastCopyAt: int("last_copy_at", { mode: "number" }),
|
|
lastCopyStatus: text("last_copy_status").$type<"success" | "error" | "in_progress">(),
|
|
lastCopyError: text("last_copy_error"),
|
|
createdAt: int("created_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
},
|
|
(table) => [unique().on(table.scheduleId, table.repositoryId)],
|
|
);
|
|
|
|
export type BackupScheduleMirror = typeof backupScheduleMirrorsTable.$inferSelect;
|
|
|
|
/**
|
|
* App Metadata Table
|
|
* Used for storing key-value pairs like migration checkpoints
|
|
*/
|
|
export const appMetadataTable = sqliteTable("app_metadata", {
|
|
key: text().primaryKey(),
|
|
value: text().notNull(),
|
|
createdAt: int("created_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
updatedAt: int("updated_at", { mode: "number" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
});
|
|
export type AppMetadata = typeof appMetadataTable.$inferSelect;
|
|
|
|
export const twoFactor = sqliteTable(
|
|
"two_factor",
|
|
{
|
|
id: text("id").primaryKey(),
|
|
secret: text("secret").notNull(),
|
|
backupCodes: text("backup_codes").notNull(),
|
|
userId: text("user_id")
|
|
.notNull()
|
|
.references(() => usersTable.id, { onDelete: "cascade" }),
|
|
verified: integer("verified", { mode: "boolean" }).notNull().default(true),
|
|
},
|
|
(table) => [index("twoFactor_secret_idx").on(table.secret), index("twoFactor_userId_idx").on(table.userId)],
|
|
);
|
|
|
|
export const passkey = sqliteTable(
|
|
"passkey",
|
|
{
|
|
id: text("id").primaryKey(),
|
|
name: text("name"),
|
|
publicKey: text("public_key").notNull(),
|
|
userId: text("user_id")
|
|
.notNull()
|
|
.references(() => usersTable.id, { onDelete: "cascade" }),
|
|
credentialID: text("credential_id").notNull(),
|
|
counter: integer("counter").notNull(),
|
|
deviceType: text("device_type").notNull(),
|
|
backedUp: integer("backed_up", { mode: "boolean" }).notNull(),
|
|
transports: text("transports"),
|
|
createdAt: int("created_at", { mode: "timestamp_ms" })
|
|
.notNull()
|
|
.default(sql`(unixepoch() * 1000)`),
|
|
aaguid: text("aaguid"),
|
|
},
|
|
(table) => [index("passkey_userId_idx").on(table.userId), index("passkey_credentialID_idx").on(table.credentialID)],
|
|
);
|
|
export type Passkey = typeof passkey.$inferSelect;
|