/production-awid-db

name: production-awid-db
description: Connect to the awid.ai production database for migrations, audits, and data operations. Credentials at .env.awid-production.

awid.ai production database operations

Credentials

Production credentials are at the repo root:

/Users/juanre/prj/awebai/aweb/.env.awid-production

The key variable is `AWID_DATABASE_URL` — a Neon Postgres connection string. The schema name is `awid` (not `aweb`).

Connecting

# Read the URL
grep AWID_DATABASE_URL .env.awid-production

# Connect with psql (strip the channel_binding parameter if psql complains)
psql 'postgresql://...'

Common operations

Audit table counts

SELECT 'did_aw_mappings' as tbl, count(*) FROM awid.did_aw_mappings
UNION ALL SELECT 'did_aw_log', count(*) FROM awid.did_aw_log
UNION ALL SELECT 'dns_namespaces', count(*) FROM awid.dns_namespaces
UNION ALL SELECT 'public_addresses', count(*) FROM awid.public_addresses
UNION ALL SELECT 'teams', count(*) FROM awid.teams
UNION ALL SELECT 'team_certificates', count(*) FROM awid.team_certificates
ORDER BY tbl;

Check for orphan addresses (FK violation candidates)

SELECT pa.name, ns.domain, pa.did_aw
FROM awid.public_addresses pa
JOIN awid.dns_namespaces ns ON ns.namespace_id = pa.namespace_id
LEFT JOIN awid.did_aw_mappings m ON m.did_aw = pa.did_aw
WHERE m.did_aw IS NULL;

Run migrations

AWID_DATABASE_URL='...' AWID_DB_SCHEMA=awid uv run --project awid python - <<'PY'
import asyncio, os
from awid_service.db import AwidDatabaseInfra

async def main():
    infra = AwidDatabaseInfra(schema=os.environ.get('AWID_DB_SCHEMA', 'awid'))
    await infra.initialize(run_migrations=True)
    await infra.close()

asyncio.run(main())
PY

Dump data

pg_dump 'postgresql://...' --schema=awid --data-only --column-inserts -f /tmp/awid-dump.sql

Schema reset (consolidation)

When consolidating migrations into a single 001_registry.sql:

1. Dump current data with `--column-inserts` 2. Transform the dump to match the new schema (strip dropped columns) 3. Drop the schema: `DROP SCHEMA awid CASCADE;` 4. Run the migration (see above) 5. Load the transformed data 6. Verify counts and orphans

This was done for awid 0.3.1. See commit cd01fac for the consolidation.

Notes

• The hosted ac database configured from `../ac/.env.production` is SEPARATE.

It has an embedded awid copy with minimal data. The external awid.ai registry database is what matters for production.

• Always verify orphan addresses before deploying migrations that

add FK constraints.

• Do NOT use `source .env.awid-production` directly — the URL

contains `&` which breaks shell parsing. Use grep or inline the URL.