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
import { query, withTransaction } from '../lib/db.js';
import { toPgJson } from '../lib/pgJson.js';
/**
* Postgres adapter for the provider connection graph — `ai_connections`,
* `ai_harness_bindings`, `ai_route_bindings` (#6367).
*
* db-primary per `docs/STORAGE.md`: app-native relational identities with real
* foreign keys, not an externally edited file. **Machine-local — never
* federated**: every row names this host's endpoints, credentials and execution
* environment (ADR `docs/decisions/2026-08-08-privacy-records-machine-local.md`).
* No `sync_sequence`, no tombstones, no `PORTOS_SCHEMA_VERSIONS` entry. Covered
* by the normal Postgres dump in backup, like every other db-primary table.
*
* This module is only row I/O and revision checks. The projection engine, the
* reconciliation policy and the serialization against legacy provider writes
* all live in `providerGraph.js`; the pure planning lives in
* `server/lib/providerGraphRecords.js`. Keeping the split means the interesting
* decisions are testable without a database.
*
* Every write here runs inside one transaction, so a graph revision and its
* pending projection snapshot commit together or not at all — that atomicity is
* what makes an interrupted file write recoverable rather than ambiguous.
*/
const connectionRow = (row) => ({
id: row.id,
revision: row.revision,
kind: row.kind,
label: row.label,
transports: row.transports || {},
credentials: row.credentials || {},
catalog: row.catalog || { state: 'unknown', models: [] },
// Service-instance columns (#7563). `slug` / `definitionId` are null on a
// row the boot backfill has not reached; the rest carry column defaults.
slug: row.slug ?? null,
definitionId: row.definition_id ?? null,
plan: row.plan ?? 'paid',
enabled: row.enabled !== false,
credentialVia: row.credential_via ?? 'stored',
});
const bindingRow = (row) => ({
id: row.id,
revision: row.revision,
connectionId: row.connection_id,
harnessId: row.harness_id,
variantKey: row.variant_key,
label: row.label,
enabled: row.enabled,
selectedModels: row.selected_models || [],
});
const routeRow = (row) => ({
providerId: row.provider_id,
bindingId: row.binding_id,
mode: row.mode,
modelMap: row.model_map || {},
modelAliasOverrides: row.model_alias_overrides || {},
projected: row.projected || {},
pending: row.pending ?? null,
pendingRevision: row.pending_revision ?? null,
});
/** The whole graph. Small by nature — one row per configured route. */
export async function readGraph() {
const [connections, bindings, routes] = await Promise.all([
query('SELECT * FROM ai_connections ORDER BY created_at, id'),
query('SELECT * FROM ai_harness_bindings ORDER BY created_at, id'),
query('SELECT * FROM ai_route_bindings ORDER BY provider_id'),
]);
return {
connections: connections.rows.map(connectionRow),
bindings: bindings.rows.map(bindingRow),
routes: routes.rows.map(routeRow),
};
}
// The service columns (#7563) are PRESERVED on conflict when the caller did not
// name them: a reconciliation regroup rebuilds a connection from its routes and
// knows nothing about the slug a human addresses it by, and `plan` / `enabled`
// / `credential_via` are NOT NULL, so the insert half substitutes the column
// default rather than a NULL the constraint would refuse before the conflict
// clause could run.
const upsertConnection = (client, connection) => client.query(
`INSERT INTO ai_connections
(id, revision, kind, label, transports, credentials, catalog, slug, definition_id, plan, enabled, credential_via, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, COALESCE($10, 'paid'), COALESCE($11, true), COALESCE($12, 'stored'), NOW())
ON CONFLICT (id) DO UPDATE SET
revision = ai_connections.revision + 1,
kind = EXCLUDED.kind, label = EXCLUDED.label,
transports = EXCLUDED.transports, credentials = EXCLUDED.credentials,
catalog = EXCLUDED.catalog,
slug = COALESCE($8, ai_connections.slug),
definition_id = COALESCE($9, ai_connections.definition_id),
plan = COALESCE($10, ai_connections.plan),
enabled = COALESCE($11, ai_connections.enabled),
credential_via = COALESCE($12, ai_connections.credential_via),
updated_at = NOW()`,
[connection.id, connection.revision ?? 1, connection.kind, connection.label ?? '',
toPgJson(connection.transports || {}), toPgJson(connection.credentials || {}),
toPgJson(connection.catalog || { state: 'unknown', models: [] }),
connection.slug ?? null, connection.definitionId ?? null, connection.plan ?? null,
typeof connection.enabled === 'boolean' ? connection.enabled : null, connection.credentialVia ?? null],
);
const upsertBinding = (client, binding) => client.query(
`INSERT INTO ai_harness_bindings
(id, revision, connection_id, harness_id, variant_key, label, enabled, selected_models, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW())
ON CONFLICT (id) DO UPDATE SET
revision = ai_harness_bindings.revision + 1,
connection_id = EXCLUDED.connection_id, harness_id = EXCLUDED.harness_id,
variant_key = EXCLUDED.variant_key, label = EXCLUDED.label,
enabled = EXCLUDED.enabled, selected_models = EXCLUDED.selected_models, updated_at = NOW()`,
[binding.id, binding.revision ?? 1, binding.connectionId, binding.harnessId ?? null,
binding.variantKey, binding.label ?? '', binding.enabled === true,
toPgJson(binding.selectedModels || [])],
);
// `model_alias_overrides` is set on INSERT and deliberately absent from the
// conflict update: an import, a reconciliation re-import and a binding split
// all carry no overrides, so updating the column from EXCLUDED would delete a
// human's hand-authored aliases as a side effect of an unrelated repair. Only
// `saveRouteModelAliases` writes it.
const upsertRoute = (client, route) => client.query(
`INSERT INTO ai_route_bindings
(provider_id, binding_id, mode, model_map, model_alias_overrides, projected, pending, pending_revision, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW())
ON CONFLICT (provider_id) DO UPDATE SET
binding_id = EXCLUDED.binding_id, mode = EXCLUDED.mode, model_map = EXCLUDED.model_map,
projected = EXCLUDED.projected, pending = EXCLUDED.pending,
pending_revision = EXCLUDED.pending_revision, updated_at = NOW()`,
[route.providerId, route.bindingId, route.mode, toPgJson(route.modelMap || {}),
toPgJson(route.modelAliasOverrides || {}),
toPgJson(route.projected || {}),
route.pending == null ? null : toPgJson(route.pending),
route.pendingRevision ?? null],
);
/**
* Insert a freshly built graph in one transaction.
*
* Writes exactly what it is handed — a fragment with no routes is legitimate
* (an emptied binding kept for explicit cleanup), so this must not short-circuit
* on an empty route list.
*/
export async function writeGraph({ connections, bindings, routes }) {
await withTransaction(async (client) => {
for (const connection of connections) await upsertConnection(client, connection);
for (const binding of bindings) await upsertBinding(client, binding);
for (const route of routes) await upsertRoute(client, route);
});
return { connections: connections.length, bindings: bindings.length, routes: routes.length };
}
/**
* Apply one reconciliation plan atomically.
*
* Ordering inside the transaction is load-bearing: connections before the
* bindings that reference them, bindings before their routes, and route
* REMOVALS before anything else so a deleted provider's row can never collide
* with a re-import of the same id.
*
* Conflicts are deliberately not applied — a route whose file value matches
* neither snapshot keeps its `pending` row untouched, which is exactly what
* marks its binding blocked on the next read.
*/
export async function applyReconciliation(plan) {
await withTransaction(async (client) => {
if (plan.removals.length > 0) {
await client.query('DELETE FROM ai_route_bindings WHERE provider_id = ANY($1::text[])', [plan.removals]);
}
for (const regroup of plan.regroups) {
if (regroup.connectionAction !== 'unchanged') await upsertConnection(client, regroup.connection);
const bindingId = regroup.binding?.id ?? regroup.bindingId;
if (regroup.binding) {
await upsertBinding(client, { ...regroup.binding, connectionId: regroup.connection.id });
await client.query(
'UPDATE ai_route_bindings SET binding_id = $1, updated_at = NOW() WHERE provider_id = ANY($2::text[])',
[bindingId, regroup.routeIds],
);
} else if (regroup.connectionAction === 'clone') {
await client.query(
'UPDATE ai_harness_bindings SET connection_id = $1, revision = revision + 1, updated_at = NOW() WHERE id = $2',
[regroup.connection.id, bindingId],
);
}
}
for (const { providerId, owned } of plan.snapshots) {
await client.query(
'UPDATE ai_route_bindings SET projected = $1, updated_at = NOW() WHERE provider_id = $2',
[toPgJson(owned), providerId],
);
}
for (const { providerId } of plan.acknowledgements) {
await client.query(
`UPDATE ai_route_bindings
SET projected = pending, pending = NULL, pending_revision = NULL, updated_at = NOW()
WHERE provider_id = $1`,
[providerId],
);
}
for (const connection of plan.imports.connections) await upsertConnection(client, connection);
for (const binding of plan.imports.bindings) await upsertBinding(client, binding);
for (const route of plan.imports.routes) await upsertRoute(client, route);
});
}
/**
* Stage a projection: bump the named rows' revisions and record the snapshot
* each route is ABOUT to receive, in one transaction. The file write happens
* after this returns; {@link acknowledgeProjection} closes the loop.
*/
export async function commitPendingProjection(projections) {
if (projections.length === 0) return;
await withTransaction(async (client) => {
for (const { providerId, owned } of projections) {
const { rows } = await client.query(
`UPDATE ai_route_bindings SET pending = $1, updated_at = NOW() WHERE provider_id = $2
RETURNING binding_id`,
[toPgJson(owned), providerId],
);
if (rows.length === 0) continue;
const { rows: bumped } = await client.query(
'UPDATE ai_harness_bindings SET revision = revision + 1, updated_at = NOW() WHERE id = $1 RETURNING revision',
[rows[0].binding_id],
);
await client.query('UPDATE ai_route_bindings SET pending_revision = $1 WHERE provider_id = $2',
[bumped[0]?.revision ?? null, providerId]);
}
});
}
/** Promote every named route's pending snapshot to `projected`. */
export async function acknowledgeProjection(providerIds) {
if (providerIds.length === 0) return;
await query(
`UPDATE ai_route_bindings
SET projected = COALESCE(pending, projected), pending = NULL, pending_revision = NULL, updated_at = NOW()
WHERE provider_id = ANY($1::text[])`,
[providerIds],
);
}
/** Repoint one binding at another connection. Revision-checked by the caller. */
export async function relinkBinding({ bindingId, connectionId }) {
await withTransaction(async (client) => {
await client.query(
'UPDATE ai_harness_bindings SET connection_id = $1, revision = revision + 1, updated_at = NOW() WHERE id = $2',
[connectionId, bindingId],
);
await client.query('UPDATE ai_connections SET revision = revision + 1, updated_at = NOW() WHERE id = $1',
[connectionId]);
});
}
/** Attach a binding to a freshly cloned connection (the unlink half). */
export async function detachBindingToConnection({ bindingId, connection }) {
await withTransaction(async (client) => {
await upsertConnection(client, connection);
await client.query(
'UPDATE ai_harness_bindings SET connection_id = $1, revision = revision + 1, updated_at = NOW() WHERE id = $2',
[connection.id, bindingId],
);
});
}
/**
* Fold duplicate service instances into one (`planServiceInstanceMerges`):
* write the survivor, move every binding off the folded rows onto it (each on
* the variant key the plan freed), then delete the folded rows — all or
* nothing, so a crash never leaves a binding on a deleted row. The delete is
* guarded by the same "no binding names it" rule as {@link deleteConnection}.
*/
export async function mergeServiceInstances({ keeper, absorbedIds, bindingMoves }) {
await withTransaction(async (client) => {
await upsertConnection(client, keeper);
for (const { bindingId, variantKey } of bindingMoves) {
await client.query(
`UPDATE ai_harness_bindings SET connection_id = $1, variant_key = $2, revision = revision + 1, updated_at = NOW()
WHERE id = $3`,
[keeper.id, variantKey, bindingId],
);
}
await client.query(
`DELETE FROM ai_connections WHERE id = ANY($1::uuid[])
AND NOT EXISTS (SELECT 1 FROM ai_harness_bindings WHERE connection_id = ai_connections.id)`,
[absorbedIds],
);
});
}
/** Hard-delete an orphan connection. Refused while a binding still names it. */
export async function deleteConnection(connectionId) {
const { rows } = await query('SELECT 1 FROM ai_harness_bindings WHERE connection_id = $1 LIMIT 1', [connectionId]);
if (rows.length > 0) return { deleted: false, reason: 'referenced-by-binding' };
await query('DELETE FROM ai_connections WHERE id = $1', [connectionId]);
return { deleted: true };
}
// --- explicit management mutations (#6369) -----------------------------------
// Every function below is revision-checked and serialized by `providerGraph.js`
// BEFORE it is called. They write a whole row rather than a computed SET list:
// the caller already read the row it is replacing, so a partial-column update
// would only add a second place for the merge rules to live.
/**
* Write a connection's user-editable settings and bump its revision.
*
* `catalog` is written here too because a refresh is a connection-level edit,
* not a per-route one — see `refreshConnectionCatalog`.
*
* @returns {Promise<number|null>} the new revision, or `null` if the row is gone
*/
export async function saveConnectionSettings({ id, label, transports, credentials, catalog, plan, enabled, credentialVia }) {
const { rows } = await query(
`UPDATE ai_connections
SET label = $2, transports = $3, credentials = $4, catalog = $5,
plan = COALESCE($6, plan), enabled = COALESCE($7, enabled),
credential_via = COALESCE($8, credential_via),
revision = revision + 1, updated_at = NOW()
WHERE id = $1
RETURNING revision`,
[id, label ?? '', toPgJson(transports || {}), toPgJson(credentials || {}),
toPgJson(catalog || { state: 'unknown', models: [] }),
plan ?? null, typeof enabled === 'boolean' ? enabled : null, credentialVia ?? null],
);
return rows[0]?.revision ?? null;
}
/**
* Fill the service-instance columns on rows that have none (#7563) — the
* boot-time backfill planned by `planServiceColumnBackfill`. No revision bump:
* nothing a human edited changed, and a stale-revision 409 on the first read
* after upgrade would be a lie.
*/
export async function applyServiceColumnBackfill(entries) {
if (entries.length === 0) return;
await withTransaction(async (client) => {
for (const { id, slug, definitionId, plan } of entries) {
await client.query(
`UPDATE ai_connections SET slug = $2, definition_id = $3, plan = $4, updated_at = NOW()
WHERE id = $1 AND slug IS NULL`,
[id, slug, definitionId, plan],
);
}
});
}
/**
* Write a binding's management settings and bump its revision.
*
* Deliberately NOT `enabled`: route enablement is an executable-record field
* that `PATCH /api/providers/:id` owns, and projecting a binding-level toggle
* into it would turn a management edit into an execution consent grant.
*
* @returns {Promise<number|null>} the new revision, or `null` if the row is gone
*/
export async function saveBindingSettings({ id, label, selectedModels }) {
const { rows } = await query(
`UPDATE ai_harness_bindings
SET label = $2, selected_models = $3, revision = revision + 1, updated_at = NOW()
WHERE id = $1
RETURNING revision`,
[id, label ?? '', toPgJson(selectedModels || [])],
);
return rows[0]?.revision ?? null;
}
/** Record the canonical→executable model aliases a refresh observed on a route. */
export async function saveRouteModelMap(providerId, modelMap) {
await query('UPDATE ai_route_bindings SET model_map = $2, updated_at = NOW() WHERE provider_id = $1',
[providerId, toPgJson(modelMap || {})]);
}
/**
* Write one route's hand-authored alias overrides. Revision-checked and
* serialized by the caller, like every other mutation in this block.
*
* Separate from {@link saveRouteModelMap} on purpose: that column records what
* a refresh OBSERVED and is rewritten by the next one, so keeping the two apart
* is what lets a correction survive a refresh (#6369).
*/
export async function saveRouteModelAliases(providerId, overrides) {
await query(
'UPDATE ai_route_bindings SET model_alias_overrides = $2, updated_at = NOW() WHERE provider_id = $1',
[providerId, toPgJson(overrides || {})],
);
}