Skip to content

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

16.1 KB
Raw Blame
/**
 * Change-of-address inventory workflow (issue #2143, epic #2138).
 *
 * db-primary Postgres per docs/STORAGE.md: one row per declared change in
 * `privacy_change_events`. The workflow is: the user declares "field X changed
 * from A to B" — the OLD vault record is marked `previous` (+ `valid_to`), a
 * replacement record is created/linked, and EVERY `current` holding of the old
 * record flips to `update_pending`. The Changes tab then works that per-org
 * checklist to done (zero `update_pending`).
 *
 * Machine-local: NO federation, NO tombstones — NEVER federated, by decision
 * rather than deferral (ADR
 * docs/decisions/2026-08-08-privacy-records-machine-local.md, #2148). Plaintext PII is NEVER stored here and NEVER logged — the
 * inventory joins masked vault values only. The ONE place plaintext surfaces is
 * `draftUpdateEmail`, which decrypts the NEW value into an email draft the user
 * explicitly requested and must still approve before it sends (never auto-send
 * in this phase).
 *
 * The declare path runs as ONE transaction (atomic): a partial failure must not
 * leave the old record `previous` with holdings un-flipped, or an orphaned
 * replacement record. It uses the transaction client for every write rather
 * than the pool-level `privacyOrgs.setHoldingsStatus`, so the flip both sees the
 * FOR UPDATE lock and rolls back with the rest on error.
 */

import { randomUUID } from 'crypto';
import { query, withTransaction } from '../lib/db.js';
import { ServerError } from '../lib/errorHandler.js';
import { encryptValue, maskValue, ensureVaultKey } from '../lib/vaultCrypto.js';
import { resolveUseForScans, getVaultRecord, revealValue } from './privacyVault.js';
import { getOrg } from './privacyOrgs.js';
import { resolveSubjectId } from './privacySubjects.js';
import { listAccounts } from './messageAccounts.js';
import { createDraft } from './messageDrafts.js';
import { pickEmailSenderAccount, sendViaForAccountType } from '../lib/messageTransport.js';

const EVENT_COLUMNS = `id, subject_id, vault_record_id, replacement_record_id, kind, declared_at, note`;

// Holdings statuses that make up the per-event inventory (a `current` holding is
// only relevant to a NEW record, not the change checklist).
const INVENTORY_STATUSES = ['update_pending', 'updated', 'removed'];

// Human-facing field label per change kind — used in the update-email template.
const KIND_FIELD_LABEL = Object.freeze({
  address_change: 'mailing address',
  phone_change: 'phone number',
  email_change: 'email address',
  name_change: 'legal name',
  other: 'information on file',
});

// Default change kind derived from the OLD record's vault type when the caller
// omits `kind` (the UI derives it from the selected record, but the API stays
// forgiving). Anything without a dedicated kind is a generic `other`.
const TYPE_TO_KIND = Object.freeze({
  address: 'address_change',
  phone: 'phone_change',
  email: 'email_change',
  legal_name: 'name_change',
});

export function kindForType(type) {
  return TYPE_TO_KIND[type] ?? 'other';
}

function rowToEvent(row) {
  if (!row) return null;
  return {
    id: row.id,
    subjectId: row.subject_id,
    vaultRecordId: row.vault_record_id,
    replacementRecordId: row.replacement_record_id,
    kind: row.kind,
    declaredAt: row.declared_at,
    note: row.note,
  };
}

/** Pure: assemble the update-email body from old→new (masked old, plain new). */
export function renderUpdateEmailBody({ orgName, fieldLabel, oldMasked, newValue }) {
  const lines = [
    `Hello${orgName ? ` ${orgName}` : ''},`,
    '',
    `I am writing to update my ${fieldLabel} on file with your organization.`,
    '',
    `New ${fieldLabel}: ${newValue}`,
  ];
  if (oldMasked) lines.push(`Previous ${fieldLabel} on record: ${oldMasked}`);
  lines.push(
    '',
    'Please update your records accordingly and confirm once the change has been applied.',
    '',
    'Thank you.',
  );
  return lines.join('\n');
}

/**
 * Declare a change: mark the old record `previous`, create/link the replacement,
 * flip every `current` holding of the old record to `update_pending` (recording
 * a forward-looking `current` holding on the new record for each flipped org so
 * "org has old value" and "org needs new value" are both queryable), and write
 * the event row. All in ONE transaction.
 */
