nChat v0.4.0 Database Migration Guide

Version: 0.4.0 Migration Date: January 30, 2026 Database Version: PostgreSQL 14+

Overview

This guide provides detailed information about the database schema changes in v0.4.0 and step-by-step instructions for migrating your database from v0.3.0 to v0.4.0.

Migration Summary

New Tables: 8 (E2EE system)
Modified Tables: 2 (messages, channels)
New Indexes: 15
New Materialized Views: 1
Migration Files: 4 SQL files
Estimated Duration: 2-5 minutes for typical database sizes
Downtime Required: No (migrations are additive only)
Rollback Support: Yes (see Rollback section)

Migration Files
New Database Tables
Table Modifications
Indexes and Constraints
Migration Steps
Verification
Performance Impact
Rollback Procedures
Troubleshooting

Migration Files

Migration File List

File	Purpose	Tables Affected	Size
`013_e2ee_system.sql`	E2EE infrastructure (draft)	7 new tables	~18 KB
`014_e2ee_system.sql`	E2EE infrastructure (final)	8 new tables	~25 KB
`015_voice_calls.sql`	Voice calling support	3 new tables	~17 KB
`016_live_streaming.sql`	Live streaming support	2 new tables	~21 KB

Primary Migration: 014_e2ee_system.sql (most relevant for v0.4.0)

Migration Order

Migrations are applied in numerical order:

013_e2ee_system.sql (if present)
014_e2ee_system.sql ✅ Primary E2EE migration
015_voice_calls.sql
016_live_streaming.sql

New Database Tables

1. nchat_user_master_keys

Purpose: Store encrypted master keys for each user's E2EE setup.

Schema:

CREATE TABLE nchat_user_master_keys (
    user_id UUID PRIMARY KEY REFERENCES nchat_users(id) ON DELETE CASCADE,
    master_key_salt BYTEA NOT NULL,  -- 32 bytes
    pbkdf2_iterations INTEGER NOT NULL DEFAULT 100000,
    recovery_code_hash TEXT NOT NULL,  -- SHA-256 hash of recovery code
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_user_master_keys_user_id ON nchat_user_master_keys(user_id);

Relationships:

One-to-one with nchat_users

Storage Estimate: ~100 bytes per user with E2EE enabled

2. nchat_identity_keys

Purpose: Store device identity keys (one per device per user).

Schema:

CREATE TABLE nchat_identity_keys (
    device_id TEXT PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES nchat_users(id) ON DELETE CASCADE,
    identity_key_public BYTEA NOT NULL,  -- 32 bytes (Curve25519)
    identity_key_private_encrypted BYTEA NOT NULL,  -- ~80 bytes (AES-256-GCM encrypted)
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, device_id)
);

CREATE INDEX idx_identity_keys_user_id ON nchat_identity_keys(user_id);
CREATE INDEX idx_identity_keys_device_id ON nchat_identity_keys(device_id);

Relationships:

Many-to-one with nchat_users

Storage Estimate: ~150 bytes per device

3. nchat_signed_prekeys

Purpose: Store signed prekeys (rotated weekly for security).

Schema:

CREATE TABLE nchat_signed_prekeys (
    id SERIAL PRIMARY KEY,
    device_id TEXT NOT NULL REFERENCES nchat_identity_keys(device_id) ON DELETE CASCADE,
    signed_prekey_id INTEGER NOT NULL,
    signed_prekey_public BYTEA NOT NULL,  -- 32 bytes
    signed_prekey_private_encrypted BYTEA NOT NULL,  -- ~80 bytes
    signature BYTEA NOT NULL,  -- 64 bytes (Ed25519)
    rotation_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(device_id, signed_prekey_id)
);

CREATE INDEX idx_signed_prekeys_device_id ON nchat_signed_prekeys(device_id);
CREATE INDEX idx_signed_prekeys_active ON nchat_signed_prekeys(device_id, is_active);
CREATE INDEX idx_signed_prekeys_rotation ON nchat_signed_prekeys(rotation_date)
    WHERE is_active = TRUE;

