homeio

1import {
2  boolean,
3  index,
4  integer,
5  jsonb,
6  pgTable,
7  text,
8  timestamp,
9  uniqueIndex,
10} from "drizzle-orm/pg-core";
11
12export const apps = pgTable(
13  "apps",
14  {
15    id: text("id").primaryKey(),
16    name: text("name").notNull(),
17    status: text("status").notNull().default("unknown"),
18    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
19  },
20  (table) => [index("apps_name_idx").on(table.name)],
21);
22
23export const users = pgTable(
24  "users",
25  {
26    id: text("id").primaryKey(),
27    username: text("username").notNull().unique(),
28    passwordHash: text("password_hash").notNull(),
29    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
30    totpSecret: text("totp_secret"),
31    totpEnabled: boolean("totp_enabled").notNull().default(false),
32    totpBackupCodes: text("totp_backup_codes"),
33    totpEnrolledAt: timestamp("totp_enrolled_at", { withTimezone: true }),
34  },
35  (table) => [index("users_username_idx").on(table.username)],
36);
37
38export const sessions = pgTable(
39  "sessions",
40  {
41    id: text("id").primaryKey(),
42    userId: text("user_id")
43      .notNull()
44      .references(() => users.id, { onDelete: "cascade" }),
45    expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
46    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
47  },
48  (table) => [
49    index("sessions_user_id_idx").on(table.userId),
50    index("sessions_expires_at_idx").on(table.expiresAt),
51  ],
52);
53
54export const appStacks = pgTable(
55  "app_stacks",
56  {
57    appId: text("app_id").primaryKey(),
58    templateName: text("template_name").notNull(),
59    stackName: text("stack_name").notNull(),
60    composePath: text("compose_path").notNull(),
61    status: text("status").notNull().default("not_installed"),
62    webUiPort: integer("web_ui_port"),
63    webUiUrl: text("web_ui_url"),
64    /** Subdomain this app is published under when the tunnel is enabled. */
65    tunnelSubdomain: text("tunnel_subdomain"),
66    envJson: jsonb("env_json").notNull().default({}),
67    displayName: text("display_name"),
68    iconUrl: text("icon_url"),
69    installedAt: timestamp("installed_at", { withTimezone: true }),
70    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
71    isUpToDate: boolean("is_up_to_date").default(true),
72    lastUpdateCheck: timestamp("last_update_check", { withTimezone: true }),
73    localDigest: text("local_digest"),
74    remoteDigest: text("remote_digest"),
75  },
76  (table) => [
77    index("app_stacks_status_idx").on(table.status),
78    index("app_stacks_web_ui_port_idx").on(table.webUiPort),
79  ],
80);
81
82export const appOperations = pgTable(
83  "app_operations",
84  {
85    id: text("id").primaryKey(),
86    appId: text("app_id").notNull(),
87    action: text("action").notNull(),
88    status: text("status").notNull(),
89    progressPercent: integer("progress_percent").notNull().default(0),
90    currentStep: text("current_step").notNull().default("queued"),
91    errorMessage: text("error_message"),
92    startedAt: timestamp("started_at", { withTimezone: true }),
93    finishedAt: timestamp("finished_at", { withTimezone: true }),
94    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
95  },
96  (table) => [
97    index("app_operations_app_id_idx").on(table.appId),
98    index("app_operations_status_idx").on(table.status),
99    index("app_operations_updated_at_idx").on(table.updatedAt.desc()),
100  ],
101);
102
103export const customStoreApps = pgTable(
104  "custom_store_apps",
105  {
106    appId: text("app_id").primaryKey(),
107    name: text("name").notNull(),
108    iconUrl: text("icon_url"),
109    webUiUrl: text("web_ui_url"),
110    sourceType: text("source_type").notNull(),
111    sourceText: text("source_text").notNull(),
112    composeContent: text("compose_content").notNull(),
113    repositoryUrl: text("repository_url"),
114    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
115    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
116  },
117  (table) => [index("custom_store_apps_updated_at_idx").on(table.updatedAt.desc())],
118);
119
120/**
121 * Name, icon and link an operator set for a container Homeio did not deploy.
122 * Keyed by container name: the id changes every time the container is recreated.
123 */
124export const containerCustomizations = pgTable("container_customizations", {
125  containerName: text("container_name").primaryKey(),
126  displayName: text("display_name"),
127  iconUrl: text("icon_url"),
128  dashboardUrl: text("dashboard_url"),
129  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
130});
131
132export const filesNetworkShares = pgTable(
133  "files_network_shares",
134  {
135    id: text("id").primaryKey(),
136    host: text("host").notNull(),
137    share: text("share").notNull(),
138    username: text("username").notNull(),
139    passwordCiphertext: text("password_ciphertext").notNull(),
140    passwordIv: text("password_iv").notNull(),
141    passwordTag: text("password_tag").notNull(),
142    mountPath: text("mount_path").notNull(),
143    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
144    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
145  },
146  (table) => [
147    uniqueIndex("files_network_shares_mount_path_idx").on(table.mountPath),
148    index("files_network_shares_created_at_idx").on(table.createdAt),
149  ],
150);
151
152export const filesLocalShares = pgTable(
153  "files_local_shares",
154  {
155    id: text("id").primaryKey(),
156    shareName: text("share_name").notNull(),
157    sourcePath: text("source_path").notNull(),
158    sharedPath: text("shared_path").notNull(),
159    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
160    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
161  },
162  (table) => [
163    uniqueIndex("files_local_shares_share_name_idx").on(table.shareName),
164    uniqueIndex("files_local_shares_source_path_idx").on(table.sourcePath),
165    uniqueIndex("files_local_shares_shared_path_idx").on(table.sharedPath),
166    index("files_local_shares_created_at_idx").on(table.createdAt),
167  ],
168);
169
170export const notifications = pgTable(
171  "notifications",
172  {
173    id: text("id").primaryKey(),
174    title: text("title").notNull(),
175    body: text("body").notNull(),
176    kind: text("kind").notNull().default("info"),
177    read: boolean("read").notNull().default(false),
178    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
179  },
180  (table) => [index("notifications_created_at_idx").on(table.createdAt.desc())],
181);
182
183export const scheduledTasks = pgTable(
184  "scheduled_tasks",
185  {
186    id: text("id").primaryKey(),
187    label: text("label").notNull(),
188    taskType: text("task_type").notNull(),
189    taskConfig: jsonb("task_config").notNull().default({}),
190    cronExpression: text("cron_expression").notNull(),
191    enabled: boolean("enabled").notNull().default(true),
192    lastRunAt: timestamp("last_run_at", { withTimezone: true }),
193    lastRunStatus: text("last_run_status"),
194    lastRunOutput: text("last_run_output"),
195    nextRunAt: timestamp("next_run_at", { withTimezone: true }),
196    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
197    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
198  },
199  (table) => [index("scheduled_tasks_next_run_at_idx").on(table.nextRunAt)],
200);
201
202export const scheduledTaskExecutions = pgTable(
203  "scheduled_task_executions",
204  {
205    id: text("id").primaryKey(),
206    taskId: text("task_id").notNull().references(() => scheduledTasks.id, { onDelete: "cascade" }),
207    status: text("status").notNull(),
208    output: text("output"),
209    startedAt: timestamp("started_at", { withTimezone: true }).notNull().defaultNow(),
210    durationMs: integer("duration_ms"),
211  },
212  (table) => [
213    index("scheduled_task_executions_task_id_idx").on(table.taskId),
214    index("scheduled_task_executions_started_at_idx").on(table.startedAt.desc()),
215  ],
216);
217
218export const settings = pgTable("settings", {
219  id: text("id").primaryKey().default("singleton"),
220  appearanceJson: jsonb("appearance_json").notNull().default({}),
221  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
222  googleClientId: text("google_client_id"),
223  googleClientSecretCiphertext: text("google_client_secret_ciphertext"),
224  googleClientSecretIv: text("google_client_secret_iv"),
225  googleClientSecretTag: text("google_client_secret_tag"),
226  googleRedirectUri: text("google_redirect_uri"),
227  tailscaleTailnet: text("tailscale_tailnet"),
228  tailscaleApiKeyCiphertext: text("tailscale_api_key_ciphertext"),
229  tailscaleApiKeyIv: text("tailscale_api_key_iv"),
230  tailscaleApiKeyTag: text("tailscale_api_key_tag"),
231  cloudflareTunnelEnabled: boolean("cloudflare_tunnel_enabled").notNull().default(false),
232  cloudflareTunnelDomain: text("cloudflare_tunnel_domain"),
233  cloudflareTunnelTokenCiphertext: text("cloudflare_tunnel_token_ciphertext"),
234  cloudflareTunnelTokenIv: text("cloudflare_tunnel_token_iv"),
235  cloudflareTunnelTokenTag: text("cloudflare_tunnel_token_tag"),
236  cloudflareApiTokenCiphertext: text("cloudflare_api_token_ciphertext"),
237  cloudflareApiTokenIv: text("cloudflare_api_token_iv"),
238  cloudflareApiTokenTag: text("cloudflare_api_token_tag"),
239  telemetryEnabled: boolean("telemetry_enabled").notNull().default(true),
240  telemetryInstanceId: text("telemetry_instance_id"),
241});
242
243export const filesGoogleDriveTokens = pgTable(
244  "files_google_drive_tokens",
245  {
246    id: text("id").primaryKey(),
247    email: text("email").notNull(),
248    displayName: text("display_name"),
249    accessTokenCiphertext: text("access_token_ciphertext").notNull(),
250    accessTokenIv: text("access_token_iv").notNull(),
251    accessTokenTag: text("access_token_tag").notNull(),
252    refreshTokenCiphertext: text("refresh_token_ciphertext"),
253    refreshTokenIv: text("refresh_token_iv"),
254    refreshTokenTag: text("refresh_token_tag"),
255    expiresAt: timestamp("expires_at", { withTimezone: true }),
256    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
257    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
258  },
259  (table) => [
260    uniqueIndex("files_google_drive_tokens_email_idx").on(table.email),
261    index("files_google_drive_tokens_created_at_idx").on(table.createdAt),
262  ],
263);
264
265export const filesTrashEntries = pgTable(
266  "files_trash_entries",
267  {
268    id: text("id").primaryKey(),
269    trashPath: text("trash_path").notNull(),
270    originalPath: text("original_path").notNull(),
271    deletedAt: timestamp("deleted_at", { withTimezone: true }).notNull().defaultNow(),
272  },
273  (table) => [
274    uniqueIndex("files_trash_entries_trash_path_idx").on(table.trashPath),
275    index("files_trash_entries_deleted_at_idx").on(table.deletedAt.desc()),
276  ],
277);
278