Skip to content

Audit table: the decision log in PostgreSQL

The decision log of F6 is a file: one writer at a time, no way to query it, and anyone with the file can edit a record. An audit table in PostgreSQL keeps the same hash chain and adds two things a file cannot have: a SELECT that returns a page of records, and a trigger that refuses any UPDATE and any DELETE. A record has the same hash in the file and in the table, because the hash is one function that both use.

1. Start PostgreSQL and run the migration

docker-compose.yml starts a PostgreSQL 16 container. The migration is in alembic/versions/0001_decisions.py; alembic/env.py reads the URL from DENSE_ARMOR_PG_URL or from -x url=....

import os, subprocess, sys
work = "/content/Dense-Armor"
url = os.environ["DENSE_ARMOR_TEST_PG_URL"]
env = os.environ.copy()
env["DENSE_ARMOR_PG_URL"] = url
r = subprocess.run(
    [sys.executable, "-m", "alembic", "downgrade", "base"],
    capture_output=True, text=True, cwd=work, env=env,
)
r = subprocess.run(
    [sys.executable, "-m", "alembic", "upgrade", "head"],
    capture_output=True, text=True, cwd=work, env=env,
)
print("exit:", r.returncode)
print(r.stderr.strip())
exit: 0
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 0001_decisions, create the decisions table and the trigger that forbids update and delete

The migration creates the table decisions with five columns (seq, t, payload, prev, hash), a unique constraint on prev (two records cannot follow the same one, so a fork is refused by the database), a unique constraint on hash, and the trigger decisions_no_change.

2. Append three records and read them back

AuditDB(url) opens a SQLAlchemy connection. append(t, payload) writes one row in one transaction: pg_advisory_xact_lock, read the last row, compute the hash, insert. verify() reads the rows in batches and recomputes every hash.

import os, sqlalchemy
from dense_armor.core.finance.audit_db import AuditDB
db = AuditDB(os.environ["DENSE_ARMOR_TEST_PG_URL"])
db.append(1.0, {"account": "AAPL", "reason": "ok"})
db.append(2.0, {"account": "MSFT", "reason": "size"})
db.append(3.0, {"account": "NVDA", "reason": "ok"})
e = sqlalchemy.create_engine(os.environ["DENSE_ARMOR_TEST_PG_URL"])
with e.connect() as conn:
    for row in conn.execute(sqlalchemy.text(
            "select seq, t, payload, prev, hash from decisions order by seq")):
        print(row[0], row[1], row[2], row[3][:8] + "...", row[4][:8] + "...")
print("verify:", db.verify())
0 1.0 {"account":"AAPL","reason":"ok"} 00000000... 53c903e6...
1 2.0 {"account":"MSFT","reason":"size"} 53c903e6... 2725282c...
2 3.0 {"account":"NVDA","reason":"ok"} 2725282c... 6b104b87...
verify: True

prev of the first row is GENESIS_HASH (64 zeros), prev of each other row is the hash of the row before. The payload column keeps the canonical JSON text that was hashed, not JSONB, which would not preserve the exact text. A record has the same hash in the file of F6 and in this table, because both use record_hash from decision_log.py.

3. The trigger refuses an UPDATE, and a tamper with the trigger off is detected

The trigger decisions_no_change raises an exception before any UPDATE and any DELETE. The verify() method returns False when a record has been edited with the trigger off.

import os, sqlalchemy
e = sqlalchemy.create_engine(os.environ["DENSE_ARMOR_TEST_PG_URL"])
try:
    with e.begin() as conn:
        conn.execute(sqlalchemy.text("UPDATE decisions SET t = 999 WHERE seq = 0"))
except Exception as ex:
    print("UPDATE:", type(ex).__name__, "-", str(ex).splitlines()[0][:80])
from dense_armor.core.finance.audit_db import AuditDB
db = AuditDB(os.environ["DENSE_ARMOR_TEST_PG_URL"])
with e.begin() as conn:
    conn.execute(sqlalchemy.text("ALTER TABLE decisions DISABLE TRIGGER decisions_no_change"))
    conn.execute(
        sqlalchemy.text("UPDATE decisions SET payload = :p WHERE seq = 0"),
        {"p": '{"account":"AAPL","reason":"tampered"}'},
    )
    conn.execute(sqlalchemy.text("ALTER TABLE decisions ENABLE TRIGGER decisions_no_change"))
print("verify after tamper:", db.verify())
UPDATE: InternalError - (psycopg2.errors.RaiseException) decisions is append-only
verify after tamper: False

Details

References. - NIST. FIPS 180-4, Secure Hash Standard. August 2015. SHA-256 is section 6.2 (pages 27 and 28 of the PDF). - Alembic documentation, Auto Generating Migrations (alembic.sqlalchemy.org). Used to compare the migration and the metadata of the table. - SQLAlchemy documentation, Engine Configuration (docs.sqlalchemy.org). Used for the URL and the dialect check.

Choices stated explicitly. - The payload column is TEXT, not JSONB. JSONB normalises the text of the object, so {"a": 1} and {"a":1} would be stored the same way and the hash recomputed from the database would differ from the hash stored in the row. TEXT keeps the exact text. - prev has a unique constraint. Two records cannot follow the same one: a fork is refused by the database, not only by verify(). - append takes pg_advisory_xact_lock on one fixed integer before it reads the last row and inserts. Two writers cannot insert the same seq. - The hash is record_hash from decision_log.py, the same function that DecisionLog uses. The code is one function, not a copy: a record has the same hash in the file and in the table. - The URL must be PostgreSQL. A SQLite URL is ValueError, not a fallback.

Measured cost. The append method takes an exclusive advisory lock and reads the last row, so its cost is O(1) in the number of rows. verify() reads the table in batches of 500 rows and recomputes every hash, so its cost is O(n). The two numbers are not measured here.

Declared limits. - The owner of the table can ALTER TABLE ... DISABLE TRIGGER and edit a record. The trigger is a guard against mistakes, not a security boundary. verify() catches the edit, but only the next time it runs. - There is no signature. The hash is not signed, and no key is involved. A reader who trusts the code that wrote the table trusts the hash. - There is no retention policy and no backup. The table is a live table, not a legal record. - Deleting the last rows of the table leaves a valid chain. verify() cannot see it; the only defence is to keep last_hash somewhere else, and the class exposes it. - One PostgreSQL 16 was tested. Another major version may work; it is not measured. - No network, no credential beyond the throwaway password of the container in docker-compose.yml and the CI service. - The migration is 0001_decisions. If a schema change is needed, it is a new migration, not an edit of this one.

These pages describe what the code does on the data shown. Not a forecast, not financial advice.