Database Migration Guide

Overview

This guide explains how database schema is managed for the crypto inventory platform across different deployment environments, including Docker Compose, EC2, EKS, and RDS.

Schema & Seed Management Pattern

Single Source of Truth Approach:

All database schema and core seed data is maintained in two primary files, with an optional third file for demo data:

scripts/database/schema.sql – Consolidated schema file (manually maintained)
- Contains all schema definitions: extensions, types, tables, indexes, functions, RLS policies, etc.
- Manually maintained – edit this file directly when schema changes are needed
- Used for new database initialization in all environments
- Idempotent – safe to run on existing databases (uses CREATE TABLE IF NOT EXISTS, etc.)
scripts/database/seed_core.sql – Core platform configuration seed (manually maintained)
- Contains environment-agnostic configuration:
  - Platform admin users (platform_users)
  - Measurement templates (measurement_templates)
  - Platform compliance frameworks and controls (platform_frameworks, platform_framework_controls, control_measurements)
- Manually maintained – edit this file when core configuration changes are needed
- Applied explicitly by scripts in all environments (dev, smoke, prod); not mounted into docker-entrypoint-initdb.d.
scripts/database/seed.sql – Dev/demo seed data (manually maintained)
- Contains demo/test data: tenants, demo users, large asset sets, legacy backfills, etc.
- Development-only – used to populate rich demo data in dev environments
- Not used in production, and not required for smoke tests

Archived Files:

Individual migration files have been archived to scripts/database/archive/ for historical reference. All schema changes should now be made directly to schema.sql.

Schema File Structure

The consolidated schema.sql includes (in order):

Database initialization – Extensions, types, functions (init.sql)
Authentication schema – Tenants, users (001_auth_schema.sql)
Core platform – Assets, certificates, implementations (migrations.sql)
RBAC system – Roles and permissions (05-rbac-migration.sql)
Feature schemas – Discovery, sensors, artifacts, integrations, etc.
Compliance frameworks – Frameworks, controls, measurements, templates
- Framework management (27-compliance-framework-management.sql)
- Measurement templates (28-measurement-templates.sql)
- Enhanced measurement types (30-enhance-measurement-types.sql)
Security – Row Level Security policies (24-rls-policies.sql)
Scalability – Table partitioning (25-table-partitioning.sql)

Seed Data

Seed data is split into core and demo layers:

Core seed (seed.sql):
- Platform admins, measurement templates, and platform frameworks
- Required in all environments (dev, smoke, prod)
- Applied explicitly using psql in session/deployment scripts
Demo seed (Tier 2):
- Demo tenants, demo users, bulk asset data, compliance violations
- Optional and dev-only – used to provide a rich demo dataset for local development
- Never applied automatically in production; smoke environments may choose not to run it at all
- Includes event-driven compliance findings generation (see below)

Demo Data Seeding and Event-Driven Findings

Demo data seeding uses an event-driven approach to generate compliance findings:

Violation Seeding (seed_democorp_compliance_violations.sql):
- Creates assets and certificates with actual violations matching NIST CSF controls
- Examples:
  - PR.DS-1: Certificate with 1024-bit RSA key (violates < 2048 bit threshold)
  - PR.DS-2: Network asset with TLS 1.0 (violates weak TLS version pattern)
  - PR.DS-3: Certificate expiring in 15 days (violates < 30 day threshold)
Event Triggering (trigger-compliance-evaluation.sh):
- Publishes AssetChangedEvent events via NATS for all demo tenant assets
- Triggers compliance engine evaluation
- Generates findings naturally through the normal evaluation flow
Benefits:
- Findings persist through re-evaluation (not marked INACTIVE)
- Findings match actual asset violations
- Automatic updates when assets change

Environment Behavior:

Development (DEPLOY_ENV=development): Violations seeded and events triggered automatically
Smoke (DEPLOY_ENV=smoke): Only if --with-demo flag is used
Production (DEPLOY_ENV=production): Demo findings generation is skipped

For more details, see scripts/database/README.md and docsv4/development/data-seeding.md.

Deployment Environments

Docker Compose (Development/Testing)

New Databases (Automatic + Script-Driven Seed):

On first startup, schema is automatically applied via schema.sql mount:

volumes:
  - ./scripts/database/schema.sql:/docker-entrypoint-initdb.d/01-schema.sql
  - ./scripts/database/critical-tables.sql:/docker-entrypoint-initdb.d/03-critical-tables.sql

