Database schema design for Burning Ash Protocol — tables, relationships, and SQLite/PostgreSQL support.

Database Architecture

BAP uses a relational database with support for both SQLite (development) and PostgreSQL (production).

Database Support

Database	Use Case	Driver
SQLite	Development, small deployments	gorm.io/driver/sqlite
PostgreSQL	Production	gorm.io/driver/postgres (via pgx)

Both use the same schema via GORM's database abstraction. Migrations are maintained in parallel for both dialects (api/migrations/sqlite/ and api/migrations/postgres/).

Schema Overview

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   hosts     │────▶│  survivors  │     │   wills     │
└─────────────┘     └─────────────┘     └─────────────┘
       │                   │                   │
       │                   │                   │
       ▼                   ▼                   ▼
┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│  connectors │     │ will_docs   │     │  storages   │
└─────────────┘     └─────────────┘     └─────────────┘
       │                   │                   │
       │                   ▼                   │
       │            ┌─────────────┐           │
       │            │ liveness    │           │
       │            └─────────────┘           │
       │                   │                   │
       │                   ▼                   │
       │            ┌─────────────┐           │
       │            │ transfers   │           │
       │            └─────────────┘           │
       │                   │                   │
       ▼                   ▼                   ▼
┌─────────────────────────────────────────────────┐
│                   audit_log                    │
└─────────────────────────────────────────────────┘

Tables

hosts

Stores Host accounts.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
email	VARCHAR(255)	UNIQUE, NOT NULL	Email address
password_hash	VARCHAR(255)	NOT NULL	Argon2 hash
display_name	VARCHAR(100)	NOT NULL	Display name
status	VARCHAR(20)	NOT NULL	pending_verification, active, disabled
setup_complete	BOOLEAN	DEFAULT false	Setup wizard complete
master_key_encrypted	BYTEA		Encrypted master key
created_at	TIMESTAMP	NOT NULL	Creation time
updated_at	TIMESTAMP	NOT NULL	Last update

survivors

Stores Survivors for each Host's will.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
host_id	UUID	FK → hosts.id, NOT NULL	Parent host
name	VARCHAR(100)	NOT NULL	Survivor name
relationship	VARCHAR(50)		e.g., spouse, child
contact_encrypted	BYTEA	NOT NULL	Encrypted contact info
sss_share_encrypted	BYTEA	NOT NULL	Encrypted SSS share
personal_message	TEXT		Message for survivor
backup_codes_hash	BYTEA		Hash of backup codes
connector_priority	JSONB	NOT NULL	OTP delivery order
created_at	TIMESTAMP	NOT NULL	Creation time
updated_at	TIMESTAMP	NOT NULL	Last update

wills

Stores will metadata.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
host_id	UUID	FK → hosts.id, NOT NULL	Owner
status	VARCHAR(30)	NOT NULL	draft, active, pending_transfer, etc.
dek_encrypted	BYTEA		Encrypted DEK
threshold	INTEGER	NOT NULL	SSS threshold (K)
storage_id	UUID	FK → storages.id	Primary storage
created_at	TIMESTAMP	NOT NULL	Creation time
sealed_at	TIMESTAMP		When sealed
last_encrypted_at	TIMESTAMP		Last encryption time

will_documents

Stores document metadata.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
will_id	UUID	FK → wills.id, NOT NULL	Parent will
filename	VARCHAR(255)	NOT NULL	Original filename
mime_type	VARCHAR(100)	NOT NULL	MIME type
size_bytes	BIGINT	NOT NULL	File size
sha256_hash	VARCHAR(64)	NOT NULL	SHA-256 hash
storage_path	VARCHAR(500)	NOT NULL	Path in storage
created_at	TIMESTAMP	NOT NULL	Upload time

connectors

Stores connector configurations.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
host_id	UUID	FK → hosts.id, NOT NULL	Owner
type	VARCHAR(20)	NOT NULL	email, sms, whatsapp, telegram
name	VARCHAR(100)	NOT NULL	Display name
config_encrypted	BYTEA	NOT NULL	Encrypted credentials
priority	INTEGER	DEFAULT 0	Send priority
is_verified	BOOLEAN	DEFAULT false	Working status
created_at	TIMESTAMP	NOT NULL	Creation time

storages

