Database Migration Guide

1.0.0 is a fresh install only. The general asset inventory release dropped the port-as-asset tables (network_assets, devices) and the asset_type enum rather than migrating them, and moved credential encryption to v2 — old credentials cannot be decrypted under it. There is no migration path from a core-v0.x database to 1.0.0; start a new database and pg_dump the old one first if you want to keep it for reference. See INSTALL.md's "Run it" section and the [1.0.0] entry in CHANGELOG.md.

Overview

This guide explains how database schema is managed for Vista Platform across different deployment environments, including Docker Compose and Kubernetes (EKS/RDS).

Schema & Seed Management Pattern

Single Source of Truth Approach:

All database schema and seed data is maintained in two files:

• 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.sql – Core platform seed data (manually maintained)

• Contains platform admin users (platform_users), subscription tiers, measurement templates, and the free compliance frameworks (platform_frameworks, platform_framework_controls, control_measurements)
• Manually maintained – edit this file when core configuration changes are needed
• Applied automatically by Postgres's own init mechanism, immediately after schema.sql, in every environment

For re-seeding an existing database idempotently (e.g. after a partial apply), scripts/apply_core_seed.sh re-runs seed.sql via docker exec with dependency checks.

Archived Files:

Individual per-feature migration files have been consolidated into schema.sql and removed — there is no directory of historical migration files to consult. All schema changes should now be made directly to schema.sql.

Schema File Structure

The consolidated schema.sql includes (in order): 1. Database initialization - Extensions, types, functions (init.sql) 2. Authentication schema - Tenants, users (001_auth_schema.sql) 3. Core platform - Assets, certificates, implementations (migrations.sql) 4. RBAC system - Roles and permissions (05-rbac-migration.sql) 5. Feature schemas - Discovery, sensors, artifacts, integrations, etc. 6. 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) 7. Security - Row Level Security policies (24-rls-policies.sql) 8. 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:

1. Violation Seeding (seed_democorp_compliance_violations.sql):
2. Creates assets and certificates with actual violations matching NIST CSF controls

3. 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)

4. Event Triggering (trigger-compliance-evaluation.sh):

5. Publishes AssetChangedEvent events via NATS for all demo tenant assets
6. Triggers compliance engine evaluation

7. Generates findings naturally through the normal evaluation flow

8. Benefits:

9. Findings persist through re-evaluation (not marked INACTIVE)
10. Findings match actual asset violations
11. 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.

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/seed.sql:/docker-entrypoint-initdb.d/02-seed.sql

• Both files are applied automatically by Postgres's own init mechanism on first startup — the container runs every .sql file it finds under docker-entrypoint-initdb.d/ in filename order, so 01-schema.sql runs before 02-seed.sql. No script drives this; nothing else runs at startup.

The consolidated schema.sql file contains all schema definitions and is applied automatically when PostgreSQL initializes a new database. Platform admin users, subscription tiers, measurement templates and the free compliance frameworks are then loaded 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.

The ADR-0016 catalogue migration removes the old algorithms.risk_score database default of 50. Applying it does not re-grade any existing row: explicit scores, old rows that previously received 50, and NULL scores are preserved. New algorithm rows must include a deliberate integer score from 0 through 100 through the catalogue API. An insert-only database guard also rejects direct inserts without one. NULL remains allowed for historical or operator-created rows because the migration has no authoritative evidence with which to grade them; those rows can still receive unrelated corrections until a curator supplies the missing score.

Docker Compose "production-style" stack

A production-style Compose stack (docker-compose.prod.yml — not part of the public distribution; production installs use the Helm chart, covered under EKS/Kubernetes below) applies schema the same way as local dev: mounted at /docker-entrypoint-initdb.d/01-schema.sql on first startup, or manually for an existing database:

# 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

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

Apply Schema

Apply schema.sql directly — there is no bundled RDS migration script in this repository; apply the schema with psql as shown below.

# 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

1. Navigate to RDS Console → Your Database → Query Editor
2. Copy contents of scripts/database/schema.sql
3. 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:

1. Edit scripts/database/schema.sql directly
2. Add new tables, columns, indexes, functions, etc.
3. Maintain proper dependency order (tables before foreign keys, etc.)

4. Use idempotent statements (CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE FUNCTION, etc.)

5. Test the changes

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

6. Apply to existing databases

7. The schema is idempotent, so you can run it on existing databases with psql -f scripts/database/schema.sql (see the RDS section above for connecting to a managed Postgres instance)

Schema Structure (for reference)

The consolidated schema applies migrations in this order:

1. Database initialization - Extensions, types, functions
2. Authentication - Tenants, users, platform users
3. Core platform - Assets, certificates, implementations
4. RBAC - Roles and permissions
5. Features - Discovery, sensors, artifacts, integrations, monitoring
6. Security - Row Level Security policies (REQUIRED for production)
7. 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', 'assets', 'asset_endpoints', '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', 'assets', 'asset_endpoints', '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:

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

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

network_assets_partitioned is not listed above because its source table, network_assets, was dropped outright in the 1.0.0 general asset inventory release (port-as-asset model retired, not migrated) — see the note below.

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:

1. Check service logs for RLS context errors:

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

2. Test tenant isolation:

3. Create test data for different tenants
4. Verify each tenant only sees their own data

5. Verify cross-tenant queries are blocked

6. Monitor performance:

7. Check query performance hasn't degraded
8. Monitor connection pool usage
9. 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

Related Documentation

• Production Checklist - Production deployment checklist