Patterns
Fiscal log immutability in PostgreSQL
INSERT-only with BEFORE UPDATE/DELETE triggers
Scell.io's fiscal log (table `fiscal_entries`) must be effectively immutable: no update or delete possible once validated, per French tax authority ISCA requirements. Three defence-in-depth levels. (1) Application level: Eloquent ISCA Guard blocks any mutation outside `draft` status. (2) DB level: `BEFORE UPDATE OR DELETE` trigger raises an exception if entry is `validated`. (3) Audit level: any attempt is logged in `fiscal_audit_logs` with `critical` severity. SHA-256 chain (each entry's hash includes previous hash) makes any retroactive tampering immediately detectable via `GET /api/v1/fiscal/integrity`. Complementary pattern: revoke `UPDATE`/`DELETE` at GRANT level for the application role, give this right only to a `dba_emergency` role used exclusively for schema fixes (never to modify fiscal data). A GDPR deletion (right to be forgotten) on non-fiscal PII is done via anonymisation (NULL or hash), not DELETE.
Key facts
- 3 levels: app ISCA Guard + DB trigger + audit log
- BEFORE UPDATE/DELETE trigger raises exception on validated entry
- GRANT revoked on app role, separate dba_emergency role
- SHA-256 chain detects any retroactive tampering
- GDPR: anonymise non-fiscal PII, never DELETE
Code example
-- 1. Trigger d'immutabilité au niveau DB
CREATE OR REPLACE FUNCTION prevent_fiscal_mutation()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.status = 'validated' THEN
RAISE EXCEPTION 'Fiscal entry % is immutable (status=validated)', OLD.id
USING ERRCODE = 'restrict_violation';
END IF;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER fiscal_entries_immutable
BEFORE UPDATE OR DELETE ON fiscal_entries
FOR EACH ROW EXECUTE FUNCTION prevent_fiscal_mutation();
-- 2. Révocation des droits côté applicatif
REVOKE UPDATE, DELETE ON fiscal_entries FROM scell_app;
GRANT UPDATE, DELETE ON fiscal_entries TO dba_emergency;
-- 3. Vérification d'intégrité de la chaîne SHA-256
SELECT id, prev_hash, hash,
encode(sha256((id || prev_hash || data)::bytea), 'hex') AS computed
FROM fiscal_entries
WHERE encode(sha256((id || prev_hash || data)::bytea), 'hex') <> hash;
-- Doit retourner 0 lignes