Database Migrations

PostgreSQL database migrations for the RCM system, including schema definitions, reference data, and demo seed data.

The migrations can run against any PostgreSQL database - local Docker containers for development, or production databases like AWS RDS.

Quick Start (Local Development)

For local development, use Docker to run PostgreSQL:

# 1. Start PostgreSQL (port 5432)
docker-compose up -d

# 2. Create environment file
cp packages/migrations/.env.example packages/migrations/.env

# 3. Install dependencies
yarn install

# 4. Build and run migrations
yarn workspace @rcm/migrations build
yarn workspace @rcm/migrations migrate:up

# 5. Seed demo data (optional - for development only)
yarn workspace @rcm/migrations seed

# 6. Verify setup
docker exec -it rcm-postgres psql -U rcm_admin -d rcmdb -c "\dt"

Local Connection Details:

Property	Value
Host	localhost:5432
Database	rcmdb
Username	rcm_admin
Password	rcm_password
DATABASE_URL	postgresql://rcm_admin:rcm_password@localhost:5432/rcmdb

Optional - pgAdmin UI (local only):

docker-compose --profile tools up -d pgadmin
# Access at http://localhost:5050 (admin@example.com / admin)

Production Deployment

To run migrations against a production database (e.g., AWS RDS), set the DATABASE_URL environment variable and run the migration commands:

# Set connection string to your production database
export DATABASE_URL="postgresql://user:password@your-rds-endpoint.amazonaws.com:5432/rcmdb"

# Build and run migrations
yarn workspace @rcm/migrations build
yarn workspace @rcm/migrations migrate:up

caution

Do not run the seed command against production databases. Seed data is for development and testing only.

See the Infrastructure docs for deploying the database with AWS CDK.

Schema Overview

The database tracks the complete healthcare revenue cycle workflow, from claim submission through payment collection or resolution.

Core Entities

Table	Description
payers	Insurance companies that pay for medical services
claims	Billing requests submitted for reimbursement
denials	Rejections by payers refusing to pay claims
appeals	Formal requests to reconsider denied claims
write_offs	Decisions to stop pursuing payment
rebills	Corrected claims resubmitted after denial
payment_variances	Discrepancies between expected and actual payments

Reference Tables

Table	Description
denial_code_library	Standard denial codes with meanings and recommended actions
org_policies	Configurable business rules for handling denials
coding_rules	Pre-submission validation rules to prevent denials

Schema Diagram

Data Flow Examples

Denial → Appeal (Authorization Issue)

1. Claim Submission: Claim CLM-2024-001235 created for $500 MRI
2. Denial Received: CO-197 (authorization absent), denying $500
3. Appeal Filed: First-level appeal with supporting documents
4. Outcome: Pending review, assigned to staff member

Denial → Write-off (Small Balance)

1. Claim Submission: Claim CLM-2024-001236 created for $15
2. Denial Received: PR-1 (patient responsibility)
3. Write-off Created: Amount below $25 threshold
4. Outcome: Written off per organization policy

Denial → Rebill (Coding Error)

1. Claim Submission: Claim CLM-2024-001237 for $250, missing modifier
2. Denial Received: CO-4 (modifier error)
3. Rebill Created: Corrected claim with modifier 25 added
4. Outcome: New claim paid $200

Managing Migrations

# Apply all pending migrations
yarn workspace @rcm/migrations migrate:up

# Rollback last migration
yarn workspace @rcm/migrations migrate:down

# Create new migration
yarn workspace @rcm/migrations migrate:create migration-name

Seeded Reference Data

Denial Codes

Common codes seeded in denial_code_library:

• CO-197: Authorization missing (65% appeal success rate)
• CO-4: Modifier error (rebill recommended)
• CO-50: Medical necessity (appeal with clinical notes)
• CO-97: Bundling issue (review NCCI edits)
• PR-1/PR-2: Patient responsibility (verify eligibility)

Organization Policies

Default policies in org_policies:

• Appeal thresholds ($100+ default)
• Authorization denials auto-appeal ($250+)
• Small balance write-offs (under $25)
• Coding errors route to rebill queue

Coding Rules

Validation rules in coding_rules:

• Modifier 25 required with E/M codes on same day as procedure
• Bilateral procedure modifier 50
• Screening colonoscopy diagnosis requirements
• Invalid diagnosis code detection

Database Management

# Stop database (preserves data)
docker-compose down

# Start database
docker-compose up -d

# Reset database (deletes all data)
docker exec rcm-postgres psql -U rcm_admin -d rcmdb \
  -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public; CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";"
yarn workspace @rcm/migrations build && yarn workspace @rcm/migrations migrate:up

Troubleshooting

Issue	Solution
DATABASE_URL not set	Create .env file with correct DATABASE_URL
Directory not found	Run yarn workspace @rcm/migrations build first
Unknown file extension .ts/.d.ts	Config should use "migration-file-language": "js"
Relation already exists	Reset database schema (see above)

Using in Application Code

import { getDbClient } from '@rcm/migrations'

const client = await getDbClient()
const result = await client.query('SELECT * FROM claims WHERE claim_id = $1', [
  claimId,
])

Next Steps

• MCP Server - Use the database with AI-powered tools
• Workflows - See how data flows through the system