export async function declareChange({ vaultRecordId, replacement, replacementRecordId, kind, note }) {
  // ensureVaultKey may append to .env — do it before opening the transaction
  // so the transaction body is pure DB work (a replacement is encrypted inside).
  if (replacement) await ensureVaultKey();
  return withTransaction(async (client) => {
    // The event inherits its subject from the OLD record — a change is always
    // "this person's fact changed", so there is no subjectId on the input.
    const { rows: oldRows } = await client.query(
      `SELECT id, type, subject_id FROM privacy_vault_records WHERE id = $1 FOR UPDATE`, [vaultRecordId],
    );
    const oldRecord = oldRows[0];
    if (!oldRecord) throw new ServerError('Vault record not found', { status: 404, code: 'NOT_FOUND' });

    // Resolve the replacement record id (inline-create, link-existing, or none).
    let replacementId = null;
    if (replacementRecordId) {
      const { rows } = await client.query(
        `SELECT id, subject_id FROM privacy_vault_records WHERE id = $1`, [replacementRecordId],
      );
      if (!rows[0]) {
        throw new ServerError('Replacement vault record not found', { status: 400, code: 'REPLACEMENT_NOT_FOUND' });
      }
      // Retiring one person's address in favour of ANOTHER person's record is
      // never a legitimate change — it would silently re-point their orgs.
      if (rows[0].subject_id !== oldRecord.subject_id) {
        throw new ServerError(
          'Replacement vault record belongs to a different privacy subject',
          { status: 400, code: 'SUBJECT_MISMATCH' },
        );
      }
      replacementId = replacementRecordId;
    } else if (replacement) {
      replacementId = randomUUID();
      const type = replacement.type ?? oldRecord.type;
      const useForScans = resolveUseForScans(type, replacement.useForScans);
      await client.query(
        `INSERT INTO privacy_vault_records
           (id, subject_id, type, label, value_enc, masked_value, status, valid_from, valid_to,
            share_with_twin, use_for_scans, notes, created_at, updated_at)
         VALUES ($1, $2, $3, $4, $5, $6, 'current', $7, NULL, $8, $9, $10, NOW(), NOW())`,
        [
          replacementId, oldRecord.subject_id, type, replacement.label,
          encryptValue(replacement.value), maskValue(type, replacement.value),
          replacement.validFrom ?? null,
          replacement.shareWithTwin === true, useForScans, replacement.notes ?? '',
        ],
      );
    }

    // Retire the old record. COALESCE keeps an already-set valid_to intact.
    await client.query(
      `UPDATE privacy_vault_records
       SET status = 'previous', valid_to = COALESCE(valid_to, CURRENT_DATE), updated_at = NOW()
       WHERE id = $1`,
      [vaultRecordId],
    );

    // Flip every active holding of the old record → update_pending, capturing
    // the affected orgs so we can mirror a forward-looking holding on the new one.
    const { rows: flipped } = await client.query(
      `UPDATE privacy_org_holdings SET status = 'update_pending', updated_at = NOW()
       WHERE vault_record_id = $1 AND status = ANY(ARRAY['current', 'unknown', 'update_pending'])
       RETURNING org_id`,
      [vaultRecordId],
    );
    if (replacementId) {
      for (const row of flipped) {
        await client.query(
          `INSERT INTO privacy_org_holdings (org_id, subject_id, vault_record_id, status, noted_at, updated_at)
           VALUES ($1, $2, $3, 'current', NOW(), NOW())
           ON CONFLICT (org_id, vault_record_id) DO UPDATE SET status = 'current', updated_at = NOW()`,
          [row.org_id, oldRecord.subject_id, replacementId],
        );
      }
    }

    const eventId = randomUUID();
    const resolvedKind = kind ?? kindForType(oldRecord.type);
    const { rows: evRows } = await client.query(
      `INSERT INTO privacy_change_events (id, subject_id, vault_record_id, replacement_record_id, kind, declared_at, note)
       VALUES ($1, $2, $3, $4, $5, NOW(), $6)
       RETURNING ${EVENT_COLUMNS}`,
      [eventId, oldRecord.subject_id, vaultRecordId, replacementId, resolvedKind, note ?? ''],
    );
    console.log(`📮 Declared privacy change ${eventId} (kind=${resolvedKind}, orgs flipped=${flipped.length}, replacement=${replacementId ? 'yes' : 'none'})`);
    return rowToEvent(evRows[0]);
  });
}

