Enable pgaudit and enforce doc fk

[creatorrr]

trafficstars

User description

Summary

• enable pgaudit in memory-store docker compose
• enforce doc ownership with foreign key
• compute token counts using NEW.model
• add migration 000042 for prod rollout
• document the migration in CHANGELOG

Testing

• ruff format memory-store/docker-compose.yml memory-store/migrations/000006_docs.up.sql memory-store/migrations/000006_docs.down.sql memory-store/migrations/000015_entries.up.sql memory-store/migrations/000042_doc_fk_and_token_function.up.sql memory-store/migrations/000042_doc_fk_and_token_function.down.sql (fails: Failed to parse ...)

---

PR Type

enhancement, bug_fix, documentation

---

Description

• Enable and configure pgaudit in Docker Compose for auditing.

• Enforce foreign key constraint on doc_owners for doc integrity.

• Update token counting trigger to use NEW.model dynamically.

• Add migration 000042 for new constraints and rollback support.

• Document all changes in the changelog.

---

Changes walkthrough 📝

	Relevant files
Documentation	1 files CHANGELOG.md Document pgaudit, doc FK, and migration changes                    +7/-0
Enhancement	4 files docker-compose.yml Enable and configure pgaudit in dev Docker Compose              +7/-5      000006_docs.up.sql Add unique constraint and enforce doc FK in schema              +6/-2      000042_doc_fk_and_token_function.down.sql Migration rollback: remove doc FK and revert trigger          +30/-0    000042_doc_fk_and_token_function.up.sql Migration: add doc FK, unique constraint, update trigger  +32/-0
Bug fix	2 files 000006_docs.down.sql Drop doc_owners/doc constraints on rollback                            +2/-0      000015_entries.up.sql Use NEW.model for token counting in trigger                            +3/-1

---

Need help?

Type /help how to ... in the comments thread for any questions about Qodo Merge usage.

Check out the documentation for more information.

[qodo-merge-for-open-source[bot]]

PR Reviewer Guide 🔍

Here are some key observations to aid the review process:

⏱️ Estimated effort to review: 2 🔵🔵⚪⚪⚪

🧪 No relevant tests

🔒 No security concerns identified

⚡ Recommended focus areas for review Container Restart The pgaudit installation modifies PostgreSQL configuration files after container initialization. This could cause issues if the container restarts, as the changes might be lost or applied multiple times. entrypoint: ["bash", "-c"] command: >- apt-get update && apt-get install -y postgresql-17-pgaudit && sed -ri "s/[#]*\s*(shared_preload_libraries)\s*=\s*'(.*)'/\1 = 'pgaudit,\2'/;s/,'/'/" /home/postgres/pgdata/data/postgresql.conf && echo "pgaudit.log = 'read, write'" >> /home/postgres/pgdata/data/postgresql.conf && exec /docker-entrypoint.sh postgres Migration Idempotency The migration adds constraints with IF NOT EXISTS but doesn't check if they already exist before attempting to add them. This could cause issues if the migration is run multiple times. ALTER TABLE docs ADD CONSTRAINT IF NOT EXISTS uq_docs_developer_doc UNIQUE (developer_id, doc_id); ALTER TABLE doc_owners ADD CONSTRAINT IF NOT EXISTS fk_doc_owners_doc FOREIGN KEY (developer_id, doc_id) REFERENCES docs (developer_id, doc_id) ON DELETE CASCADE;

[qodo-merge-for-open-source[bot]]

PR Code Suggestions ✨

Explore these optional code suggestions:

Category	Suggestion	Impact
Possible issue	Validate data before constraints The migration adds constraints without checking for existing data violations. Add validation to ensure existing data complies with the new constraints before applying them, preventing migration failures. memory-store/migrations/000042_doc_fk_and_token_function.up.sql [3-9] +-- Check for orphaned doc_owners records before adding constraint +DO $$ +BEGIN + IF EXISTS ( + SELECT 1 FROM doc_owners o + LEFT JOIN docs d ON o.developer_id = d.developer_id AND o.doc_id = d.doc_id + WHERE d.doc_id IS NULL + ) THEN + RAISE EXCEPTION 'Cannot add foreign key: orphaned records exist in doc_owners'; + END IF; +END $$; + ALTER TABLE docs ADD CONSTRAINT IF NOT EXISTS uq_docs_developer_doc UNIQUE (developer_id, doc_id); ALTER TABLE doc_owners ADD CONSTRAINT IF NOT EXISTS fk_doc_owners_doc FOREIGN KEY (developer_id, doc_id) REFERENCES docs (developer_id, doc_id) ON DELETE CASCADE; [ ] Apply / Chat Suggestion importance[1-10]: 8 __ Why: Adding a check for orphaned records before applying foreign key constraints is important for migration reliability, preventing failures and potential data integrity issues. This is a significant improvement for production database migrations.	Medium
	Handle first-time container startup The current approach modifies configuration files after container startup, which may fail if the data directory doesn't exist yet (first run). Add a check to ensure the configuration file exists before attempting to modify it. memory-store/docker-compose.yml [14-20] # Install and enable pgaudit for logging entrypoint: ["bash", "-c"] command: >- apt-get update && apt-get install -y postgresql-17-pgaudit && - sed -ri "s/[#]*\s*(shared_preload_libraries)\s*=\s*'(.*)'/\1 = 'pgaudit,\2'/;s/,'/'/" /home/postgres/pgdata/data/postgresql.conf && - echo "pgaudit.log = 'read, write'" >> /home/postgres/pgdata/data/postgresql.conf && + if [ -f /home/postgres/pgdata/data/postgresql.conf ]; then + sed -ri "s/[#]*\s*(shared_preload_libraries)\s*=\s*'(.*)'/\1 = 'pgaudit,\2'/;s/,'/'/" /home/postgres/pgdata/data/postgresql.conf && + echo "pgaudit.log = 'read, write'" >> /home/postgres/pgdata/data/postgresql.conf + fi && exec /docker-entrypoint.sh postgres [ ] Apply / Chat Suggestion importance[1-10]: 7 __ Why: The suggestion improves robustness by ensuring configuration changes are only attempted if the config file exists, which prevents errors during first-time container startup. This is a practical enhancement but not critical, as it addresses a potential but not guaranteed failure scenario.	Medium
[ ] More