What a Postgres migration audit log needs to prove
A useful migration audit log is not just an activity feed. It needs to prove what changed, what risk was found, who approved it, and which policy applied at the time.
Most teams already have some record of schema changes.
There is a pull request. There is a CI run. There is a deploy log. There might be a Slack thread where someone said “looks good.”
That is not the same thing as a migration audit log.
A useful migration audit log needs to answer a stricter question:
For this risky Postgres migration, can we prove what was reviewed, what risk was known, who approved it, and which policy applied at the time?
If the answer is no, you do not have evidence. You have breadcrumbs.
The PR is not enough
GitHub already stores a lot:
- who opened the pull request
- which files changed
- who reviewed it
- when it merged
- which CI checks passed
That is valuable, but it is not migration-specific evidence.
A reviewer approving a PR might be approving application code, SQL, generated migration files, config, tests, or all of it at once. The approval does not say which Postgres lock modes were found, whether unknown SQL existed, whether lock_timeout was missing, or whether the risky statement changed after review.
For normal code review, that may be fine. For production schema changes, it leaves gaps.
The audit log needs the analysis result
The first thing to record is the analyzer output that existed at review time:
- migration files analyzed
- statements analyzed
- dynamic or unknown statements
- findings
- highest risk level
- lock mode for each risky statement
- policy violations
- safe rewrite guidance shown to the reviewer
This matters because “approved” is only meaningful if you know what the reviewer saw.
If a migration had an ACCESS EXCLUSIVE finding and the reviewer approved it, that is one kind of decision. If pgfence could not analyze a dynamic statement and the reviewer accepted that unknown risk, that is a different decision. Both need to be visible.
The audit log needs a content hash
Commit SHAs are not enough.
A rebase changes commit SHA even when the migration SQL is identical. If approvals are tied only to commit SHA, every rebase forces another approval. Teams learn to rubber-stamp.
The better key is a content hash over the migration files themselves:
contentHash = sha256(canonical migration paths + file contents)Then the workflow can distinguish two cases:
- Commit changed, migration content unchanged: keep the approval.
- Migration content changed: require re-review.
That preserves the meaning of approval. Reviewers should re-approve changed migration content, not every Git event.
The audit log needs the policy snapshot
Policies change.
Maybe unknown analysis blocks by default this month. Maybe it only warned last month. Maybe HIGH risk required one approval before, and now it requires two. Maybe statement_timeout moved from a recommendation to a blocking rule.
Old audit records should not be reinterpreted through today’s policy.
Each run should store the policy snapshot used at the time:
- risk threshold
- unknown handling
- timeout requirements
- approval requirements
- exemption settings
- reviewer role requirements
That way, a record from March still means what it meant in March.
The audit log needs explicit approvals
“Approved” should include:
- approver
- approval source
- timestamp
- repository
- pull request
- commit SHA
- content hash
- policy snapshot
- decision
- optional note
The approval should be tied to the migration content hash. If the SQL changes, the approval is stale.
This is why GitHub PR review is a good primary signal, but not a complete audit log by itself. GitHub knows who reviewed the PR. The migration governance layer needs to connect that review to the exact migration analysis and policy result.
The audit log needs exemptions
Real production systems need exceptions.
Sometimes the correct answer is not “block forever.” It is:
We understand this migration takes a risky lock. We are running it during a maintenance window. The owner is named. The reason is written down. The exemption expires.
An exemption should require:
- actor
- written reason
- scope
- expiration
- related finding or run
- audit event
That is not a bypass. It is a controlled decision.
Auditors usually do not expect a perfect system with no exceptions. They expect exceptions to be deliberate, visible, and reviewable.
The audit log should be tamper-evident
A normal activity feed says “this happened.”
A stronger audit log also makes later tampering detectable.
One practical approach is a hash chain:
eventHash = sha256(previousEventHash + canonicalEventPayload)Every audit event stores the previous event hash and its own event hash. If someone edits an old event, the chain no longer verifies.
That does not remove the need for database permissions, backups, retention policy, or operational controls. But it makes the audit record more than a mutable table called events.
The CSV export is the first compliance feature
The first useful export does not need to be fancy.
A CSV is enough if it includes the fields people actually need:
- organization
- repository
- pull request
- commit SHA
- content hash
- migration files
- highest risk
- findings
- unknown statement count
- policy result
- approver
- approval time
- exemption reason
- event hash
That is the artifact an engineering manager can hand to an auditor or use in a post-incident review.
What pgfence Cloud is meant to add
The free pgfence CLI already analyzes migrations locally. That stays free.
The exploratory Cloud work is about the workflow around that analysis:
- block risky migration PRs
- require the right approval
- record the policy result
- record exemptions
- keep a migration audit trail
- export evidence
That is the difference between “we lint migrations” and “we can prove how risky migrations are governed.”
Cloud is currently an exploratory design-partner conversation around real production Postgres workflows. The point is to learn whether the approval and audit loop is painful enough to deserve a hosted product.