Relationships:

Many-to-one with nchat_identity_keys

Storage Estimate: ~200 bytes per signed prekey (1-2 per device)

4. nchat_one_time_prekeys

Purpose: Store one-time prekeys for perfect forward secrecy.

Schema:

CREATE TABLE nchat_one_time_prekeys (
    id SERIAL PRIMARY KEY,
    device_id TEXT NOT NULL REFERENCES nchat_identity_keys(device_id) ON DELETE CASCADE,
    prekey_id INTEGER NOT NULL,
    prekey_public BYTEA NOT NULL,  -- 32 bytes
    prekey_private_encrypted BYTEA NOT NULL,  -- ~80 bytes
    is_consumed BOOLEAN NOT NULL DEFAULT FALSE,
    consumed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(device_id, prekey_id)
);

CREATE INDEX idx_one_time_prekeys_device_id ON nchat_one_time_prekeys(device_id);
CREATE INDEX idx_one_time_prekeys_available ON nchat_one_time_prekeys(device_id, is_consumed)
    WHERE is_consumed = FALSE;
CREATE INDEX idx_one_time_prekeys_consumed ON nchat_one_time_prekeys(consumed_at)
    WHERE is_consumed = TRUE;

Relationships:

Many-to-one with nchat_identity_keys

Storage Estimate: ~150 bytes per prekey (100 per device initially)

5. nchat_signal_sessions

Purpose: Store active Signal Protocol sessions (Double Ratchet state).

Schema:

CREATE TABLE nchat_signal_sessions (
    id SERIAL PRIMARY KEY,
    device_id TEXT NOT NULL REFERENCES nchat_identity_keys(device_id) ON DELETE CASCADE,
    peer_user_id UUID NOT NULL REFERENCES nchat_users(id) ON DELETE CASCADE,
    peer_device_id TEXT NOT NULL,
    session_state_encrypted BYTEA NOT NULL,  -- Encrypted Double Ratchet state (~500 bytes)
    is_initiator BOOLEAN NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    last_message_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(device_id, peer_user_id, peer_device_id)
);

CREATE INDEX idx_signal_sessions_device_id ON nchat_signal_sessions(device_id);
CREATE INDEX idx_signal_sessions_peer ON nchat_signal_sessions(peer_user_id, peer_device_id);
CREATE INDEX idx_signal_sessions_active ON nchat_signal_sessions(device_id, is_active);
CREATE INDEX idx_signal_sessions_last_message ON nchat_signal_sessions(last_message_at)
    WHERE is_active = TRUE;

Relationships:

Many-to-one with nchat_identity_keys (local device)
Many-to-one with nchat_users (peer user)

Storage Estimate: ~600 bytes per active session

6. nchat_safety_numbers

Purpose: Store verified safety numbers for identity verification.

Schema:

CREATE TABLE nchat_safety_numbers (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES nchat_users(id) ON DELETE CASCADE,
    peer_user_id UUID NOT NULL REFERENCES nchat_users(id) ON DELETE CASCADE,
    safety_number TEXT NOT NULL,  -- 60 digits
    verified_at TIMESTAMP WITH TIME ZONE,
    verification_method TEXT,  -- 'manual', 'qr_code', 'out_of_band'
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, peer_user_id),
    CHECK (LENGTH(safety_number) = 60),
    CHECK (verification_method IN ('manual', 'qr_code', 'out_of_band', NULL))
);

CREATE INDEX idx_safety_numbers_user_id ON nchat_safety_numbers(user_id);
CREATE INDEX idx_safety_numbers_peer_user_id ON nchat_safety_numbers(peer_user_id);
CREATE INDEX idx_safety_numbers_verified ON nchat_safety_numbers(user_id, verified_at)
    WHERE verified_at IS NOT NULL;

Relationships:

Many-to-one with nchat_users (both user_id and peer_user_id)

Storage Estimate: ~150 bytes per verified contact pair

7. nchat_e2ee_audit_log

Purpose: Security event logging for E2EE operations (metadata only).

Schema:

