Data Storage ¶
Primary database ¶
IndeVets’ primary internal database is a self-hosted PostgreSQL instance. This database instance contains both the operating tables for the CATS scheduling system, and warehousing tables for the BI team. The recipe for recreating this resource and processes/scripts for managing it are versioned in IndeVets’ source control system.
Data warehousing ¶
The BI team uses scripts and processes stored in IndeVets’ source control system to mirror and archive data from 3rd party systems for internal analysis and archival.
BI replica ¶
The entire PostgreSQL database, including both CATS operating tables and BI warehousing tables, is replicated to a read-only instance that BI tools and processes use to execute analysis queries. The BI replica also serves as an immediately available and consistently up-to-date data recovery source in the event that the primary database need be recreated/restored.
Auditing table ¶
All data changes made by users through the CATS application are logged to a dedicated auditing table contained within the primary database. The auditing table logs the before and after states of any changed values, the timestamp, the author, and full metadata about the request (including IP address, user agent, and URL). Staff have access to browse/search the auditing table through their CATS user interface, and the raw data is available for BI analysis.
Archival Snapshots ¶
Every hour, the entire database is snapshot to an encrypted backup repository hosted in cloud object storage.