/** List all change events, newest first, with per-event progress counts + masked old/new values. */
export async function listChangeEvents({ subjectId } = {}) {
  const { rows } = await query(
    `SELECT e.id, e.subject_id, e.vault_record_id, e.replacement_record_id, e.kind, e.declared_at, e.note,
            ov.type AS old_type, ov.label AS old_label, ov.masked_value AS old_masked,
            rv.type AS new_type, rv.label AS new_label, rv.masked_value AS new_masked,
            COUNT(h.org_id) FILTER (WHERE h.status = 'update_pending')::int AS pending_count,
            COUNT(h.org_id) FILTER (WHERE h.status = 'updated')::int AS updated_count,
            COUNT(h.org_id) FILTER (WHERE h.status = 'removed')::int AS removed_count
     FROM privacy_change_events e
     JOIN privacy_vault_records ov ON ov.id = e.vault_record_id
     LEFT JOIN privacy_vault_records rv ON rv.id = e.replacement_record_id
     LEFT JOIN privacy_org_holdings h
       ON h.vault_record_id = e.vault_record_id AND h.status = ANY($1::text[])
     WHERE e.subject_id = $2
     GROUP BY e.id, ov.type, ov.label, ov.masked_value, rv.type, rv.label, rv.masked_value
     ORDER BY e.declared_at DESC`,
    [INVENTORY_STATUSES, resolveSubjectId(subjectId)],
  );
  return rows.map((row) => ({
    ...rowToEvent(row),
    oldRecord: { type: row.old_type, label: row.old_label, maskedValue: row.old_masked },
    replacementRecord: row.replacement_record_id
      ? { type: row.new_type, label: row.new_label, maskedValue: row.new_masked }
      : null,
    progress: {
      pending: row.pending_count,
      updated: row.updated_count,
      removed: row.removed_count,
      total: row.pending_count + row.updated_count + row.removed_count,
    },
  }));
}

export async function getChangeEvent(eventId) {
  const { rows } = await query(`SELECT ${EVENT_COLUMNS} FROM privacy_change_events WHERE id = $1`, [eventId]);
  return rowToEvent(rows[0]);
}

async function requireEvent(eventId) {
  const event = await getChangeEvent(eventId);
  if (!event) throw new ServerError('Change event not found', { status: 404, code: 'NOT_FOUND' });
  return event;
}

/**
 * Per-org inventory for a change event, grouped by holding status of the OLD
 * record: `{ pending[], updated[], removed[] }`. Each entry carries the org's
 * name, website, and contact email (for the per-org actions + draft-email).
 */
export async function getChangeProgress(eventId) {
  const event = await requireEvent(eventId);
  const { rows } = await query(
    `SELECT h.org_id, h.status, o.name AS org_name, o.website, o.contact
     FROM privacy_org_holdings h
     JOIN privacy_orgs o ON o.id = h.org_id
     WHERE h.vault_record_id = $1 AND h.status = ANY($2::text[])
     ORDER BY o.name`,
    [event.vaultRecordId, INVENTORY_STATUSES],
  );
  const groups = { pending: [], updated: [], removed: [] };
  for (const r of rows) {
    const entry = {
      orgId: r.org_id,
      orgName: r.org_name,
      website: r.website || null,
      contactEmail: r.contact?.email || null,
    };
    if (r.status === 'update_pending') groups.pending.push(entry);
    else if (r.status === 'updated') groups.updated.push(entry);
    else if (r.status === 'removed') groups.removed.push(entry);
  }
  return groups;
}

/** Event + inventory in one payload — powers GET /changes/:id (event detail). */
export async function getChange(eventId) {
  const event = await requireEvent(eventId);
  const [oldRecord, replacementRecord] = await Promise.all([
    getVaultRecord(event.vaultRecordId),
    event.replacementRecordId ? getVaultRecord(event.replacementRecordId) : Promise.resolve(null),
  ]);
  const progress = await getChangeProgress(eventId);
  return { event, oldRecord, replacementRecord, progress };
}

