HostAgentics Docs

HostAgentics Database

Last updated: 2026-08-06

This is an internal operational document describing the platform database: the Drizzle ORM schema, the schema domains, the migration workflow, and indexing conventions. The database is the control plane's system of record for identity, billing, runtimes, and platform operations. Customer-facing documentation never references schema internals; table names and column details here are for engineers and operators.

1. Stack

  • **Database**: PostgreSQL, accessed through Drizzle ORM (`packages/db`). TypeScript strict mode; all schema files live under `packages/db/src/`.
  • **Client**: `packages/db/src/client.ts` exposes the Drizzle client; `createDb(databaseUrl)` is the single entry point used by `apps/api` and `apps/worker`.
  • **Migrations**: generated and applied with `drizzle-kit`. Migrations are forward-only; schema changes ship as new migration files.
  • **Seed data**: `packages/db/src/seed.ts` inserts canonical plans/entitlements (mirror of `packages/pricing`), feature flags, and status components. Seeding runs once per environment as part of the release step, not on every deploy.

    • 2. Schema domains

    The schema is split into four files by domain, all re-exported from `packages/db/src/schema.ts`.

    Identity (`schema-identity.ts`)

    Better Auth owns the core auth tables (`users`, `accounts`, `sessions`, `verificationTokens`); HostAgentics stores organization structure alongside them:

  • `users` — email, verified flag, password hash (only when not using OAuth/magic link), `isAdmin` platform-operator flag.
  • `sessions` — Better Auth session model with expiry; revocation is delegated to Better Auth.
  • `accounts` — OAuth/credential provider accounts (credential, google, github).
  • `mfaMethods` — TOTP and recovery methods; TOTP secrets are stored as encrypted envelopes, never plaintext.
  • `organizations` — the tenant boundary; carries the Stripe customer ID.
  • `memberships` — org ↔ user with a role (`owner|admin|developer|operator|billing|viewer`). Unique on (org, user).
  • `invitations` — pending invites with hashed token, role, expiry, acceptance timestamp.
  • `featureFlags`, `adminNotes` — platform support tables. The `n8n_provisioning` flag lives here (see `docs/n8n-license-gate.md`).

    • Billing (`schema-billing.ts`)

    All money amounts are integer EUR cents. Prices are never trusted from the browser; the server maps canonical product keys to Stripe price IDs.

  • `products` — canonical product keys (`hostagentics_n8n_cloud`, `hostagentics_openclaw_cloud`, `hostagentics_hermes_cloud`, `hostagentics_complete`, `hostagentics_resource_boost`, `hostagentics_ai_credit_10/25/50/100`), kind (`subscription|addon|one_time`).
  • `plans` — a product × interval (monthly/annual) pair with its Stripe price ID and a `currentVersion` pointer.
  • `planVersions` — versioned entitlement snapshots; changing a plan never mutates history.
  • `planEntitlements` — per-runtime-kind entitlements (n8n/openclaw/hermes) for a plan version.
  • `subscriptions` / `subscriptionItems` — subscription lifecycle (active|past_due|grace_period|suspended|cancelled|retention|deleted); Resource Boost items are attributed to one explicit runtime.
  • `invoices` / `payments` / `refunds` — issued amounts, tax, payment outcomes.
  • `processedStripeEvents` — webhook event dedupe (primary key = Stripe event ID) for replay protection.
  • `aiCreditWallets` / `aiCreditTransactions` — one wallet per org; balance in cents, **never negative**; every grant, top-up, consumption, expiry, and refund is an append-only transaction with an idempotency key.
  • `aiProviderKeys` — managed and BYOK model-provider keys: encrypted secret envelope, display hint only, monthly spend limits, rotation version, revocation flag.

    • Runtime (`schema-runtime.ts`)

  • `infrastructureShards` — capacity units: a shard is a named set of provider projects in a region (`europe`, `north-america`, `asia-pacific`), with capacity accounting, health, and an accept-new-runtimes flag.
  • `runtimes` — the customer runtime record: kind (n8n|openclaw|hermes), unique subdomain, status (provisioning state machine), product key, region, update channel, safe-mode flag.
  • `providerResources` — **internal only**: maps each runtime to concrete provider resource IDs (service, volume, domain, database, deployment). These identifiers are never serialized into customer-facing responses; the provider-leak test enforces this.
  • `encryptedSecrets` — per-runtime secret scope: purpose (gateway-token, db-password, encryption-key, bot-token, webhook-secret, n8n-api-key), encrypted envelope, key version, rotation version. Unique on (runtime, purpose).
  • `runtimeOperations` / `operationSteps` — every lifecycle operation (provision, restart, pause, resume, update, backup, restore, repair, delete, apply-resources) with idempotency key, correlation ID, attempt/maxAttempts, safe message, internal HA-* diagnostic code, retryability, and per-step status.
  • `runtimeConfigurations` / `runtimeResources` — non-secret config (timezone, editor base URL) and the applied resource plan (CPU, memory, storage, transfer, concurrency, retention).
  • `runtimeVersions` / `runtimeDeployments` — the tested-only version catalog (digest-pinned, tested flag, security status, migration/rollback compatibility) and per-runtime deployment history.
  • `runtimeDomains` — managed (`*.runtime.hostagentics.com`) and future custom domains with certificate status.
  • `backups` / `restoreOperations` / `updateOperations` — provider snapshot manifests (manifest checksum, reported size, expiry) and gated completion records: a restore is completed only after health verification, an update only after stabilization. `encryptionKeyEnvelope` is reserved and unused for provider-native snapshots.
  • `healthChecks` / `metricSnapshots` / `usageSnapshots` / `limitEvents` / `logReferences` — monitoring data. Metric fields are nullable: a null value means data unavailable, and surfaces must display "Data unavailable" rather than an invented number. Limit events record the 70/85/95/100% thresholds.

    • Platform (`schema-platform.ts`)

  • Relay: `relayTokens` (hashed, hint only, scoped claims), `relayEvents`, `relayNonces` (single-use nonce store), `agentRuns` / `workflowRuns` (run task / trigger workflow records with idempotency keys), `webhookDeliveries`.
  • `notifications` / `notificationPreferences` — in-app and email notification records.
  • `auditEvents` / `securityEvents` — append-only audit trail (actor, action, target, correlation ID) and security telemetry (throttled logins, MFA failures, rate limiting).
  • `supportRequests` — support tickets with diagnostics flag and admin notes.
  • `statusComponents` / `incidents` — status page state (operational/degraded/partial_outage/major_outage/maintenance) and incident records with public updates.
  • `migrationImports` — validated imports from customers migrating existing n8n/OpenClaw/Hermes setups; payloads are validated before storage and applied only on explicit user action.
  • Internal cost accounting (`internalCostSnapshots`), competitor pricing intelligence (`competitorRecords`, `competitorPriceSnapshots`, `priceRecommendations`), and `blogPosts` metadata complete the platform schema.

    • 3. Migration workflow

    1. Schema changes are made in the domain files under `packages/db/src/`.

    2. Generate the migration: `pnpm --filter @hostagentics/db db:generate` (drizzle-kit). Review the generated SQL before committing.

    3. **Migrations run exactly once per release, in the release step, before new app code serves traffic** (`db:migrate` is wired as the release command on the API service and is idempotent). Never run migrations from two services concurrently; a second concurrent run is the most common cause of split-brain schema state.

    4. Because migrations are forward-only, a rollback across a schema change requires a forward fix or a restore from backup (see `docs/recovery-runbook.md`).

    4. Indexing notes

  • Membership lookups are the hottest path in authorization: `memberships_org_user_idx` (unique) and `memberships_user_idx`.
  • Runtime, backup, health, metric, and audit queries are always scoped by org or runtime and time-ordered; the indexes on `(runtimeId, createdAt)` / `(organizationId, createdAt)` match that access pattern.
  • Uniqueness constraints carry the integrity guarantees: subdomains, membership (org,user), encrypted secrets (runtime,purpose), usage snapshots (runtime,period), relay token hashes, processed Stripe event IDs, and idempotency keys.
  • `usageSnapshots` is unique per (runtime, periodStart) so the limit engine can upsert safely; `limitEvents` records each threshold crossing exactly once per runtime.

    • 5. Related documents

  • `docs/architecture.md` — where the database sits in the control plane
  • `docs/deployment.md` — release-step migration execution
  • `docs/security.md` — at-rest encryption of secret envelopes stored in this schema