likwid/docs/admin/database.md

Database Administration

Likwid uses PostgreSQL 16+ for data storage.

Connection

# Connection string format
DATABASE_URL=postgres://user:password@host:port/database

# Example
DATABASE_URL=postgres://likwid:secret@localhost:5432/likwid_prod

Migrations

Migrations are managed with SQLx.

Running Migrations

cd backend
export DATABASE_URL="postgres://..."
sqlx migrate run

Checking Status

sqlx migrate info

Creating New Migrations

sqlx migrate add <migration_name>

Key Tables

Table	Purpose
users	User accounts
communities	Organizations
community_members	Membership relationships
proposals	Decision items
proposal_options	Voting options
votes	Cast votes
delegations	Delegation relationships
moderation_log	Audit trail
comments	Discussion comments
topics	Categorization for proposals

Backup

Full Backup

pg_dump -h localhost -U likwid likwid_prod > backup_$(date +%Y%m%d).sql

Automated Backups

Set up a cron job:

0 3 * * * pg_dump -h localhost -U likwid likwid_prod | gzip > /backups/likwid_$(date +\%Y\%m\%d).sql.gz

Restore

psql -h localhost -U likwid likwid_prod < backup.sql

Maintenance

Vacuum

Run periodically to reclaim space:

VACUUM ANALYZE;

Index Maintenance

REINDEX DATABASE likwid_prod;

Connection Monitoring

SELECT * FROM pg_stat_activity WHERE datname = 'likwid_prod';

Performance

Key Indexes

Likwid creates indexes on:

• User lookups (username, email)
• Community slugs
• Proposal status and dates
• Vote relationships

Query Analysis

EXPLAIN ANALYZE SELECT * FROM proposals WHERE community_id = '...';

Security

User Permissions

Create a dedicated database user:

CREATE USER likwid WITH PASSWORD 'strong_password';
GRANT ALL PRIVILEGES ON DATABASE likwid_prod TO likwid;

Connection Limits

ALTER USER likwid CONNECTION LIMIT 50;

SSL Connections

For production, require SSL:

DATABASE_URL=postgres://user:pass@host/db?sslmode=require