Stores storage backend configurations.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
host_id	UUID	FK → hosts.id, NOT NULL	Owner
type	VARCHAR(20)	NOT NULL	gdrive, dropbox, s3, sftp
name	VARCHAR(100)	NOT NULL	Display name
config_encrypted	BYTEA	NOT NULL	Encrypted credentials
directory_path	VARCHAR(255)	NOT NULL	Storage folder
is_connected	BOOLEAN	DEFAULT true	Connection status
last_verified_at	TIMESTAMP		Last successful test
created_at	TIMESTAMP	NOT NULL	Creation time

liveness_checks

Stores liveness check history.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
will_id	UUID	FK → wills.id, NOT NULL	Parent will
check_number	INTEGER	NOT NULL	Sequential check number
status	VARCHAR(20)	NOT NULL	pending, confirmed, missed
channel	VARCHAR(20)		Connector used
sent_at	TIMESTAMP	NOT NULL	When sent
responded_at	TIMESTAMP		When responded
response_time_ms	INTEGER		Response duration

transfers

Stores transfer state.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
will_id	UUID	FK → wills.id, NOT NULL	Parent will
status	VARCHAR(30)	NOT NULL	initiated, awaiting_auth, etc.
initiated_by	UUID	FK → survivors.id	Who initiated
initiated_at	TIMESTAMP	NOT NULL	Start time
host_cancel_deadline	TIMESTAMP		Deadline to cancel
completed_at	TIMESTAMP		When completed
expires_at	TIMESTAMP		Access window expiry

survivor_auth

Stores survivor authentication state during transfer.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
transfer_id	UUID	FK → transfers.id, NOT NULL	Parent transfer
survivor_id	UUID	FK → survivors.id, NOT NULL	Survivor
status	VARCHAR(20)	NOT NULL	pending, verified, failed
otp_session_id	UUID		Current OTP session
authenticated_at	TIMESTAMP		When verified

otp_sessions

Stores active OTP sessions.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
survivor_id	UUID	FK → survivors.id, NOT NULL	Target survivor
code_hash	VARCHAR(64)	NOT NULL	Hash of code
expires_at	TIMESTAMP	NOT NULL	Expiration time
attempts	INTEGER	DEFAULT 0	Attempt count
created_at	TIMESTAMP	NOT NULL	Creation time

audit_log

Append-only audit log.

Column	Type	Constraints	Description
id	UUID	PK	Unique ID
host_id	UUID	FK → hosts.id	Affected host
action	VARCHAR(50)	NOT NULL	Action type
resource_type	VARCHAR(30)	NOT NULL	Resource type
resource_id	UUID		Resource ID
details	JSONB		Additional details
ip_address	VARCHAR(45)		Client IP
created_at	TIMESTAMP	NOT NULL	Timestamp

Indexes

Key indexes for performance:

-- hosts
CREATE INDEX idx_hosts_email ON hosts(email);

-- survivors
CREATE INDEX idx_survivors_host ON survivors(host_id);

-- wills
CREATE INDEX idx_wills_host ON wills(host_id);
CREATE INDEX idx_wills_status ON wills(status);

-- liveness_checks
CREATE INDEX idx_liveness_will ON liveness_checks(will_id);
CREATE INDEX idx_liveness_will_status ON liveness_checks(will_id, status);

-- transfers
CREATE INDEX idx_transfers_will ON transfers(will_id);
CREATE INDEX idx_transfers_status ON transfers(status);

-- audit_log
CREATE INDEX idx_audit_host ON audit_log(host_id);
CREATE INDEX idx_audit_created ON audit_log(created_at);

Migrations

Using sqlc with embedded migrations:

Versioned migrations (001_, 002_, etc.)
Up/down migration pairs
Automatic execution on startup

Security

Encryption at Rest

Connector configs encrypted with master key
Storage credentials encrypted with master key
Contact info encrypted with host key
SSS shares encrypted per-survivor

Sensitive Data

Data	Encrypted	Notes
Password hash	N/A	Argon2, salted
Master key	N/A	Never stored, used from env
DEK	✅	Encrypted with KEK
Connector config	✅	Encrypted with master key
Storage config	✅	Encrypted with master key
Contact info	✅	Encrypted with host key
SSS shares	✅	Encrypted per-survivor
OTP codes	N/A	Hash only
Backup codes	N/A	Hash only

Next Steps

Connector Architecture — Notification system
Storage Architecture — Storage providers
Deployment - Docker Compose — Running the database

Database Architecture

On this page