chore(db): investigate range partitioning for high-write append-only audit tables

[ericfitz]

Summary

Investigate time-range partitioning (e.g., monthly partitions on created_at) for the append-only audit tables — system_audit_entries and audit_entries/version_snapshots — so that retention pruning on Oracle ADB becomes an instant DROP PARTITION instead of row-by-row DELETE.

Deferred from the #400 design (system-audit retention): current admin write volume (~34 /admin/* write endpoints at typical admin cadence) does not justify the machinery, so #400 ships indexed-created_at DELETE pruning. This issue tracks revisiting that decision if write volume grows.

Originally recommended by the oracle-db-admin review of the #355 schema:

Range partitioning on created_at (e.g., monthly partitions).
The right Oracle pattern for a high-write append-only audit table makes pruning a DROP PARTITION (instant, no redo, no row-by-row delete locking) instead of a DELETE.

Acceptance Criteria

• Decide trigger conditions: what observed write volume / prune duration on ADB justifies partitioning (pair with the ORA-08177 / prune-latency monitoring from chore(db): apply PG default_transaction_isolation + verify Oracle SERIALIZABLE + docs #450 and the perf(db): __global__ alias counter is a serialization hot spot under SERIALIZABLE #452 watch item)
• Evaluate feasibility: GORM AutoMigrate does not manage partitions — a internal/dbschema/ post-migration hook would own partition DDL on both PG (declarative partitioning) and Oracle (interval partitioning)
• Determine interaction with the age-floored append-only triggers (bug(db): audit prune/delete paths are blocked on Oracle by append-only triggers #453, feat(api): retention and pruning for system_audit_entries (T7 follow-up) #400): DROP PARTITION is DDL and bypasses row-level triggers — confirm this is acceptable for the immutability story (floor enforcement moves to "never drop a partition younger than the floor")
• Recommend adopt/defer with a sizing estimate

Notes

• Refs feat(api): retention and pruning for system_audit_entries (T7 follow-up) #400 (system-audit retention design), bug(db): audit prune/delete paths are blocked on Oracle by append-only triggers #453 (age-floored triggers), perf(db): __global__ alias counter is a serialization hot spot under SERIALIZABLE #452 (alias-counter ADB watch item), feat(api): step-up authentication and in-band audit log on /admin/* writes (T7 part 1) #355 (T7 system audit).
• No partitioning precedent exists in the codebase today; this would be new DDL machinery for both PostgreSQL and Oracle.