Database schema
Tables, columns, relationships, and the in-memory caches in front of them.
WiseHosting uses PostgreSQL. The schema is defined twice in lock-step: as GORM models in internal/database/models.go (used by the Go application at runtime) and as versioned golang-migrate SQL in internal/database/migrations/*.up.sql. RunMigrations() (called from Connect) embeds the SQL via embed.FS, hands it to golang-migrate's postgres driver, and applies any pending versions. A schema_migrations row tracks current state. There is no more AutoMigrate path — schema changes ship as new 000N_*.up.sql / 000N_*.down.sql pairs.
Two sources of truth, kept in sync
Why both Go structs and SQL files? The Go structs let GORM build queries (db.First(&app, id)) without us writing SQL. The migration files let us evolve the schema without GORM's AutoMigrate rewriting columns at startup — AutoMigrate is great for a prototype but ruins production change control.
Rule of thumb: add a migration first, then update the model, then write the code that uses it. Forgetting the migration step is the #1 cause of "works on my laptop, breaks in production".
Tables
users
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| string | unique, ≤255 | |
| google_id | string | unique, ≤64 — only sign-in identity |
| name | string | ≤255 |
| avatar_url | text | |
| plan | string | default free |
| theme_pref | string | system / light / dark |
| totp_secret | text | AES-GCM-encrypted; empty until enrol |
| totp_enabled | bool | default false; flipped on first verify |
| totp_verified_at | *time | last successful TOTP verification |
| created_at | time | default CURRENT_TIMESTAMP |
| updated_at | time | maintained by BeforeCreate/Update |
| is_active | bool | default true |
Apps []App cascade-deletes when a user is hard-deleted.
workers
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| name | string | unique, required |
| ip_address | string | ≤45 |
| api_key_hash | string | unique, JSON-omitted, sha256 hex (64) of raw key — raw never stored |
| zone | string | DNS suffix this worker owns |
| region_name | string | display name |
| status | string | online / offline, indexed |
| capacity_cpu | int | default 4 |
| capacity_memory | int64 | default 4 GiB |
| used_cpu | int | counter (incremented on assign) |
| used_memory | int64 | counter |
| last_heartbeat | time | indexed |
apps
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| uuid | string | unique, public identifier |
| user_id | int | FK, indexed, composite uniq with name |
| name | string | unique per user |
| repo_url | text | required |
| subdomain | string | unique |
| webhook_secret | string | JSON-omitted |
| webhook_id | *int64 | provider-side hook id |
| auto_deploy | bool | default true |
| framework | string | default dockerfile |
| root_dir | string | |
| install_command | text | |
| build_command | text | |
| start_command | text | |
| status | string | default pending, indexed |
| worker_id | *int | FK, indexed |
| container_id | string | |
| port | int | host port allocated |
| last_violation | text | |
| violation_at | *time |
deployments
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| app_id | int | FK |
| worker_id | *int | FK |
| status | string | |
| commit_sha | string | |
| commit_message | text | |
| commit_author | string | |
| branch | string | |
| build_logs | text | |
| error_message | text | |
| started_at | *time | |
| completed_at | *time | |
| created_at | time | indexed desc for recency queries |
jobs
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| type | string | deploy / restart / start / stop / delete |
| app_id | *int | |
| deployment_id | *int | |
| worker_id | *int | |
| status | string | pending / assigned / processing / completed / failed |
| priority | int | default 5; composite index with status desc |
| payload | text | AES-GCM-encrypted JSON |
| error_message | text | |
| last_message | string | |
| progress | int | 0–100 |
| assigned_at | *time | |
| started_at | *time | |
| completed_at | *time |
app_env_vars
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| app_id | int | composite uniq with key |
| key | string | matches ^[A-Z][A-Z0-9_]*$ |
| value | text | AES-GCM-encrypted |
git_provider_tokens
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| user_id | int | composite uniq with provider |
| provider | string | github / gitlab / bitbucket / codeberg |
| username | string | |
| token | text | AES-GCM-encrypted |
webhooks
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| user_id | int | FK |
| app_id | *int | optional scoping |
| name | string | |
| url | text | must be HTTPS |
| secret | string | whsec_... 32 hex bytes |
| events | text | JSON array of event names |
| active | bool | default true |
| last_delivery_at | *time | |
| last_status_code | int | |
| last_success | bool | |
| fail_count_7d | int |
webhook_deliveries
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| webhook_id | int | FK |
| event | string | |
| payload | text | request body sent |
| status_code | int | |
| success | bool | indexed |
| error | text | |
| attempt | int | |
| duration_ms | int | |
| attempted_at | time | indexed |
sessions
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| user_id | int | FK, indexed |
| token_hash | string | sha256(token) hex, uniqueIndex — never raw |
| ip | string | ≤64 |
| city / country / country_code | string | |
| user_agent | text | capped at 512 bytes |
| two_factor_verified | bool | true if the session was completed via 2FA |
| created_at | time | |
| last_seen_at | time | bumped at most once / minute |
| revoked_at | *time | nullable |
audit_events
Renamed from login_events in migration 0005. Written on every sensitive user action; failures here are logged but never break the action.
Recorded kinds: login, logout, session_revoked, app_deploy, app_restart, app_start, app_stop, app_delete, env_set, env_delete, git_token_revoke, domain_create, domain_verify, domain_delete.
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| user_id | int | indexed |
| kind | string | ≤32 |
| ip / city / country / country_code | string | |
| user_agent | text | |
| metadata | text | JSON blob — varies by kind |
| created_at | time | indexed |
app_alert_rules
Per-app rules driving the threshold poller in internal/alerts. The (app_id, kind) pair is unique.
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| app_id | int | uniqueIndex with kind |
| user_id | int | |
| kind | string | cpu / memory / network / disk / offline / crashloop / deployment_failed |
| enabled | bool | |
| threshold | float64 | percent / MB·s⁻¹ / 0 for event-driven kinds |
| sustain_minutes | int | hysteresis window before fire and resolve |
| severity | string | warning / active |
alerts
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| user_id | int | indexed |
| app_id | *int | nullable for platform-wide alerts |
| kind | string | deployment / violation / threshold / app |
| severity | string | active / warning / info |
| status | string | active / acknowledged / resolved |
| source | string | rule kind (cpu, offline, …) |
| title | string | |
| message | text | |
| created_at | time | |
| resolved_at | *time |
backup_codes
One-time TOTP recovery codes. Generated 10-at-a-time on first POST /api/me/2fa/verify; hashes stored argon2 (code_hash). used_at is stamped on first use and the row stays for audit. Cleared en-bloc when 2FA is disabled.
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| user_id | int | FK, indexed |
| code_hash | string | argon2 hash of the plaintext code |
| used_at | *time | nullable; set on consumption |
| created_at | time |
domains
Custom hostnames that route to user apps via Traefik's HTTP provider (no container restart). The user adds a CNAME from <hostname> to the system subdomain, then proves control by adding a TXT record at _wisehosting.<hostname> matching verification_token. hostname is globally unique — no two users can claim the same name.
| Column | Type | Notes |
|---|---|---|
| id | int | PK |
| user_id | int | FK, ON DELETE CASCADE |
| app_id | int | FK, ON DELETE CASCADE |
| hostname | string | ≤253, globally unique |
| verification_token | string | ≤64, opaque (wh-<hex>) |
| verified_at | *time | nullable until TXT match |
| last_check_at | *time | last verification attempt |
| last_check_error | text | last failure reason |
| created_at / updated_at | time |
Verified hosts are emitted in the worker's /v1/traefik/config poll output as Host(\a`) || Host(`b`)` rules.
pending_totps
Short-lived state for the 2FA challenge step between OAuth success and final session issue. Replaces an earlier in-process sync.Map so the challenge survives a control-plane restart.
| Column | Type | Notes |
|---|---|---|
| token | string | PK; sha256-style opaque cookie body |
| user_id | int | FK |
| provider | string | always google today; reserved for future SSO |
| expires_at | time | 5-min TTL; PrunePendingTOTPs() runs every 6h |
schema_migrations
Tracks golang-migrate state. Owned by the migration runner — application code does not read or write it directly.
| Column | Type | Notes |
|---|---|---|
| version | int64 | last applied migration version |
| dirty | bool | true if a migration failed mid-apply |
A dirty row blocks subsequent startups until an operator manually reconciles, which is intentional — silent partial schema drift is the failure mode this replaces.
usage_samples
5-min rollup written by internal/usage.Recorder. Composite PK (app_id, bucket_start). 90-day retention enforced by a daily pruner.
| Column | Type | Notes |
|---|---|---|
| app_id | int | PK |
| bucket_start | time | PK, UTC-truncated to 5-min |
| bucket_size_sec | int | always 300 |
| user_id | int | indexed |
| cpu_pct_avg | float64 | |
| cpu_pct_max | float64 | |
| mem_bytes_avg | int64 | |
| mem_bytes_max | int64 | |
| net_mbps_avg | float64 | |
| net_mbps_max | float64 | |
| net_bytes_delta | int64 | cumulative bytes transferred |
In-memory caches
GormDB keeps a sync.Map cache with entry-specific TTLs:
| Key shape | TTL | Used for |
|---|---|---|
worker:apikey:<hash> | 30s | Worker auth path (key already hashed) |
app:id:<id> | default | Single-app reads |
app:user:<userID>:<name> | default | App lookup by name |
apps:user:<userID> | 2 min | Dashboard apps list |
Cache invalidation is explicit — InvalidateUserCache and InvalidateAppCache are called on every mutation.
Encryption at rest
The master secret is api_server.secret. Per-purpose 256-bit keys are derived from it via HKDF-SHA256 with a purpose-specific info label, so a leak of one derived key never reveals another:
| Purpose label | Used for |
|---|---|
wisehosting-aes-v1 | AES-256-GCM column encryption |
wisehosting-oauth-state-v1 | OAuth state token signing |
wisehosting-oauth-bind-v1 | OAuth bind cookie HMAC |
wisehosting-completion-v1 | sign-in completion ticket |
wisehosting-worker-jwt-v1 | Worker JWT HS256 signing |
EnableSecretEncryption constructs an AES-256-GCM cipher from the AES purpose key. Every encrypted column stores ciphertext as v1:<base64url-no-pad>. Plaintext values written before encryption was enabled are passed through transparently, allowing zero-downtime rollout and rotation.
Worker API keys are not encrypted with the AES key — only sha256(rawKey) is stored as workers.api_key_hash. The raw key never lives in the DB.