Core and demo seeds are applied by scripts, not by the container entrypoint:
- scripts/database/seed_core.sql – applied explicitly by scripts/session-init.sh
- scripts/database/seed.sql – applied explicitly (and non-blockingly) by scripts/session-init.sh for dev/demo data

The consolidated schema.sql file contains all schema definitions and is applied automatically when PostgreSQL initializes a new database. Core configuration is then layered on via seed_core.sql, followed by optional demo data from seed.sql.

Existing Databases (Manual):

For existing databases, apply the consolidated schema (idempotent):

# Apply the full consolidated schema (will skip existing objects)
docker exec crypto-postgres psql -U crypto_user -d crypto_inventory 
  -f scripts/database/schema.sql

Note: The consolidated schema uses CREATE TABLE IF NOT EXISTS and similar idempotent statements, so it's safe to run on existing databases. All schema changes should be made to schema.sql directly.

EC2-Smoke / Production (Docker Compose)

New Databases (Automatic):

Schema is automatically applied via the consolidated schema.sql file:

volumes:
  - ./scripts/database/schema.sql:/docker-entrypoint-initdb.d/01-schema.sql

Existing Deployments (Manual):

For existing production databases, apply the consolidated schema:

# Apply full schema (idempotent - safe for existing databases)
docker compose -f docker-compose.prod.yml exec -T postgres 
  psql -U crypto_user -d crypto_inventory 
  -f scripts/database/schema.sql

RDS (AWS Managed PostgreSQL)

For RDS deployments, migrations must be applied manually via psql or AWS RDS Query Editor.

Prerequisites

RDS instance accessible from your local machine or bastion host
Database credentials
Security group allows connections from your IP

Apply Schema

Recommended: Use the migration script

# Use the provided script (handles connection and verification)
./scripts/apply-rds-migrations.sh prod

Manual: Apply schema.sql directly

# Set connection details
export RDS_HOST=your-rds-instance.region.rds.amazonaws.com
export RDS_DB=crypto_inventory
export RDS_USER=crypto_user
export PGPASSWORD=your-password

# Apply consolidated schema (idempotent - safe for existing databases)
psql -h $RDS_HOST -U $RDS_USER -d $RDS_DB -f scripts/database/schema.sql

Using AWS RDS Query Editor

Navigate to RDS Console → Your Database → Query Editor
Copy contents of scripts/database/schema.sql
Paste and execute (may need to run in chunks if very large)

Using AWS Systems Manager Session Manager (Recommended for Production)

# Connect to bastion host via SSM
aws ssm start-session --target i-xxxxx

# From bastion, connect to RDS
psql -h $RDS_HOST -U $RDS_USER -d $RDS_DB -f /path/to/schema.sql

EKS (Kubernetes)

For EKS deployments with RDS, apply migrations using a Kubernetes Job or directly via kubectl exec.

Option 1: Kubernetes Job (Recommended)

Create a migration job:

apiVersion: batch/v1
kind: Job
metadata:
  name: db-migration-rls
  namespace: crypto-inventory
spec:
  template:
    spec:
      containers:
      - name: postgres-client
        image: postgres:17-alpine
        command:
        - /bin/sh
        - -c
        - |
          psql -h $RDS_HOST -U $RDS_USER -d $RDS_DB -f /migrations/schema.sql
        env:
        - name: RDS_HOST
          valueFrom:
            secretKeyRef:
              name: crypto-inventory-secrets
              key: rds-host
        - name: RDS_USER
          valueFrom:
            secretKeyRef:
              name: crypto-inventory-secrets
              key: rds-user
        - name: PGPASSWORD
          valueFrom:
            secretKeyRef:
              name: crypto-inventory-secrets
              key: rds-password
        volumeMounts:
        - name: migrations
          mountPath: /migrations
      volumes:
      - name: migrations
        configMap:
          name: db-migrations
      restartPolicy: Never
  backoffLimit: 3

Create ConfigMap with migration files:

kubectl create configmap db-migrations 
  --from-file=schema.sql=scripts/database/schema.sql 
  -n crypto-inventory

Apply the job:

kubectl apply -f migration-job.yaml
kubectl wait --for=condition=complete --timeout=300s job/db-migration-rls -n crypto-inventory

Option 2: kubectl exec (Quick)

If you have a pod with postgres client:

# Copy schema file to pod
kubectl cp scripts/database/schema.sql crypto-inventory/some-pod:/tmp/

# Execute schema
kubectl exec -n crypto-inventory some-pod -- 
  psql -h $RDS_HOST -U $RDS_USER -d $RDS_DB -f /tmp/schema.sql

Making Schema Changes

Editing schema.sql

When you need to make schema changes:

