homeio

Homeio uses PostgreSQL with Drizzle ORM. The schema lives in lib/server/db/schema.ts; migrations live in drizzle/.

Tables

Table	Columns	Indexes / relationships
apps	id text pk, name text, status text default unknown, updated_at timestamptz default now	apps_name_idx
users	id text pk, username text unique, password_hash text, created_at timestamptz	users_username_idx
sessions	id text pk, user_id text, expires_at timestamptz, created_at timestamptz	user_id -> users.id cascade, sessions_user_id_idx, sessions_expires_at_idx
app_stacks	app_id text pk, template_name, stack_name, compose_path, status, web_ui_port, env_json jsonb, display_name, icon_url, installed_at, updated_at, is_up_to_date, last_update_check, local_digest, remote_digest	app_stacks_status_idx, app_stacks_web_ui_port_idx
app_operations	id text pk, app_id, action, status, progress_percent, current_step, error_message, started_at, finished_at, updated_at	app_operations_app_id_idx, app_operations_status_idx, app_operations_updated_at_idx
custom_store_apps	app_id text pk, name, icon_url, web_ui_url, source_type, source_text, compose_content, repository_url, created_at, updated_at	custom_store_apps_updated_at_idx
files_network_shares	id text pk, host, share, username, encrypted password fields, mount_path, created_at, updated_at	unique mount_path, created_at index
files_local_shares	id text pk, share_name, source_path, shared_path, created_at, updated_at	unique share/source/shared path indexes
notifications	id text pk, title, body, kind default info, read default false, created_at	notifications_created_at_idx
scheduled_tasks	id text pk, label, task_type, task_config jsonb, cron_expression, enabled, last_run_at, last_run_status, last_run_output, next_run_at, created_at, updated_at	scheduled_tasks_next_run_at_idx
scheduled_task_executions	id text pk, task_id, status, output, started_at, duration_ms	task_id -> scheduled_tasks.id cascade, task and started indexes
settings	id text pk default singleton, appearance_json jsonb, encrypted Google OAuth client secret fields, google_client_id, google_redirect_uri, telemetry_enabled, telemetry_instance_id, updated_at	singleton row pattern
files_google_drive_tokens	id text pk, email unique, display_name, encrypted access/refresh token fields, expires_at, created_at, updated_at	unique email, created index
files_trash_entries	id text pk, trash_path, original_path, deleted_at	unique trash path, deleted index

ER Diagram

erDiagram
  users ||--o{ sessions : owns
  scheduled_tasks ||--o{ scheduled_task_executions : records
  apps ||--o| app_stacks : represented_by
  app_stacks ||--o{ app_operations : has

  users {
    text id PK
    text username
    text password_hash
    timestamptz created_at
  }
  sessions {
    text id PK
    text user_id FK
    timestamptz expires_at
  }
  app_stacks {
    text app_id PK
    text stack_name
    text compose_path
    text status
    integer web_ui_port
  }
  app_operations {
    text id PK
    text app_id
    text action
    text status
    integer progress_percent
  }
  scheduled_tasks {
    text id PK
    text task_type
    jsonb task_config
    text cron_expression
  }
  scheduled_task_executions {
    text id PK
    text task_id FK
    text status
  }

Drizzle does not declare a foreign key from app_operations.app_id to app_stacks.app_id; the relationship is logical in repository/service code.

Migration Workflow

Use this sequence for schema changes:

npm run db:generate
npm run db:migrate
npm run db:init

npm run db:generate runs drizzle-kit generate and creates SQL in drizzle/.

npm run db:migrate runs drizzle-kit migrate and applies pending migrations.

npm run db:init runs scripts/db-migrate.ts. Per the audit, it drops drizzle.__drizzle_migrations before rerunning migrations. Use it for local initialization, not as a casual migration-history-preserving command.

npm run db:reset runs scripts/db-reset.ts and is destructive.

Drizzle Patterns

• Schema is centralized in lib/server/db/schema.ts.
• Database client helpers live in lib/server/db/drizzle.ts, lib/server/db/postgres.ts, and lib/server/db/query.ts.
• Repository functions live under owning server modules, for example lib/server/modules/apps/stacks-repository.ts and lib/server/modules/files/network-shares-repository.ts.
• JSON settings use jsonb fields such as settings.appearance_json, app_stacks.env_json, and scheduled_tasks.task_config.
• Google Drive OAuth app credentials are stored on the singleton settings row. The client secret uses the same ciphertext/IV/tag pattern as other encrypted file credentials; connected account tokens live separately in files_google_drive_tokens.
• The current codebase uses standard Drizzle query builders; no prepared-statement convention was found in the audited files.