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
/**
* Memory Federation Sync Service
*
* Enables memory synchronization between PortOS instances via the
* commit-ordered federation change feed (`sync_feed`, #8315). Peers pull
* changes since their last known feed position.
*
* Sync Protocol:
* 1. Every committed memory change gets a feed position, drawn AT COMMIT
* (server/lib/db/schema/syncFeed.js) — never the write-time
* sync_sequence, which a late-committing transaction could land below a
* cursor a peer already advanced past
* 2. Peers request GET /api/memory/sync?since={lastPosition}&limit=100
* 3. Response includes memories changed since that position + the max position
* 4. Peer stores the max position and uses it for the next poll. A legacy
* sync_sequence cursor is below every position, so it replays the stream
* 5. Conflict resolution: last-writer-wins by updated_at timestamp
*
* Note: memory_links (relationships) are not synced — only the memories table
* is replicated. Relationship data is instance-local.
*/
import { PORTOS_SCHEMA_VERSIONS } from '../lib/schemaVersions.js';
import { ServerError } from '../lib/errorHandler.js';
import { query, withTransaction, arrayToPgvector, pgvectorToArray } from '../lib/db.js';
import { PERSISTENT_MIND_ID } from '../lib/persistentMindTrajectory.js';
import { PERSISTENT_MIND_CHOSEN_NAME_TAG } from '../lib/persistentMindChosenName.js';
import { dedupeByKey } from '../lib/arrayUtils.js';
/**
* Get memories changed since a given feed position.
* Used by peers to pull incremental updates.
*
* @param {string} sinceSequence - Return changes after this feed position (string to avoid BigInt precision loss)
* @param {number} limit - Max records to return per batch
* @returns {Promise<{memories: Array, maxSequence: string, hasMore: boolean}>}
*/
export async function getChangesSince(sinceSequence = '0', limit = 100, schemaVersion = 0) {
if (schemaVersion > PORTOS_SCHEMA_VERSIONS.memoryHistory) {
throw new ServerError('Unsupported memory schema version', { status: 409 });
}
// Fetch limit+1 rows to detect whether more records exist beyond this batch
const result = await query(
`SELECT m.id, m.version, m.type, m.content, m.summary, m.category, m.tags,
m.embedding, m.embedding_model, m.confidence, m.importance,
m.status, m.source_task_id, m.source_agent_id, m.source_app_id,
m.expires_at, m.created_at, m.updated_at, m.origin_instance_id,
f.position::text AS feed_position
FROM sync_feed f
JOIN memories m ON m.sync_sequence = f.row_sequence
WHERE f.stream = 'memories'
AND f.position > $1
AND m.source_agent_id IS DISTINCT FROM $3
AND NOT ($4 = ANY(COALESCE(m.tags, '{}'::text[])))
ORDER BY f.position ASC
LIMIT $2`,
[sinceSequence, limit + 1, PERSISTENT_MIND_ID, PERSISTENT_MIND_CHOSEN_NAME_TAG]
);
const hasMore = result.rows.length > limit;
const rows = hasMore ? result.rows.slice(0, limit) : result.rows;
// access_count and last_accessed are instance-local read stats,
// not replicated — omitted from sync payload intentionally.
const memories = rows.map(row => ({
id: row.id,
...(schemaVersion === PORTOS_SCHEMA_VERSIONS.memoryHistory ? { version: row.version } : {}),
type: row.type,
content: row.content,
summary: row.summary,
category: row.category,
tags: row.tags || [],
embedding: pgvectorToArray(row.embedding),
embeddingModel: row.embedding_model,
confidence: row.confidence,
importance: row.importance,
status: row.status,
sourceTaskId: row.source_task_id,
sourceAgentId: row.source_agent_id,
sourceAppId: row.source_app_id,
expiresAt: row.expires_at?.toISOString() ?? null,
createdAt: row.created_at.toISOString(),
updatedAt: row.updated_at.toISOString(),
syncSequence: row.feed_position,
originInstanceId: row.origin_instance_id
}));
const maxSequence = memories.length > 0
? memories[memories.length - 1].syncSequence
: sinceSequence;
return { memories, maxSequence, hasMore, ...(schemaVersion ? { schemaVersion } : {}) };
}
/**
* Apply incoming changes from a remote peer.
* Uses last-writer-wins conflict resolution based on updated_at.
* Batches inserts (100 per query) to reduce round-trips and lock time.
*
* @param {Array} incomingMemories - Array of memory objects from remote peer
* @returns {Promise<{inserted: number, updated: number, skipped: number}>}
* inserted - new rows created
* updated - existing rows replaced (remote was newer)
* skipped - rows rejected by last-writer-wins (local was newer)
*/
export async function applyRemoteChanges(incomingMemories, schemaVersion = 0) {
if (!Number.isInteger(schemaVersion) || schemaVersion < 0 || schemaVersion > PORTOS_SCHEMA_VERSIONS.memoryHistory) {
throw new ServerError('Unsupported memory schema version', { status: 409 });
}
// Never import a peer's revision counter into the local history PK. The
// trigger snapshots the local text and increments our counter on replacement.
if (incomingMemories.length === 0) return { inserted: 0, updated: 0, skipped: 0 };
const COLS = 18;
const BATCH_SIZE = 100;
// Collapse duplicate ids BEFORE batching (see `dedupeByKey` for why a
// multi-row upsert cannot carry a repeated conflict key). This runs inside a
// transaction, so one repeated id anywhere in a peer's payload would roll back
// the ENTIRE apply, not just its batch — and the rows arrive from a remote
// peer, so uniqueness is not ours to assume.
//
// The survivor is the newest copy by `updated_at`, not simply the last one:
// that is the winner the ON CONFLICT clause's last-writer-wins rule picks when
// the same duplicates arrive in separate batches, so how a peer happened to
// order its payload can't change the outcome. Ties keep the first copy, matching
// the SQL's strict `>` (an equal clock is not a newer write).
//
// An unparseable clock sorts BELOW every real one rather than NaN-comparing
// false and thereby winning: a peer that sends one good and one malformed copy
// of a row must keep the good one, or the batch carries a timestamp Postgres
// rejects and the apply fails on a row we already had intact.
const lwwClock = (mem) => {
const at = Date.parse(mem?.updatedAt);
return Number.isNaN(at) ? -Infinity : at;
};
const deduped = dedupeByKey(
incomingMemories.filter((mem) => mem.sourceAgentId !== PERSISTENT_MIND_ID
&& !mem.tags?.includes(PERSISTENT_MIND_CHOSEN_NAME_TAG)),
(mem) => mem.id,
(held, next) => (lwwClock(held) >= lwwClock(next) ? held : next),
);
// A collapsed duplicate lost last-writer-wins, which is exactly what `skipped`
// counts — so the three tallies still sum to what the peer sent.
const collapsed = incomingMemories.length - deduped.length;
return withTransaction(async (client) => {
let inserted = 0;
let updated = 0;
let skipped = collapsed;
for (let i = 0; i < deduped.length; i += BATCH_SIZE) {
const batch = deduped.slice(i, i + BATCH_SIZE);
const values = [];
const params = [];
batch.forEach((mem, idx) => {
const base = idx * COLS;
values.push(`(${Array.from({length: COLS}, (_, j) => `$${base + j + 1}`).join(', ')})`);
params.push(
mem.id, mem.type, mem.content, mem.summary, mem.category, mem.tags || [],
arrayToPgvector(mem.embedding), mem.embeddingModel, mem.confidence, mem.importance,
mem.status, mem.sourceTaskId, mem.sourceAgentId, mem.sourceAppId,
mem.expiresAt, mem.createdAt, mem.updatedAt, mem.originInstanceId
);
});
params.push(PERSISTENT_MIND_ID, PERSISTENT_MIND_CHOSEN_NAME_TAG);
const localMindParam = params.length - 1;
const nameTagParam = params.length;
// access_count and last_accessed are instance-local, not synced
const result = await client.query(
`INSERT INTO memories (
id, type, content, summary, category, tags,
embedding, embedding_model, confidence, importance,
status, source_task_id, source_agent_id, source_app_id,
expires_at, created_at, updated_at, origin_instance_id
) VALUES ${values.join(', ')}
ON CONFLICT (id) DO UPDATE SET
type = EXCLUDED.type, content = EXCLUDED.content,
summary = EXCLUDED.summary, category = EXCLUDED.category, tags = EXCLUDED.tags,
embedding = EXCLUDED.embedding, embedding_model = EXCLUDED.embedding_model,
confidence = EXCLUDED.confidence, importance = EXCLUDED.importance,
status = EXCLUDED.status, expires_at = EXCLUDED.expires_at,
updated_at = EXCLUDED.updated_at,
source_task_id = EXCLUDED.source_task_id, source_agent_id = EXCLUDED.source_agent_id,
source_app_id = EXCLUDED.source_app_id,
origin_instance_id = EXCLUDED.origin_instance_id
WHERE EXCLUDED.updated_at > memories.updated_at
AND memories.source_agent_id IS DISTINCT FROM $${localMindParam}
AND NOT ($${nameTagParam} = ANY(COALESCE(memories.tags, '{}'::text[])))
RETURNING (xmax = 0) AS is_insert`,
params
);
inserted += result.rows.filter(r => r.is_insert).length;
updated += result.rows.filter(r => !r.is_insert).length;
skipped += batch.length - result.rows.length;
}
return { inserted, updated, skipped };
});
}
/**
* Get the current maximum feed position.
* Used by peers to determine if they're up-to-date, and to detect a reset
* (a saved cursor above this maximum rewinds to 0).
*
* @returns {Promise<string>} Position as string to avoid BigInt precision loss
*/
export async function getMaxSequence() {
const result = await query("SELECT COALESCE(MAX(position), 0)::text AS max_seq FROM sync_feed WHERE stream = 'memories'");
return result.rows?.[0]?.max_seq ?? '0';
}