homeio

1CREATE TABLE IF NOT EXISTS "app_operations" (
2	"id" text PRIMARY KEY NOT NULL,
3	"app_id" text NOT NULL,
4	"action" text NOT NULL,
5	"status" text NOT NULL,
6	"progress_percent" integer DEFAULT 0 NOT NULL,
7	"current_step" text DEFAULT 'queued' NOT NULL,
8	"error_message" text,
9	"started_at" timestamp with time zone,
10	"finished_at" timestamp with time zone,
11	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
12);
13--> statement-breakpoint
14CREATE TABLE IF NOT EXISTS "app_stacks" (
15	"app_id" text PRIMARY KEY NOT NULL,
16	"template_name" text NOT NULL,
17	"stack_name" text NOT NULL,
18	"compose_path" text NOT NULL,
19	"status" text DEFAULT 'not_installed' NOT NULL,
20	"web_ui_port" integer,
21	"env_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
22	"display_name" text,
23	"icon_url" text,
24	"installed_at" timestamp with time zone,
25	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
26	"is_up_to_date" boolean DEFAULT true,
27	"last_update_check" timestamp with time zone,
28	"local_digest" text,
29	"remote_digest" text
30);
31--> statement-breakpoint
32CREATE TABLE IF NOT EXISTS "apps" (
33	"id" text PRIMARY KEY NOT NULL,
34	"name" text NOT NULL,
35	"status" text DEFAULT 'unknown' NOT NULL,
36	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
37);
38--> statement-breakpoint
39CREATE TABLE IF NOT EXISTS "custom_store_apps" (
40	"app_id" text PRIMARY KEY NOT NULL,
41	"name" text NOT NULL,
42	"icon_url" text,
43	"web_ui_url" text,
44	"source_type" text NOT NULL,
45	"source_text" text NOT NULL,
46	"compose_content" text NOT NULL,
47	"repository_url" text,
48	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
49	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
50);
51--> statement-breakpoint
52CREATE TABLE IF NOT EXISTS "files_local_shares" (
53	"id" text PRIMARY KEY NOT NULL,
54	"share_name" text NOT NULL,
55	"source_path" text NOT NULL,
56	"shared_path" text NOT NULL,
57	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
58	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
59);
60--> statement-breakpoint
61CREATE TABLE IF NOT EXISTS "files_network_shares" (
62	"id" text PRIMARY KEY NOT NULL,
63	"host" text NOT NULL,
64	"share" text NOT NULL,
65	"username" text NOT NULL,
66	"password_ciphertext" text NOT NULL,
67	"password_iv" text NOT NULL,
68	"password_tag" text NOT NULL,
69	"mount_path" text NOT NULL,
70	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
71	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
72);
73--> statement-breakpoint
74CREATE TABLE IF NOT EXISTS "files_trash_entries" (
75	"id" text PRIMARY KEY NOT NULL,
76	"trash_path" text NOT NULL,
77	"original_path" text NOT NULL,
78	"deleted_at" timestamp with time zone DEFAULT now() NOT NULL
79);
80--> statement-breakpoint
81CREATE TABLE IF NOT EXISTS "sessions" (
82	"id" text PRIMARY KEY NOT NULL,
83	"user_id" text NOT NULL,
84	"expires_at" timestamp with time zone NOT NULL,
85	"created_at" timestamp with time zone DEFAULT now() NOT NULL
86);
87--> statement-breakpoint
88CREATE TABLE IF NOT EXISTS "users" (
89	"id" text PRIMARY KEY NOT NULL,
90	"username" text NOT NULL,
91	"password_hash" text NOT NULL,
92	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
93	CONSTRAINT "users_username_unique" UNIQUE("username")
94);
95--> statement-breakpoint
96DO $$ BEGIN
97  ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
98EXCEPTION WHEN duplicate_object THEN NULL;
99END $$;--> statement-breakpoint
100CREATE INDEX IF NOT EXISTS "app_operations_app_id_idx" ON "app_operations" USING btree ("app_id");--> statement-breakpoint
101CREATE INDEX IF NOT EXISTS "app_operations_status_idx" ON "app_operations" USING btree ("status");--> statement-breakpoint
102CREATE INDEX IF NOT EXISTS "app_operations_updated_at_idx" ON "app_operations" USING btree ("updated_at" DESC NULLS LAST);--> statement-breakpoint
103CREATE INDEX IF NOT EXISTS "app_stacks_status_idx" ON "app_stacks" USING btree ("status");--> statement-breakpoint
104CREATE INDEX IF NOT EXISTS "app_stacks_web_ui_port_idx" ON "app_stacks" USING btree ("web_ui_port");--> statement-breakpoint
105CREATE INDEX IF NOT EXISTS "apps_name_idx" ON "apps" USING btree ("name");--> statement-breakpoint
106CREATE INDEX IF NOT EXISTS "custom_store_apps_updated_at_idx" ON "custom_store_apps" USING btree ("updated_at" DESC NULLS LAST);--> statement-breakpoint
107CREATE UNIQUE INDEX IF NOT EXISTS "files_local_shares_share_name_idx" ON "files_local_shares" USING btree ("share_name");--> statement-breakpoint
108CREATE UNIQUE INDEX IF NOT EXISTS "files_local_shares_source_path_idx" ON "files_local_shares" USING btree ("source_path");--> statement-breakpoint
109CREATE UNIQUE INDEX IF NOT EXISTS "files_local_shares_shared_path_idx" ON "files_local_shares" USING btree ("shared_path");--> statement-breakpoint
110CREATE INDEX IF NOT EXISTS "files_local_shares_created_at_idx" ON "files_local_shares" USING btree ("created_at");--> statement-breakpoint
111CREATE UNIQUE INDEX IF NOT EXISTS "files_network_shares_mount_path_idx" ON "files_network_shares" USING btree ("mount_path");--> statement-breakpoint
112CREATE INDEX IF NOT EXISTS "files_network_shares_created_at_idx" ON "files_network_shares" USING btree ("created_at");--> statement-breakpoint
113CREATE UNIQUE INDEX IF NOT EXISTS "files_trash_entries_trash_path_idx" ON "files_trash_entries" USING btree ("trash_path");--> statement-breakpoint
114CREATE INDEX IF NOT EXISTS "files_trash_entries_deleted_at_idx" ON "files_trash_entries" USING btree ("deleted_at" DESC NULLS LAST);--> statement-breakpoint
115CREATE INDEX IF NOT EXISTS "sessions_user_id_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint
116CREATE INDEX IF NOT EXISTS "sessions_expires_at_idx" ON "sessions" USING btree ("expires_at");--> statement-breakpoint
117CREATE INDEX IF NOT EXISTS "users_username_idx" ON "users" USING btree ("username");
118