CREATE TABLE nchat_e2ee_audit_log (
    id SERIAL PRIMARY KEY,
    event_type TEXT NOT NULL,  -- 'master_key_created', 'device_keys_generated', etc.
    user_id UUID REFERENCES nchat_users(id) ON DELETE SET NULL,
    device_id TEXT,
    metadata JSONB,  -- Additional event metadata (no sensitive data)
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    CHECK (event_type IN (
        'master_key_created',
        'device_keys_generated',
        'session_established',
        'session_destroyed',
        'signed_prekey_rotated',
        'prekeys_replenished',
        'safety_number_verified',
        'safety_number_changed',
        'recovery_code_used'
    ))
);

CREATE INDEX idx_e2ee_audit_log_user_id ON nchat_e2ee_audit_log(user_id);
CREATE INDEX idx_e2ee_audit_log_device_id ON nchat_e2ee_audit_log(device_id);
CREATE INDEX idx_e2ee_audit_log_event_type ON nchat_e2ee_audit_log(event_type);
CREATE INDEX idx_e2ee_audit_log_created_at ON nchat_e2ee_audit_log(created_at DESC);

Relationships:

Many-to-one with nchat_users (optional, ON DELETE SET NULL)

Storage Estimate: ~200 bytes per audit log entry

8. nchat_prekey_bundles (Materialized View)

Purpose: Optimized view for fetching complete prekey bundles efficiently.

Schema:

CREATE MATERIALIZED VIEW nchat_prekey_bundles AS
SELECT
    ik.user_id,
    ik.device_id,
    ik.identity_key_public,
    sp.signed_prekey_id,
    sp.signed_prekey_public,
    sp.signature,
    otp.prekey_id AS one_time_prekey_id,
    otp.prekey_public AS one_time_prekey_public
FROM nchat_identity_keys ik
JOIN nchat_signed_prekeys sp ON ik.device_id = sp.device_id AND sp.is_active = TRUE
LEFT JOIN LATERAL (
    SELECT prekey_id, prekey_public
    FROM nchat_one_time_prekeys
    WHERE device_id = ik.device_id AND is_consumed = FALSE
    ORDER BY created_at ASC
    LIMIT 1
) otp ON TRUE;

CREATE UNIQUE INDEX idx_prekey_bundles_device ON nchat_prekey_bundles(device_id);
CREATE INDEX idx_prekey_bundles_user ON nchat_prekey_bundles(user_id);

-- Refresh function (called automatically when keys change)
CREATE OR REPLACE FUNCTION refresh_prekey_bundles()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY nchat_prekey_bundles;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Triggers to auto-refresh materialized view
CREATE TRIGGER refresh_prekey_bundles_on_identity_key
    AFTER INSERT OR UPDATE OR DELETE ON nchat_identity_keys
    FOR EACH STATEMENT EXECUTE FUNCTION refresh_prekey_bundles();

CREATE TRIGGER refresh_prekey_bundles_on_signed_prekey
    AFTER INSERT OR UPDATE OR DELETE ON nchat_signed_prekeys
    FOR EACH STATEMENT EXECUTE FUNCTION refresh_prekey_bundles();

CREATE TRIGGER refresh_prekey_bundles_on_one_time_prekey
    AFTER INSERT OR UPDATE ON nchat_one_time_prekeys
    FOR EACH STATEMENT EXECUTE FUNCTION refresh_prekey_bundles();

Purpose: Dramatically improves performance when fetching prekey bundles for session establishment.

Table Modifications

1. nchat_messages

Changes:

-- Add E2EE support columns
ALTER TABLE nchat_messages
    ADD COLUMN IF NOT EXISTS is_encrypted BOOLEAN NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS encrypted_payload BYTEA,
    ADD COLUMN IF NOT EXISTS sender_device_id TEXT;

-- Add foreign key constraint
ALTER TABLE nchat_messages
    ADD CONSTRAINT fk_messages_sender_device
    FOREIGN KEY (sender_device_id) REFERENCES nchat_identity_keys(device_id)
    ON DELETE SET NULL;

