[Feature]: Manage postgres schema in a versioned manner

[dentiny]

Prerequisites

• I searched existing issues

Code of Conduct

• I agree to follow NVSentinel's Code of Conduct

Feature Summary

Hi team, in the current implementation, we're managing postgres schema in a single file, it's not ideal for several reasons

• (major) No schema version support
  • With one single file, users cannot track version history, making version rollback and forward, incident diagnosis manual
• (major) Application startup performs DDL
  • The datastore executes schema creation and modification during application startup, which means (1) application credentials require DDL privileges; (2) multiple replicas can attempt schema changes concurrently; (3) DDL failures prevent application startup; (4) Index or trigger changes may introduce locks during a rollout; (5) Schema deployment is coupled to application deployment without an explicit migration gate.
• (medium) The pg schema has been largely duplicate for multiple copies in the codebase, and they're not identical
  • Example-1: datastore golang code adds two timestamp columns but they're not in SQL schema
  • Example-2: datastore golang code adds an index but they're not in SQL schema
• (minor) The init schema values-tilt-postgresql.yaml is not very meaningful
  • It doesn't have any impact to an existing database
  • It's almost a duplicate with datastore code, which adds unnecessary maintenance burden

Problem/Use Case

I use NV sentinel in production and use postgres as datastore.

Proposed Solution

There're a few general DB management principles we should follow

• Manage the database schema in one single place, instead of replicating here and there
• Version the schema and store it in a persistent storage (i.e., a system postgres table)
• DDL and DML generally involves different procedure and permission, only perform DML in the application; DDL should be managed separately

Component

Deployment/Config