Edit scripts/database/schema.sql directly
- Add new tables, columns, indexes, functions, etc.
- Maintain proper dependency order (tables before foreign keys, etc.)
- Use idempotent statements (CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE FUNCTION, etc.)

Test the changes

# Test with fresh database
docker compose down -v
docker compose up -d postgres
# Verify schema applied correctly

Apply to existing databases
- The schema is idempotent, so you can run it on existing databases
- Or use the RDS migration script: ./scripts/apply-rds-migrations.sh <env>

Schema Structure (for reference)

The consolidated schema applies migrations in this order:

Database initialization – Extensions, types, functions
Authentication – Tenants, users, platform users
Core platform – Assets, certificates, implementations
RBAC – Roles and permissions
Features – Discovery, sensors, artifacts, integrations, monitoring
Security – Row Level Security policies (REQUIRED for production)
Scalability – Table partitioning (optional, for high scale)

Note: For existing databases, you may need to apply individual migrations incrementally rather than the full consolidated schema.

Verification

After applying migrations, verify they were successful:

Check RLS is Enabled

-- Check if RLS is enabled on sensors table
SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' 
AND tablename IN ('sensors', 'pending_sensors', 'sensor_discoveries', 'network_assets', 'crypto_implementations');

-- Should show rowsecurity = true

Check RLS Policies Exist

-- List all RLS policies
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual 
FROM pg_policies 
WHERE schemaname = 'public' 
AND tablename IN ('sensors', 'pending_sensors', 'sensor_discoveries', 'network_assets', 'crypto_implementations');

-- Should show policies for each table

Check Security Functions Exist

-- Check if security functions exist
SELECT proname, proargtypes 
FROM pg_proc 
WHERE proname IN ('set_tenant_context', 'clear_tenant_context');

-- Should return 2 rows

Test RLS (Development Only)

-- Set tenant context
SELECT set_tenant_context('your-tenant-uuid-here'::uuid);

-- Try to query sensors (should only see your tenant's sensors)
SELECT id, name, tenant_id FROM sensors;

-- Clear context
SELECT clear_tenant_context();

Rollback Procedures

RLS Policies Rollback

RLS policies can be disabled if needed (not recommended):

-- Disable RLS on a table (emergency only)
ALTER TABLE sensors DISABLE ROW LEVEL SECURITY;

-- Drop policies (if needed)
DROP POLICY IF EXISTS sensors_tenant_isolation ON sensors;

Warning: Disabling RLS removes database-level tenant isolation. Only do this in emergencies and re-enable immediately.

Partitioning Rollback

Partitioning migration creates new tables but doesn't rename existing ones. To rollback:

Don't activate partitioned tables (keep using original tables)
Drop partitioned tables if created:

DROP TABLE IF EXISTS sensor_discoveries_partitioned CASCADE;
DROP TABLE IF EXISTS network_assets_partitioned CASCADE;
DROP TABLE IF EXISTS crypto_implementations_partitioned CASCADE;

Pre-Deployment Checklist

Before deploying updated sensor-manager service with RLS support:

RLS policies migration (24-rls-policies.sql) applied to database
RLS policies verified (see Verification section)
Security functions (set_tenant_context, clear_tenant_context) exist
Application code updated (sensor-manager with RLS middleware)
Tested in staging environment
Backup database before production deployment

Post-Deployment Verification

After deploying updated services:

Check service logs for RLS context errors:

docker logs crypto-sensor-manager | grep -i "rls|tenant"

Test tenant isolation:
- Create test data for different tenants
- Verify each tenant only sees their own data
- Verify cross-tenant queries are blocked
Monitor performance:
- Check query performance hasn't degraded
- Monitor connection pool usage
- Check for any RLS-related errors

Troubleshooting

RLS Blocking All Queries

If RLS is blocking all queries (including system queries):

-- Temporarily allow empty tenant context (development only)
-- Check if policies allow empty string
SELECT qual FROM pg_policies WHERE policyname LIKE '%tenant_isolation%';

-- Policies should include: OR current_setting('app.tenant_id', true) = ''

Migration Fails with Permission Error

Ensure database user has necessary permissions:

-- Grant required permissions
GRANT EXECUTE ON FUNCTION set_tenant_context(UUID) TO crypto_user;
GRANT EXECUTE ON FUNCTION clear_tenant_context() TO crypto_user;

Application Can't Set Tenant Context

Check middleware is setting context:

-- Check if context is being set (from application logs)
-- Should see: SELECT set_tenant_context(...) before queries

Production Checklist – Production deployment checklist