-- Add index for encrypted messages
CREATE INDEX idx_messages_encrypted ON nchat_messages(channel_id, is_encrypted)
    WHERE is_encrypted = TRUE;

-- Add index for device messages
CREATE INDEX idx_messages_sender_device ON nchat_messages(sender_device_id)
    WHERE sender_device_id IS NOT NULL;

Impact:

No data loss
Existing messages: is_encrypted = FALSE by default
New messages: Can be encrypted if E2EE enabled
Storage increase: ~4 bytes per message (boolean) + variable (encrypted payload)

2. nchat_channels

Changes:

-- Add E2EE enforcement option
ALTER TABLE nchat_channels
    ADD COLUMN IF NOT EXISTS enforce_encryption BOOLEAN NOT NULL DEFAULT FALSE;

-- Add index for encrypted channels
CREATE INDEX idx_channels_enforce_encryption ON nchat_channels(enforce_encryption)
    WHERE enforce_encryption = TRUE;

Impact:

No data loss
Existing channels: enforce_encryption = FALSE by default
Administrators can enable per-channel encryption enforcement
Storage increase: 1 byte per channel

Indexes and Constraints

New Indexes (15 total)

idx_user_master_keys_user_id - Fast lookup of master keys
idx_identity_keys_user_id - User device lookup
idx_identity_keys_device_id - Device identity lookup
idx_signed_prekeys_device_id - Device signed prekeys
idx_signed_prekeys_active - Active signed prekeys only
idx_signed_prekeys_rotation - Prekeys needing rotation
idx_one_time_prekeys_device_id - Device one-time prekeys
idx_one_time_prekeys_available - Available (unconsumed) prekeys
idx_one_time_prekeys_consumed - Cleanup of consumed prekeys
idx_signal_sessions_device_id - Device sessions
idx_signal_sessions_peer - Peer sessions
idx_signal_sessions_active - Active sessions only
idx_signal_sessions_last_message - Session activity tracking
idx_safety_numbers_user_id - User safety numbers
idx_safety_numbers_verified - Verified safety numbers only

Foreign Key Constraints

nchat_user_master_keys.user_id → nchat_users.id (CASCADE)
nchat_identity_keys.user_id → nchat_users.id (CASCADE)
nchat_signed_prekeys.device_id → nchat_identity_keys.device_id (CASCADE)
nchat_one_time_prekeys.device_id → nchat_identity_keys.device_id (CASCADE)
nchat_signal_sessions.device_id → nchat_identity_keys.device_id (CASCADE)
nchat_signal_sessions.peer_user_id → nchat_users.id (CASCADE)
nchat_safety_numbers.user_id → nchat_users.id (CASCADE)
nchat_safety_numbers.peer_user_id → nchat_users.id (CASCADE)
nchat_messages.sender_device_id → nchat_identity_keys.device_id (SET NULL)

Migration Steps

Prerequisites

Backup Database:

cd .backend
docker exec -t nself-postgres pg_dump -U postgres nchat > backup_pre_v0.4.0.sql

Verify Current Version:

docker exec -t nself-postgres psql -U postgres -d nchat -c "
  SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 1;
"

Check Disk Space:
```
df -h
# Ensure at least 1GB free space
```

Step 1: Review Migration SQL

# Review E2EE migration
cd .backend
cat migrations/014_e2ee_system.sql | less

# Check for any custom modifications needed
# (typically none required)

Step 2: Dry Run (Optional)

# Create a test database
docker exec -t nself-postgres psql -U postgres -c "CREATE DATABASE nchat_test;"

# Restore backup to test database
docker exec -i nself-postgres psql -U postgres -d nchat_test < backup_pre_v0.4.0.sql

# Run migration on test database
docker exec -t nself-postgres psql -U postgres -d nchat_test -f migrations/014_e2ee_system.sql

# Verify test database
docker exec -t nself-postgres psql -U postgres -d nchat_test -c "
  SELECT table_name FROM information_schema.tables
  WHERE table_schema = 'public' AND table_name LIKE '%e2ee%'
  ORDER BY table_name;
"

