agent-database

The agent-database skill is a specialized PostgreSQL expert designed to manage the database lifecycle for the Diddit household chore management application. It focuses on maintaining schema integrity through a rigorous, migration-first workflow that ensures every change is versioned, idempotent, and testable. The agent ensures that all database operations—including CREATE TABLE, ALTER TABLE, index creation, and constraint management—are executed within atomic transactions using BEGIN and COMMIT blocks. This prevents partial deployments and keeps the schema synchronized across local development, testing, and production environments.

• Expert management of migration files following the NNN_description.sql naming convention within docker/postgres/migrations/.

• Enforces strict camelCase naming for all column definitions with mandatory double-quote wrapping, ensuring consistency with the TypeScript-based backend.

• Implements idempotent logic using IF NOT EXISTS and anonymous DO blocks to prevent errors during repeated execution, which is crucial for automated deployment pipelines.

• Handles sophisticated database tasks such as UUID primary key generation, timestamp management with createdAt/updatedAt, foreign key relationships with cascading deletes, and enumeration modeling using TEXT with CHECK constraints.

• Coordinates with the init.sql file to guarantee that fresh database installations remain identical to the migrated production state.

• Provides a robust testing checklist, including instructions for local validation using docker exec commands and psql verification, ensuring that migrations never break the running application.

• Always verify migrations using the test database (st44_test) before committing changes to the repository.

• Ensure schema_migrations table is updated within every transaction to track deployment state.

• Use idempotent patterns for all schema modifications to allow re-running scripts without failure.

• Perform schema verification after every migration using psql introspection commands like \d to inspect table structure and constraints.

• When creating or modifying tables, always prioritize the NNN_description sequential numbering system to maintain proper migration order.