Database and the write-only audit log

The API needs PostgreSQL. Which version and where it runs (managed service or your own server) is up to you; the schema uses the built-in gen_random_uuid() and sha256() functions, so you need PostgreSQL 13 or newer. (Development and tests use an embedded PostgreSQL 18.)

What’s stored

Where	What
Core tables	Organizations, users (password hashes for owners and super admins), sessions, one-time email tokens (only their hashes), processed Stripe event IDs, signing requests, recipients and fields
envelope_files	The PDF of every document sent for signature, and the signed PDF once complete, stored in the database
audit.events	The activity log

Files opened from or saved to OneDrive and Teams are not stored. Nothing is deleted automatically: signing requests and their PDFs are kept until you remove them.

Migrations

Migrations are plain SQL files in apps/api/migrations/core (application tables) and apps/api/migrations/audit (the activity log). Applied migrations are recorded in a schema_migrations table in each database.

npm run migrate -w @pdfeditor/api

• Development: the API applies pending migrations every time it starts.
• Production: run npm run migrate as a release step, or set MIGRATE_ON_START=true.

Migrations with insert-only audit credentials

The insert-only audit_writer role can’t run migrations. With the hardened setup below, also set AUDIT_ADMIN_DATABASE_URL to an owner connection for the audit database; npm run migrate, MIGRATE_ON_START and create-super-admin all use it for the audit migrations.

Hardening the audit log

By default the activity log is written through the main connection. For production, give the API credentials that can only insert into the log, and only read from it.

1. Decide where the log lives: in the main database (simplest), or in a separate PostgreSQL database for stronger separation.

2. Run the migrations as the owner of each database. If the audit log is separate:

   AUDIT_DATABASE_URL=postgres://owner:...@audit-db/pdfeditor_audit \
     npm run migrate -w @pdfeditor/api

3. As the owner of the audit database, run apps/api/sql/audit-roles.sql after replacing its placeholder passwords. It creates:

   • audit_writer: may only INSERT into audit.events (and use its sequence),
   • audit_reader: may only SELECT from it and run the verification functions,

   and revokes everything else on the audit schema from PUBLIC.

   psql "postgres://owner:...@audit-db/pdfeditor_audit" -f apps/api/sql/audit-roles.sql

4. Configure the API:

   AUDIT_DATABASE_URL=postgres://audit_writer:...@audit-db/pdfeditor_audit
   AUDIT_READ_DATABASE_URL=postgres://audit_reader:...@audit-db/pdfeditor_audit

5. For future migrations, run npm run migrate with AUDIT_ADMIN_DATABASE_URL set to an owner connection for the audit database.

How the write-only log works

• Append-only by trigger. UPDATE, DELETE and TRUNCATE on audit.events raise audit.events is append-only, whoever runs them, as long as the triggers are in place.
• Hash chain. A BEFORE INSERT trigger takes an advisory lock, reads the previous row’s hash, and sets the row’s id, timestamp (clock_timestamp()), prev_hash and hash. The hash is SHA-256 over the previous hash and all the row’s fields, so the chain order is the commit order even with concurrent writers. The first row links to 64 zeros. The trigger is SECURITY DEFINER, so the insert-only role can chain rows without being able to read the table.
• Values set by the database. The API can’t choose an event’s ID, time or hashes; the trigger overwrites them.
• Verification. audit.verify_chain() recomputes every row’s hash and compares each prev_hash with the previous row, returning rows that fail as hash mismatch (row was modified) or chain broken (row missing or reordered). Super admins run it with Verify integrity, or POST /api/admin/audit/verify. See Audit-log integrity.

What the chain does and doesn't protect against

A database superuser or the schema owner can disable the triggers and rewrite rows; the chain makes that detectable, unless the attacker also recomputes every later hash. Keep owner and superuser credentials away from the application, and keep backups (or an export) of the log somewhere the database owner can’t change, so a recomputed chain can be compared with a known-good copy.

Audit writes are best effort

If writing an event fails (for example the audit database is down), the user’s action still succeeds and the API logs audit log write failed at error level. Alert on that log line.

Backups

Back up the main database (it holds every document sent for signature) and the audit database, if separate. The backup method and schedule are your decision; test restores. SESSION_SECRET should be kept with your secrets, not in backups of the database.