# Drop test database
docker exec -t nself-postgres psql -U postgres -c "DROP DATABASE nchat_test;"

Step 3: Run Production Migration

# Using nself CLI (recommended)
cd .backend
nself db migrate up

# Expected output:
# ✓ Running migration: 014_e2ee_system.sql
# ✓ Migration completed successfully in 3.42s

Manual Method (if nself CLI unavailable):

docker exec -i nself-postgres psql -U postgres -d nchat < migrations/014_e2ee_system.sql

Step 4: Verify Migration

# Check new tables exist
docker exec -t nself-postgres psql -U postgres -d nchat -c "
  SELECT table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
  FROM information_schema.tables
  WHERE table_schema = 'public'
    AND table_name IN (
      'nchat_user_master_keys',
      'nchat_identity_keys',
      'nchat_signed_prekeys',
      'nchat_one_time_prekeys',
      'nchat_signal_sessions',
      'nchat_safety_numbers',
      'nchat_e2ee_audit_log'
    )
  ORDER BY table_name;
"

# Expected output: 7 tables listed with sizes

Step 5: Update Schema Migrations Table

The migration script automatically updates the schema_migrations table:

# Verify migration was recorded
docker exec -t nself-postgres psql -U postgres -d nchat -c "
  SELECT version, executed_at
  FROM schema_migrations
  WHERE version >= 14
  ORDER BY version DESC
  LIMIT 5;
"

Verification

1. Table Count Verification

SELECT
    COUNT(*) FILTER (WHERE table_name LIKE 'nchat_user_master_keys') AS master_keys,
    COUNT(*) FILTER (WHERE table_name LIKE 'nchat_identity_keys') AS identity_keys,
    COUNT(*) FILTER (WHERE table_name LIKE 'nchat_signed_prekeys') AS signed_prekeys,
    COUNT(*) FILTER (WHERE table_name LIKE 'nchat_one_time_prekeys') AS one_time_prekeys,
    COUNT(*) FILTER (WHERE table_name LIKE 'nchat_signal_sessions') AS sessions,
    COUNT(*) FILTER (WHERE table_name LIKE 'nchat_safety_numbers') AS safety_numbers,
    COUNT(*) FILTER (WHERE table_name LIKE 'nchat_e2ee_audit_log') AS audit_log
FROM information_schema.tables
WHERE table_schema = 'public';

Expected: All counts = 1

2. Column Verification

-- Check nchat_messages modifications
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'nchat_messages'
    AND column_name IN ('is_encrypted', 'encrypted_payload', 'sender_device_id')
ORDER BY column_name;

-- Check nchat_channels modifications
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'nchat_channels'
    AND column_name = 'enforce_encryption';

3. Index Verification

SELECT
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
    AND tablename LIKE '%e2ee%'
    OR tablename IN ('nchat_messages', 'nchat_channels')
ORDER BY tablename, indexname;

Expected: 15+ indexes

4. Foreign Key Verification

SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name,
    rc.delete_rule
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints AS rc
    ON rc.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_name LIKE '%e2ee%'
    OR tc.table_name LIKE '%identity_keys%'
    OR tc.table_name LIKE '%prekey%'
    OR tc.table_name LIKE '%signal_session%'
ORDER BY tc.table_name, kcu.column_name;

5. Data Integrity Verification

-- Verify existing messages are unaffected
SELECT
    COUNT(*) AS total_messages,
    COUNT(*) FILTER (WHERE is_encrypted = FALSE) AS unencrypted_messages,
    COUNT(*) FILTER (WHERE is_encrypted = TRUE) AS encrypted_messages
FROM nchat_messages;

-- Verify existing channels are unaffected
SELECT
    COUNT(*) AS total_channels,
    COUNT(*) FILTER (WHERE enforce_encryption = FALSE) AS non_enforced,
    COUNT(*) FILTER (WHERE enforce_encryption = TRUE) AS enforced
FROM nchat_channels;

Expected: All existing messages unencrypted, all existing channels non-enforced

Performance Impact

Migration Duration

Estimated migration time based on database size:

Database Size	Estimated Duration
< 1GB	1-2 minutes
1-5GB	2-3 minutes
5-10GB	3-5 minutes
10-50GB	5-10 minutes
> 50GB	10-20 minutes

Storage Impact

Additional storage required:

Component	Per Record	For 1,000 Users
Master Keys	~100 bytes	~100 KB
Identity Keys	~150 bytes/device	~150 KB (1 device/user)
Signed Prekeys	~200 bytes/device	~200 KB
One-Time Prekeys	~150 bytes × 100	~15 MB (100 keys/device)
Sessions	~600 bytes/session	~600 KB (1 session/user)
Safety Numbers	~150 bytes/pair	Varies by usage
Audit Log	~200 bytes/event	Varies by usage

Total: ~16 MB per 1,000 users (plus encrypted message payloads)

Query Performance

New indexes ensure E2EE operations remain fast:

Operation	Typical Duration	Index Used
Fetch prekey bundle	< 10ms	`idx_prekey_bundles_device`
Check session exists	< 5ms	`idx_signal_sessions_active`
Get available prekeys	< 10ms	`idx_one_time_prekeys_available`
Verify safety number	< 5ms	`idx_safety_numbers_verified`

Rollback Procedures

Option 1: Restore from Backup (Recommended)

# Stop application
cd .backend
nself stop

# Drop current database
docker exec -t nself-postgres psql -U postgres -c "DROP DATABASE nchat;"

# Recreate database
docker exec -t nself-postgres psql -U postgres -c "CREATE DATABASE nchat;"

# Restore backup
docker exec -i nself-postgres psql -U postgres -d nchat < backup_pre_v0.4.0.sql

# Restart services
nself start

Option 2: Manual Rollback (Advanced)

-- Drop E2EE tables in reverse dependency order
DROP MATERIALIZED VIEW IF EXISTS nchat_prekey_bundles CASCADE;
DROP TABLE IF EXISTS nchat_e2ee_audit_log CASCADE;
DROP TABLE IF EXISTS nchat_safety_numbers CASCADE;
DROP TABLE IF EXISTS nchat_signal_sessions CASCADE;
DROP TABLE IF EXISTS nchat_one_time_prekeys CASCADE;
DROP TABLE IF EXISTS nchat_signed_prekeys CASCADE;
DROP TABLE IF EXISTS nchat_identity_keys CASCADE;
DROP TABLE IF EXISTS nchat_user_master_keys CASCADE;

-- Remove columns from nchat_messages
ALTER TABLE nchat_messages
    DROP COLUMN IF EXISTS is_encrypted,
    DROP COLUMN IF EXISTS encrypted_payload,
    DROP COLUMN IF EXISTS sender_device_id;

-- Remove column from nchat_channels
ALTER TABLE nchat_channels
    DROP COLUMN IF EXISTS enforce_encryption;

-- Delete migration record
DELETE FROM schema_migrations WHERE version = 14;

Verify Rollback

-- Ensure E2EE tables are gone
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
    AND table_name LIKE '%e2ee%';

-- Should return no results

Troubleshooting

Issue 1: Migration Fails with Foreign Key Error

Error: ERROR: foreign key constraint "fk_identity_keys_user" violates foreign key constraint

Solution:

-- Check for orphaned data
SELECT user_id FROM nchat_identity_keys
WHERE user_id NOT IN (SELECT id FROM nchat_users);

-- If found, delete orphaned records
DELETE FROM nchat_identity_keys
WHERE user_id NOT IN (SELECT id FROM nchat_users);

-- Retry migration

Issue 2: Materialized View Creation Fails

Error: ERROR: could not create unique index "idx_prekey_bundles_device"

Solution:

-- Check for duplicate device_ids
SELECT device_id, COUNT(*)
FROM nchat_identity_keys
GROUP BY device_id
HAVING COUNT(*) > 1;

-- If duplicates found, delete or update them
-- Then retry migration

Issue 3: Out of Disk Space

Error: ERROR: could not extend file "base/...": No space left on device

Solution:

# Check disk usage
df -h

