new row violates check constraint on documents.source

The short version

The documents table’s source column had a CHECK constraint, documents_source_check, allowing only 'upload' and 'dropbox'. But the automated document-archiving feature inserts quote/invoice/application/receipt snapshots with source: 'system', and the Anyflow import feature inserts signed contracts with source: 'anyflow'. Neither value was allowed.

The result: the file upload to storage succeeded every single time, while the insert into the documents table failed every single time, with new row for relation "documents" violates check constraint "documents_source_check". Not one row of the automated document vault had ever been successfully created.

A failure designed to be swallowed silently

upsertDocument in document-archive.ts uploads the file to storage first, then upserts the documents row:

async function upsertDocument(
  admin: AdminClient,
  opts: { /* ... */ source: "system" | "anyflow"; /* ... */ }
): Promise<ArchiveResult> {
  const { error: upErr } = await admin.storage
    .from(BUCKET)
    .upload(opts.storagePath, opts.bytes, { contentType: opts.mimeType, upsert: true });
  if (upErr) return { ok: false, reason: `storage: ${upErr.message}` };

  // ...check for an existing row by external_ref...

  const { data: inserted, error } = await admin
    .from("documents")
    .insert({ ...fields, created_by: null })
    .select("id")
    .single();
  if (error) {
    // 23505 (uq_documents_external race) is handled separately above
    // ...
    return { ok: false, reason: `insert: ${error.message}` };
  }
  return { ok: true, documentId: inserted.id };
}

A CHECK violation (Postgres error code 23514) doesn’t match the 23505 unique-violation race handled just above it, so it falls straight through to { ok: false, reason: "insert: new row for relation \"documents\" violates check constraint \"documents_source_check\"" }.

The function that calls this, archiveContractDocument, has a comment stating the intent directly:

/**
 * Automatically archives generated documents (quote/invoice/application/receipt)
 * to the document vault.
 * ...
 * - Callers (the signature webhook, invoice issuance) must not break, so every
 *   failure — including exceptions — is returned as an ArchiveResult instead of thrown.
 * ...
 */

archiveExternalDocument, which handles external imports, carries the same stated intent:

/**
 * Archives a signed file imported from outside (e.g. an Anyflow-signed contract)
 * to the document vault.
 * ...
 * - Failures are returned as an ArchiveResult so the caller (the webhook) is not broken.
 */

This was not an accident — it was the intended design. A document-archiving failure should never block the main flow, such as a payment-state transition or signature confirmation. Choosing to return a result object instead of throwing is reasonable on its own. The problem is that nothing downstream was actually reading that ArchiveResult.

Nobody was checking .ok

invoice-issue.ts looked like this:

// Auto-archive the invoice snapshot to the document vault.
// Runs before checking if the email can be sent, so the issued document is
// kept even without a registered email. Idempotent — failures are ignored.
const archived = await archiveContractDocument({ contractId, kind: "invoice" });

// ...resolve recipient...

const sent = await sendEmail({
  // ...
  text: [
    // ...
    "pdf" in archived && archived.pdf != null
      ? "The invoice (PDF) is attached to this email. ..."
      : "You can view and save the invoice as a PDF here (no login required):",
    // ...
  ].join("\n"),
  ...("pdf" in archived && archived.pdf != null
    ? { attachments: [{ filename: "invoice.pdf", content: archived.pdf }] }
    : {}),
});

Nowhere in the file is archived.ok ever read. Only archived.pdf (the PDF bytes for the email attachment) is used. Since dryRun isn’t set, archiveContractDocument does attempt the real storage and DB writes, but PDF generation itself doesn’t depend on whether the documents insert succeeded — so the email-sending path has no way to notice the archive is failing. Invoices kept issuing and sending normally while the document vault stayed empty underneath them.

The signing flow in contract-signed.ts has the same pattern, and says so in its own comment:

// 2b. Auto-archive the application snapshot to the document vault (ops spec §1).
//     A signature is frozen as proof of consent. Non-kimiteras contract types
//     are skipped inside archiveContractDocument. Idempotent — failures are swallowed.
try {
  const r = await archiveContractDocument({ contractId, kind: "application" });
  if (!r.ok) {
    console.error("[contract-signed] application archive failed:", r.reason);
  } else if (!r.skipped) {
    // ...signature evidence-anchor processing...
  }
}

This path does log the failure with console.error, but takes no further action. Without active log monitoring, this line filling up with CHECK violations never reaches anyone.

The fix was just widening the constraint

The fix, in migration 0074, only added 'system' and 'anyflow' to documents_source_check’s allowed values:

alter table public.documents drop constraint if exists documents_source_check;
alter table public.documents add constraint documents_source_check
  check (source = any (array['upload'::text, 'dropbox'::text, 'system'::text, 'anyflow'::text]));

It’s an additive, idempotent migration (drop constraint if exists then add constraint); since every existing row used 'upload', they all still satisfy the new constraint. No application code changed — upsertDocument and invoice-issue.ts were already correct code that only needed the constraint to let their rows through.

The hidden cost of “don’t break the main flow”

Swallowing failures silently so the main flow never breaks is a reasonable design choice on its own. In fact, the payment-transition and signature-confirmation flows never stopped once, even while the CHECK violation went unfixed. The cost is that the assumption “the archive must be succeeding” accumulated for months with nothing ever verifying it. Multiple callers that never check archived.ok can’t be caught by console.error logging alone — in practice, what surfaced this was literally counting the rows in the document vault. Choosing to swallow failures silently only works if the swallowed outcome (the count of ok: false results) is made visible somewhere else.