Database Schema#

Overview#

Toadist uses PostgreSQL 16+ for persistent storage.

Tables#

users#

Stores user accounts.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  username VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

noots#

Stores noot containers.

CREATE TABLE noots (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  version INTEGER NOT NULL DEFAULT 1,
  checksum VARCHAR(64) NOT NULL,
  data JSONB NOT NULL,
  deleted BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_noots_user_id ON noots(user_id);
CREATE INDEX idx_noots_checksum ON noots(checksum);
CREATE INDEX idx_noots_deleted ON noots(deleted);

refresh_tokens#

Stores active refresh tokens.

CREATE TABLE refresh_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  token VARCHAR(512) NOT NULL,
  expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_refresh_tokens_user_id ON refresh_tokens(user_id);
CREATE INDEX idx_refresh_tokens_token ON refresh_tokens(token);

JSONB Data Structure#

The data column in noots stores:

{
  "activities": [
    {
      "activityType": "todo",
      "properties": {
        "title": "Buy groceries",
        "completed": false,
        "priority": "high"
      },
      "baseProperties": {
        "encrypted": false,
        "syncPriority": "normal"
      }
    }
  ]
}

Migrations#

Migrations run automatically on server startup via DatabaseService.initialize().

Adding New Migrations#

1. Add SQL to DatabaseService._runMigrations()
2. Use IF NOT EXISTS for idempotency
3. Test with fresh database

Queries#

Common Patterns#

// Get user's noots
final noots = await db.getNootsByUser(userId);

// Get noot by ID
final noot = await db.getNoot(nootId);

// Upsert noot
await db.upsertNoot(userId, nootData);

// Get checksums for sync
final checksums = await db.getChecksums(userId);

Backup and Restore#

Creating Backups#

Use the backup_db.sh script to create timestamped, compressed backups:

# Create backup in default 'backups/' directory
./scripts/backup_db.sh

# Create backup in custom directory
./scripts/backup_db.sh /path/to/backup/directory

The script will:

• Automatically detect Docker or local PostgreSQL
• Create a timestamped backup file: toadist_backup_YYYY-MM-DD_HH-MM-SS.sql.gz
• Compress the backup using gzip

Restoring from Backup#

Use the restore_db.sh script to restore from a backup file:

./scripts/restore_db.sh backups/toadist_backup_2026-02-09_23-15-00.sql.gz

The script will:

• Prompt for confirmation before overwriting data
• Automatically detect Docker or local PostgreSQL
• Handle both compressed (.gz) and uncompressed (.sql) files

Best Practices#

• Regular backups: Schedule regular backups using cron or similar tools
• Backup retention: Keep multiple backup versions (daily, weekly, monthly)
• Test restores: Periodically test backup restoration to ensure backups are valid
• Secure storage: Store backups in a secure location separate from the database server
• Before updates: Always create a backup before major updates or migrations