Database

MemGhost uses a single PostgreSQL 15+ database for both the event store and read models.

Connection

The default connection string (set via DATABASE_URL):

postgres://memghost:memghost@db:5432/memghost?sslmode=disable

In Docker Compose, the db hostname resolves to the PostgreSQL container on the compose stack’s internal network. From a devcontainer, use host.docker.internal:5432 instead.

Migrations

Automatic (Docker)

Migrations run automatically when the app container starts:

migrate -path /workspace/migrations -database "$DATABASE_URL" up

No manual intervention is needed for normal deployments.

Manual

If running outside Docker or if you need to manage migrations directly:

# Apply all pending migrations
task migrate:up

# Rollback the last migration
task migrate:down

# Create a new migration
task migrate:create -- add_shopping_lists

# Force a specific version (use with care)
task migrate:force -- 19

Migration Files

Migrations live in migrations/ and are numbered sequentially:

migrations/
├── 000001_create_event_store.up.sql
├── 000001_create_event_store.down.sql
├── 000002_create_note_views.up.sql
├── 000002_create_note_views.down.sql
├── ...

Each migration has an up (apply) and down (rollback) file.

Schema Overview

Event Store Tables

Table	Purpose
`events`	Immutable event log with aggregate versioning
`snapshots`	Performance cache for large aggregates

Read Model Tables

Read model tables are created by migrations and maintained by projections. Each module manages its own read models. Examples:

vault_items — canonical item store
note_views — denormalized note data for fast queries
hub_nodes — hub page content and metadata

Key Indexes

The event store has indexes on:

(aggregate_id, aggregate_version) — unique constraint for optimistic concurrency
(event_type, occurred_at) — efficient event type queries
(correlation_id) — workflow tracing

Resetting the Database

To wipe all data and start fresh:

# Via Taskfile
task db:reset

# Via Docker Compose (removes the volume)
docker compose -f docker-compose.test.yml down -v
docker compose -f docker-compose.test.yml up -d

Backups

Back up the PostgreSQL data regularly:

# Dump the database
docker compose -f docker-compose.test.yml exec db \
  pg_dump -U memghost memghost > backup.sql

# Restore
docker compose -f docker-compose.test.yml exec -T db \
  psql -U memghost memghost < backup.sql

Or back up the Docker volume directly:

docker run --rm -v postgres-test-data:/data -v $(pwd):/backup \
  alpine tar czf /backup/pg-backup.tar.gz /data