--- # Gravity Platform Database Setup Playbook # Creates database tables - uses CREATE TABLE IF NOT EXISTS (safe to run multiple times) # # Usage: # ansible-playbook -i inventory/production.yml playbooks/db-setup.yml # # Prerequisites: # - Core services deployed (install.yml) # - DATABASE_URL configured in /opt/gravity/.env # - Required extensions enabled in your DB provider: # vector, pg_stat_statements # (DigitalOcean: Database → Settings → Allowed Extensions) - name: Database Setup hosts: all become: yes tasks: # REGISTER NAMES MUST NOT COLLIDE WITH -e VARIABLES. This registered into `env_file`, # which is also the name deploy passes on the command line as the path to the rendered # settings. Extra vars outrank registered ones in Ansible's precedence, so the register # never took: the variable stayed a string and the very next line asked a string for # `.stat`, failing with "object of type 'str' has no attribute 'stat'" — an error that # says nothing about the actual cause. - name: "[1/5] Verify .env exists" stat: path: /opt/gravity/.env register: remote_env - name: "[1/5] Fail if .env missing" fail: msg: ".env file not found at /opt/gravity/.env - run install.yml first" when: not remote_env.stat.exists - name: "[2/5] Check DATABASE_URL is configured" shell: grep -q "^DATABASE_URL=" /opt/gravity/.env register: db_url_check ignore_errors: yes - name: "[2/5] Fail if DATABASE_URL missing" fail: msg: "DATABASE_URL not found in .env - configure database connection first" when: db_url_check.rc != 0 - name: "[3/5] Enable required PostgreSQL extensions" command: > docker compose exec -T -e NODE_TLS_REJECT_UNAUTHORIZED=0 unoverse node -e "const{Pool}=require('pg'); const p=new Pool({connectionString:process.env.DATABASE_URL,ssl:{rejectUnauthorized:false}}); (async()=>{ const exts=['vector','pg_stat_statements']; const results=[]; for(const ext of exts){ try{await p.query('CREATE EXTENSION IF NOT EXISTS '+ext);results.push(ext+': OK')} catch(e){results.push(ext+': FAILED ('+e.message+')')} } console.log(results.join('\n')); await p.end(); process.exit(results.some(r=>r.includes('FAILED'))?1:0); })()" args: chdir: /opt/gravity register: ext_result ignore_errors: yes - name: "[3/5] Extension status" debug: msg: "{{ ext_result.stdout_lines | default(['No output']) }}" - name: "[3/5] Warn if extensions failed" debug: msg: | ⚠️ Some extensions failed to enable. You must enable them in your database provider's dashboard BEFORE running this playbook. DigitalOcean: Database → Settings → Allowed Extensions Enable: vector, pg_stat_statements See docs/runbooks/02-database.md for details. when: ext_result.rc != 0 # BEFORE MIGRATIONS, OR THEY CANNOT CREATE THEIR OWN BOOKKEEPING TABLE. PostgreSQL 15+ # dropped the implicit CREATE on schema public, and DigitalOcean creates every database # owned by its admin user, so the universe user connects fine and then fails on the # first CREATE TABLE. Granting is idempotent and only ever widens this one user's rights # inside its own database — it touches nothing else on a shared cluster. # # no_log because the admin connection string is an argument here. It arrives from # terraform at deploy time and is never written to the server. - name: "[3b/5] Grant the universe user rights on its own schema" command: > docker compose exec -T -e NODE_TLS_REJECT_UNAUTHORIZED=0 -e ADMIN_URL={{ pg_admin_url }} -e PG_USER={{ pg_user | default('universe') }} unoverse node -e "const{Client}=require('pg'); const c=new Client({connectionString:process.env.ADMIN_URL,ssl:{rejectUnauthorized:false}}); (async()=>{ await c.connect(); const u = JSON.stringify(process.env.PG_USER).replace(/^\"|\"$/g,''); await c.query('GRANT ALL ON SCHEMA public TO \"'+u+'\"'); await c.query('GRANT ALL ON ALL TABLES IN SCHEMA public TO \"'+u+'\"'); await c.query('GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO \"'+u+'\"'); await c.end(); console.log('granted'); })().catch(e=>{console.error(e.message);process.exit(1)})" args: chdir: /opt/gravity when: (pg_admin_url | default('')) | length > 0 and (pg_user | default('')) | length > 0 no_log: true register: grant_result - name: "[3b/5] Schema rights" debug: msg: "{{ 'universe may create objects in its database' if (pg_admin_url | default('')) | length > 0 else 'skipped — bring-your-own database, permissions are yours' }}" # Apply the .sql migrations with node-pg-migrate — same as `./unoverse db-setup` # (scripts/lib/db-setup.sh). The old programmatic `require('./dist/db')` table # setup is retired: the engine no longer ships a dist, and .sql migrations under # apps/unoverse/engine/migrations are the single source of truth. # --no-check-order, and it is NOT optional here: a database from before the # 2026-07-28 squash has 002-018 recorded in pgmigrations with no matching files # (they live inside 001_baseline now), so the order check refuses EVERY new # migration with "Not run migration 019_… is preceding already run migration 002_…". # db-setup.sh has carried this flag since the squash; this playbook runs the same # command and did not, so `unoverse db-setup` worked while `unoverse deploy db` # failed on the same database. Ordering still holds by filename and an applied name # is never re-run, so the check buys nothing a squashed history can satisfy. - name: "[4/5] Apply database migrations (node-pg-migrate)" shell: | docker compose exec -T -e NODE_TLS_REJECT_UNAUTHORIZED=0 unoverse \ npx node-pg-migrate up \ --migrations-dir /app/apps/unoverse/engine/migrations \ --migration-file-language sql \ --no-lock \ --no-check-order args: chdir: /opt/gravity register: migrate_result ignore_errors: yes # THE WHOLE FILE IS NOT THE ANSWER TO "DID IT WORK". This printed every line of the # baseline — several hundred lines of CREATE TABLE nobody asked to read, burying the one # line that says whether anything ran. Keep the names and the verdict. - name: "[4/5] Migration output" debug: msg: >- {{ (migrate_result.stdout_lines | default([]) | select('match', '^> - ') | list) + (migrate_result.stdout_lines | default([]) | select('search', 'Migrations complete|No migrations') | list) | default(['No output'], true) }} # SERVICES BOOTED BEFORE THIS SCHEMA EXISTED. install.yml has to start the containers # first — migrations run THROUGH the unoverse container, so there is nothing to run them # in until it is up. The cost is that every service that touches the database at boot # does so against an empty one: memory logged "permission denied for schema public" and # then 'relation "memories" does not exist', and stayed in that state until something # restarted it. Restarting here is the missing half of that ordering, and it only ever # happens on a first setup. - name: "[4b/5] Restart services against the migrated schema" command: docker compose restart args: chdir: /opt/gravity when: migrate_result.rc | default(1) == 0 - name: "[4b/5] Settle" pause: seconds: 15 when: migrate_result.rc | default(1) == 0 - name: "[5/5] Verify database connectivity" uri: url: "http://localhost:4101/health" status_code: 200 register: db_check retries: 5 delay: 2 until: db_check.status == 200 ignore_errors: yes post_tasks: - name: "=== DATABASE MIGRATION SUMMARY ===" debug: msg: | ============================================ DATABASE MIGRATION ============================================ Host: {{ inventory_hostname }} ({{ ansible_host }}) Extensions: {{ 'OK' if ext_result.rc == 0 else 'FAILED — enable in DB provider dashboard' }} Migration: {{ 'OK' if migrate_result.rc == 0 else 'FAILED — check logs above' }} If migration failed, check: - Extensions enabled in DB provider (vector, pg_stat_statements) - DATABASE_URL in /opt/gravity/.env - Database is accessible from VM - See docs/runbooks/02-database.md for setup instructions ============================================