Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS
JavaScript ai-agentsdashboarddeveloper-toolsdigital-twinquantified-selfsecond-brainself-hostedtailscale
// AI provider connection graph DDL — `ai_connections`, `ai_harness_bindings`
// and `ai_route_bindings` (#6367, design record
// docs/plans/2026-09-06-provider-connections-and-harnesses.md).
//
// The MANAGEMENT half of provider configuration: which backend a harness talks
// to, which harness configurations exist on it, and which executable route each
// one projects into `data/providers.json`. The provider file stays the
// EXECUTION contract — every connection-owned value is materialized back into
// it — so a downgraded install runs unchanged with these tables simply idle.
//
// Machine-local by construction and never federated: rows carry endpoints,
// credential material and this host's execution environment, which the privacy
// ADR (docs/decisions/2026-08-08-privacy-records-machine-local.md) keeps off the
// federation layer entirely. No `sync_sequence`, no tombstones, no
// PORTOS_SCHEMA_VERSIONS entry. Hard deletes, refused by the service while a
// row is still referenced.
//
// Ordering inside this array is load-bearing: `ai_harness_bindings` references
// `ai_connections`, and `ai_route_bindings` references `ai_harness_bindings`.
export const aiGraphDdl = [
// The backend a harness talks to. `credentials` is raw server-side secret
// material under the same private boundary as provider secrets — it is never
// published, logged, or federated; DTOs carry a `hasCredentials` boolean.
`CREATE TABLE IF NOT EXISTS ai_connections (
id UUID PRIMARY KEY,
revision INTEGER NOT NULL DEFAULT 1,
kind TEXT NOT NULL,
label TEXT NOT NULL DEFAULT '',
transports JSONB NOT NULL DEFAULT '{}'::jsonb,
credentials JSONB NOT NULL DEFAULT '{}'::jsonb,
catalog JSONB NOT NULL DEFAULT '{"state":"unknown","models":[]}'::jsonb,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
)`,
// A connection IS a service instance (#7563, epic #7561): a `SERVICE_DEFINITIONS`
// row (`definition_id`), addressed by a `slug` (the composite-id segment a
// later slice reads), declared under one of the definition's `plan`s, and
// switchable off without deleting it (`enabled`). `credential_via` says how
// the instance authenticates — `stored` (a key on the row), `env`, `cli-login`
// (the harness signed in) or `bootstrap` (a launch wrapper supplies the key at
// spawn, so no secret is ever stored). All additive with defaults, so a row
// written by an older release reads back unchanged and the boot reconcile
// pass fills `slug` / `definition_id` / `plan` from `kind` — never a seed.
`ALTER TABLE ai_connections ADD COLUMN IF NOT EXISTS slug TEXT`,
`ALTER TABLE ai_connections ADD COLUMN IF NOT EXISTS definition_id TEXT`,
`ALTER TABLE ai_connections ADD COLUMN IF NOT EXISTS plan TEXT NOT NULL DEFAULT 'paid'`,
`ALTER TABLE ai_connections ADD COLUMN IF NOT EXISTS enabled BOOLEAN NOT NULL DEFAULT true`,
`ALTER TABLE ai_connections ADD COLUMN IF NOT EXISTS credential_via TEXT NOT NULL DEFAULT 'stored'`,
// Partial: a row the backfill has not reached yet carries NULL, and two NULLs
// never collide, so the index constrains only addressed instances.
`CREATE UNIQUE INDEX IF NOT EXISTS uq_ai_connections_slug
ON ai_connections (slug)
WHERE slug IS NOT NULL`,
// One harness configuration on one connection. `harness_id` is NULL only for
// a direct API binding, which is why the uniqueness below needs two partial
// indexes rather than one constraint: in Postgres a NULL never equals another
// NULL, so a plain UNIQUE(connection_id, harness_id, variant_key) would let
// unlimited duplicate `default` API bindings onto one connection.
`CREATE TABLE IF NOT EXISTS ai_harness_bindings (
id UUID PRIMARY KEY,
revision INTEGER NOT NULL DEFAULT 1,
connection_id UUID NOT NULL REFERENCES ai_connections (id),
harness_id TEXT,
variant_key TEXT NOT NULL DEFAULT 'default',
label TEXT NOT NULL DEFAULT '',
enabled BOOLEAN NOT NULL DEFAULT false,
selected_models JSONB NOT NULL DEFAULT '[]'::jsonb,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
)`,
`CREATE UNIQUE INDEX IF NOT EXISTS uq_ai_harness_bindings_variant
ON ai_harness_bindings (connection_id, harness_id, variant_key)
WHERE harness_id IS NOT NULL`,
`CREATE UNIQUE INDEX IF NOT EXISTS uq_ai_harness_bindings_api_variant
ON ai_harness_bindings (connection_id, variant_key)
WHERE harness_id IS NULL`,
`CREATE INDEX IF NOT EXISTS idx_ai_harness_bindings_connection
ON ai_harness_bindings (connection_id)`,
// The executable route. `provider_id` is the PRIMARY KEY because it IS the
// saved-selection contract every scheduled task, CoS pin, orchestration role
// and fallback reference already stores — route ids are never reused.
//
// `projected` is the connection-owned snapshot last acknowledged as present
// in providers.json; `pending` is one committed to the DB but not yet
// acknowledged. A crash between the two is recoverable precisely because both
// are kept: reconciliation compares the file's ACTUAL values against both
// before deciding to retry, acknowledge, or refuse to overwrite a third,
// externally changed value. Both snapshots can carry secrets, so they are as
// private as `ai_connections.credentials`.
`CREATE TABLE IF NOT EXISTS ai_route_bindings (
provider_id TEXT PRIMARY KEY,
binding_id UUID NOT NULL REFERENCES ai_harness_bindings (id),
mode TEXT NOT NULL CHECK (mode IN ('cli', 'tui', 'api')),
model_map JSONB NOT NULL DEFAULT '{}'::jsonb,
projected JSONB NOT NULL DEFAULT '{}'::jsonb,
pending JSONB,
pending_revision INTEGER,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
)`,
// Hand-authored canonical→executable aliases, kept apart from `model_map`
// on purpose (#6369): a refresh rewrites what it OBSERVED, and merging the
// two into one column would make the next refresh silently delete the
// correction a human typed. Read as `{ ...model_map, ...overrides }`.
`ALTER TABLE ai_route_bindings ADD COLUMN IF NOT EXISTS model_alias_overrides JSONB NOT NULL DEFAULT '{}'::jsonb`,
`CREATE UNIQUE INDEX IF NOT EXISTS uq_ai_route_bindings_binding_mode
ON ai_route_bindings (binding_id, mode)`,
`CREATE INDEX IF NOT EXISTS idx_ai_route_bindings_binding
ON ai_route_bindings (binding_id)`,
];