# Database Schema

## Overview

Toadist uses PostgreSQL 16+ for persistent storage.

## Tables

### users

Stores user accounts.

```sql
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()
);
```

| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Primary key |
| username | VARCHAR(255) | Unique username |
| email | VARCHAR(255) | Unique email |
| password_hash | VARCHAR(255) | bcrypt hash |
| created_at | TIMESTAMP | Account creation |
| updated_at | TIMESTAMP | Last update |

---

### noots

Stores noot containers.

```sql
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);
```

| Column | Type | Description |
|--------|------|-------------|
| id | UUID | Noot ID (from client) |
| user_id | UUID | Owner reference |
| version | INTEGER | Conflict resolution |
| checksum | VARCHAR(64) | SHA-256 hash |
| data | JSONB | Serialized activities |
| deleted | BOOLEAN | Soft delete flag |
| created_at | TIMESTAMP | Creation time |
| updated_at | TIMESTAMP | Last modification |

---

### refresh_tokens

Stores active refresh tokens.

```sql
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:

```json
{
  "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

```dart
// 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:

```bash
# 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:

```bash
./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

> [!WARNING]
> Restoring a backup will **overwrite all existing data** in the database. Always confirm you have the correct backup file before proceeding.

### 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