// Flip ONE org's old-record holding to a terminal inventory status. Idempotent:
// a holding already in an inventory status re-settles to the requested one, so a
// double-click is a no-op rather than a 404.
async function setOrgHoldingStatus(event, orgId, toStatus) {
  const { rows } = await query(
    `UPDATE privacy_org_holdings SET status = $1, updated_at = NOW()
     WHERE vault_record_id = $2 AND org_id = $3 AND status = ANY($4::text[])
     RETURNING org_id`,
    [toStatus, event.vaultRecordId, orgId, INVENTORY_STATUSES],
  );
  if (!rows[0]) {
    throw new ServerError('Organization is not part of this change', { status: 404, code: 'HOLDING_NOT_FOUND' });
  }
}

export async function markOrgUpdated(eventId, orgId) {
  const event = await requireEvent(eventId);
  await setOrgHoldingStatus(event, orgId, 'updated');
  console.log(`✅ Change ${eventId}: org ${orgId} marked updated`);
  return getChangeProgress(eventId);
}

export async function markOrgRemoved(eventId, orgId) {
  const event = await requireEvent(eventId);
  await withTransaction(async (client) => {
    const { rows } = await client.query(
      `UPDATE privacy_org_holdings SET status = 'removed', updated_at = NOW()
       WHERE vault_record_id = $1 AND org_id = $2 AND status = ANY($3::text[])
       RETURNING org_id`,
      [event.vaultRecordId, orgId, INVENTORY_STATUSES],
    );
    if (!rows[0]) {
      throw new ServerError('Organization is not part of this change', { status: 404, code: 'HOLDING_NOT_FOUND' });
    }
    // The org dropped the field entirely — retire the forward-looking holding we
    // created on the replacement record at declare time so the Organizations tab
    // stays honest (this org does not hold the new value either).
    if (event.replacementRecordId) {
      await client.query(
        `DELETE FROM privacy_org_holdings WHERE vault_record_id = $1 AND org_id = $2`,
        [event.replacementRecordId, orgId],
      );
    }
  });
  console.log(`🚫 Change ${eventId}: org ${orgId} marked removed`);
  return getChangeProgress(eventId);
}

/**
 * Draft a plain "please update my records" email for one org in the inventory,
 * old→new, into the messages drafts queue as an UNAPPROVED draft (status
 * `draft`). Template-only (no LLM), runs only on an explicit user click. The
 * new value is decrypted for the body (the org needs the actual new address);
 * the draft still requires user approval before it can send.
 */
export async function draftUpdateEmail(eventId, orgId) {
  const event = await requireEvent(eventId);
  if (!event.replacementRecordId) {
    throw new ServerError('No replacement record to update to', { status: 400, code: 'REPLACEMENT_REQUIRED' });
  }
  const org = await getOrg(orgId);
  if (!org) throw new ServerError('Organization not found', { status: 404, code: 'NOT_FOUND' });
  const to = org.contact?.email;
  if (!to) throw new ServerError('Organization has no contact email', { status: 400, code: 'ORG_EMAIL_MISSING' });

  const accounts = await listAccounts();
  const account = pickEmailSenderAccount(accounts);
  if (!account) throw new ServerError('Connect a Gmail or Outlook account to send update emails', { status: 400, code: 'NO_MESSAGE_ACCOUNT' });

  const oldRecord = await getVaultRecord(event.vaultRecordId);
  const newReveal = await revealValue(event.replacementRecordId);
  const fieldLabel = KIND_FIELD_LABEL[event.kind] ?? KIND_FIELD_LABEL.other;
  const subject = `Request to update my ${fieldLabel}`;
  const body = renderUpdateEmailBody({
    orgName: org.name,
    fieldLabel,
    oldMasked: oldRecord?.maskedValue,
    newValue: newReveal.value,
  });

  const draft = await createDraft({
    accountId: account.id,
    to: [to],
    subject,
    body,
    generatedBy: 'privacy-change',
    sendVia: sendViaForAccountType(account.type),
  });
  console.log(`📧 Drafted privacy update email for org ${orgId} (change ${eventId}, draft ${draft.id})`);
  return { draftId: draft.id, status: draft.status };
}