# Free up space
docker system prune -a

# Or increase disk size
# Then retry migration

Issue 4: Migration Timeout

Error: ERROR: canceling statement due to statement timeout

Solution:

-- Increase statement timeout temporarily
SET statement_timeout = '10min';

-- Then retry migration
\i migrations/014_e2ee_system.sql

-- Reset timeout
RESET statement_timeout;

Issue 5: Permission Denied

Error: ERROR: permission denied for table nchat_users

Solution:

# Ensure you're running as postgres user
docker exec -u postgres -t nself-postgres psql -d nchat -f migrations/014_e2ee_system.sql

Post-Migration Maintenance

Vacuum and Analyze

After migration, optimize the database:

-- Vacuum new tables
VACUUM ANALYZE nchat_user_master_keys;
VACUUM ANALYZE nchat_identity_keys;
VACUUM ANALYZE nchat_signed_prekeys;
VACUUM ANALYZE nchat_one_time_prekeys;
VACUUM ANALYZE nchat_signal_sessions;
VACUUM ANALYZE nchat_safety_numbers;
VACUUM ANALYZE nchat_e2ee_audit_log;

-- Vacuum modified tables
VACUUM ANALYZE nchat_messages;
VACUUM ANALYZE nchat_channels;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY nchat_prekey_bundles;

Monitor Index Usage

After a week of production use:

-- Check index usage statistics
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename LIKE '%e2ee%'
    OR tablename IN ('nchat_messages', 'nchat_channels')
ORDER BY idx_scan DESC;

Set Up Automated Cleanup

Clean up old audit logs periodically:

-- Delete audit logs older than 90 days
DELETE FROM nchat_e2ee_audit_log
WHERE created_at < NOW() - INTERVAL '90 days';

-- Clean up consumed prekeys older than 30 days
DELETE FROM nchat_one_time_prekeys
WHERE is_consumed = TRUE
    AND consumed_at < NOW() - INTERVAL '30 days';

Summary

After completing the migration:

✅ 8 new tables created for E2EE
✅ 2 existing tables modified (additive only)
✅ 15 new indexes for optimal performance
✅ 1 materialized view for efficient queries
✅ All data preserved (no data loss)
✅ Zero downtime (migrations are additive)
✅ Rollback supported (restore from backup)

Next Steps:

Verify migration with checklist above
Test E2EE functionality in application
Monitor performance and index usage
Set up automated cleanup jobs

For further assistance, see:

v0.4.0 MIGRATION GUIDE - nself-org/nchat GitHub Wiki

nChat v0.4.0 Database Migration Guide

Overview

Migration Summary

Table of Contents

Migration Files

Migration File List

Migration Order

New Database Tables

1. nchat_user_master_keys

2. nchat_identity_keys

3. nchat_signed_prekeys

4. nchat_one_time_prekeys

5. nchat_signal_sessions

6. nchat_safety_numbers

7. nchat_e2ee_audit_log

8. nchat_prekey_bundles (Materialized View)

Table Modifications

1. nchat_messages

2. nchat_channels

Indexes and Constraints

New Indexes (15 total)

Foreign Key Constraints

Migration Steps

Prerequisites

Step 1: Review Migration SQL

Step 2: Dry Run (Optional)

Step 3: Run Production Migration

Step 4: Verify Migration

Step 5: Update Schema Migrations Table

Verification

1. Table Count Verification

2. Column Verification

3. Index Verification

4. Foreign Key Verification

5. Data Integrity Verification

Performance Impact

Migration Duration

Storage Impact

Query Performance

Rollback Procedures

Option 1: Restore from Backup (Recommended)

Option 2: Manual Rollback (Advanced)

Verify Rollback

Troubleshooting

Issue 1: Migration Fails with Foreign Key Error

Issue 2: Materialized View Creation Fails

Issue 3: Out of Disk Space

Issue 4: Migration Timeout

Issue 5: Permission Denied

Post-Migration Maintenance

Vacuum and Analyze

Monitor Index Usage

Set Up Automated Cleanup

Summary

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️