Using database triggers to reliably track model history

on YouTube ↗38m

" summary

Proposes using Postgres triggers plus django-pghistory to create structured per-model event tables that snapshot every INSERT/UPDATE so bulk ops and raw SQL never miss an audit row, with optional immutability to prevent tampering. Shows trigger installation via PG triggers, a context manager that attaches request/user metadata, a revert API to restore prior states, and a soft-delete pattern that stores deletions in a separate history table — all claimed to run reliably and performantly.

AI-written summary · about

" chapters (15)

1. 0:00Database trigger history intro
2. 3:08Traditional history tracking flaws
3. 6:39Django-PG-History internals
4. 9:46Why database triggers matter
5. 12:11Trigger-based history benefits
6. 15:44User/request metadata tracking
7. 19:35Advanced design patterns
8. 20:18Default context with middleware
9. 22:22Celery task context tracking
10. 24:07Reverting models
11. 26:35Soft delete with triggers
12. 31:06Foreign keys to events
13. 33:32App-specific event logs
14. 35:17Overdraft alerts tracking
15. 37:23Q&A

" related talks

• 30mCall me back, PostgresIfat Ribon · RailsConf 2022
• 43mDjango Transactions: Past, Present, and FutureChristophe Pettus · DjangoCon AU 2014
• 50mPastgreSQL: Dead data tells all tales!Srivathsava Rangarajan, Samuel Elston · PostgresOpen Silicon Valley 2017
• 22mDjango with PostgreSQL superpowersPaolo Melchiorre · Python Web Conf 2023
• 21mDjango and PostgreSQL: An Even-Closer UnionChristophe Pettus · DjangoCon US 2016
• 46mImmutable Data in PostgresGary Sieling · PostgresOpen 2014
• 44mPostgreSQL in Django 1.8Christophe Pettus · DjangoCon US 2015
• 43mJust Use Postgres.Rhys Elsmore · PyCon Australia 2015