Skip to content

DB Schema Changes

Rule

Every table in the heezy database must be defined in: ansible-heezy/roles/heezy-postgres-schema/files/schema.sql

The schema is applied idempotently on every Ansible run using IF NOT EXISTS and ADD COLUMN IF NOT EXISTS.

Adding a New Table

  1. Add CREATE TABLE IF NOT EXISTS ... block to schema.sql
  2. Add indexes: CREATE INDEX IF NOT EXISTS ...
  3. Add grants if needed: GRANT SELECT ON my_table TO heezy_app;
  4. Commit + push ansible-heezy
  5. Apply immediately via Ansible or by running the SQL directly on big-boi:
ssh mcp-admin@192.168.1.21 "sudo docker exec n8n-postgres psql -U n8n -d heezy -f /dev/stdin" < schema.sql

Adding a Column

ALTER TABLE my_table ADD COLUMN IF NOT EXISTS my_col TEXT;

Add this to schema.sql after the CREATE TABLE block (it's idempotent). Also add startup migration in any app that owns the table.

Backfilling Data

For large backfills, use a standalone Node.js script in workspace/scripts/. Run directly from the OpenClaw container — Postgres is directly reachable at 192.168.1.21:5432.

const { Pool } = require('/home/node/.openclaw/workspace/node_modules/pg');
const pool = new Pool({ host: '192.168.1.21', port: 5432, user: 'n8n',
  password: '6cd971263b04ce78b430c3c65f3dcedb', database: 'heezy' });

Ownership Notes

  • heezy_app — owns most app tables (receipts, orders, etc.)
  • n8n — owns bank_statements, bank_transactions (created by statement-parser)
  • Cross-owner access requires explicit GRANT SELECT ON table TO user