# Data Model

## Database Schemas

### Main Database (scratchback-pds/data.sqlite)

#### user_quotas Table
```sql
CREATE TABLE user_quotas (
    did TEXT PRIMARY KEY,
    storage_used_bytes INTEGER NOT NULL DEFAULT 0,
    storage_limit_bytes INTEGER NOT NULL DEFAULT 2147483648,  -- 2GB
    blob_count INTEGER NOT NULL DEFAULT 0,
    blob_limit INTEGER NOT NULL DEFAULT 1000,
    country_code TEXT,
    storage_backend TEXT,
    storage_region TEXT,
    warning_sent INTEGER DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_quotas_country ON user_quotas(country_code);
CREATE INDEX idx_user_quotas_backend ON user_quotas(storage_backend);
```

**Fields:**
- `did`: User's ATProtocol DID (primary key)
- `storage_used_bytes`: Cumulative storage used (never resets)
- `storage_limit_bytes`: Maximum storage allowed (default: 2GB = 2147483648 bytes)
- `blob_count`: Number of blobs stored (cumulative)
- `blob_limit`: Maximum number of blobs (default: 1000)
- `country_code`: ISO 3166-1 alpha-2 country code (e.g., "US", "DE")
- `storage_backend`: Storage backend used ("bunny" or "digitalocean")
- `storage_region`: Region identifier (e.g., "us", "frankfurt", "nyc3")
- `warning_sent`: Flag indicating 80% threshold warning has been sent
- `created_at`: Timestamp when quota was first created
- `updated_at`: Timestamp of last update

#### blobs Table
```sql
CREATE TABLE blobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    did TEXT NOT NULL,
    blob_id TEXT NOT NULL,
    size_bytes INTEGER NOT NULL,
    url TEXT NOT NULL,
    storage_backend TEXT NOT NULL,
    storage_region TEXT NOT NULL,
    soft_deleted INTEGER DEFAULT 0,
    deleted_at TIMESTAMP,
    retention_until TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (did) REFERENCES user_quotas(did) ON DELETE CASCADE,
    UNIQUE(did, blob_id)
);

CREATE INDEX idx_blobs_did ON blobs(did);
CREATE INDEX idx_blobs_created ON blobs(created_at);
CREATE INDEX idx_blobs_soft_deleted ON blobs(soft_deleted);
CREATE INDEX idx_blobs_retention ON blobs(retention_until) WHERE retention_until IS NOT NULL;
```

**Fields:**
- `id`: Auto-increment primary key
- `did`: User's DID (foreign key to user_quotas)
- `blob_id`: Unique identifier for blob (UUID v4 format)
- `size_bytes`: Size of blob in bytes
- `url`: CDN URL for blob access
- `storage_backend`: Which backend stores this blob
- `storage_region`: Which region stores this blob
- `soft_deleted`: Flag indicating soft deletion (1 = deleted, 0 = active)
- `deleted_at`: Timestamp when soft-deleted
- `retention_until`: Timestamp for hard deletion (14 days after soft deletion)
- `created_at`: Timestamp when blob was created

#### sessions Table
```sql
CREATE TABLE sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    did TEXT NOT NULL,
    session_token TEXT UNIQUE NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (did) REFERENCES user_quotas(did) ON DELETE CASCADE
);

CREATE INDEX idx_sessions_did ON sessions(did);
CREATE INDEX idx_sessions_expires ON sessions(expires_at);
```

**Fields:**
- `id`: Auto-increment primary key
- `did`: User's DID (foreign key to user_quotas)
- `session_token`: Unique token for cookie (UUID v4 format)
- `expires_at`: Session expiration timestamp (7 days after creation)
- `created_at`: Timestamp when session was created

#### ntfy_subscriptions Table
```sql
CREATE TABLE ntfy_subscriptions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    did TEXT NOT NULL,
    topic TEXT NOT NULL,
    access_token TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(did)
);

CREATE INDEX idx_ntfy_subscriptions_did ON ntfy_subscriptions(did);
```

**Fields:**
- `id`: Auto-increment primary key
- `did`: User's DID (foreign key to user_quotas)
- `topic`: ntfy.sh topic for this user (e.g., "scratchback-did:abc123")
- `access_token`: Optional access token for private topics
- `created_at`: Timestamp when subscription was created

### Passkeys Database (passkeys/passkeys.db)

#### passkeys Table
```sql
CREATE TABLE passkeys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    credential_id TEXT NOT NULL UNIQUE,
    credential_data BLOB NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_passkeys_username ON passkeys(username);
```

**Fields:**
- `id`: Auto-increment primary key
- `username`: Admin username (e.g., "admin@scratchback.co")
- `credential_id`: Base64-encoded credential ID from WebAuthn
- `credential_data`: Serialized PasskeyCredential (BLOB)
- `created_at`: Timestamp when passkey was registered

## Data Relationships

```
user_quotas (1) ----< (1..N) ----> blobs
     |                                  |
     |----< sessions (0..1)           |
     |                                  |
     |----< ntfy_subscriptions (0..1)
```

## Storage Key Format

### Blob IDs
- Format: UUID v4 (e.g., "550e8400-e29b-41d4-a716-446655440e000")
- Purpose: Unique identifier for each blob
- Storage location: `blobs.blob_id`

### Session Tokens
- Format: UUID v4 (e.g., "550e8400-e29b-41d4-a716-446655440e000")
- Purpose: Secure session identifier for web UI
- Storage location: `sessions.session_token`
- Cookie name: "scratchback_session"

### Passkey Credential IDs
- Format: Base64-encoded credential ID from WebAuthn
- Purpose: Unique identifier for each registered passkey
- Storage location: `passkeys.credential_id`

## GDPR Data Isolation

### EU Data Storage
- **EU Users**: Stored in `srtchbk-eu` bucket (Bunny frankfurt or DO fra1)
- **Enforcement**: Automatic routing based on `user_quotas.country_code`
- **Blocking**: Uploads blocked until country is set in profile
- **Consent Flow**: User must consent to EU data center before uploading

### Non-EU Data Storage
- **Non-EU Users**: Stored in `srtchbk-us` bucket (Bunny us or DO nyc3)
- **Enforcement**: Automatic routing based on detection or profile
- **No Consent Required**: Users can upload immediately

### Region Mapping
| Region | Backend | Bunny Region | DigitalOcean Region |
|---------|----------|--------------|---------------------|
| EU      | Bunny    | frankfurt     | fra1               |
| US      | Bunny    | us           | nyc3               |
| Default | Config   | Config       | Config             |

## Soft Deletion Retention

### Deletion Process
1. **Soft Delete**: Set `soft_deleted = 1`, `deleted_at = NOW()`
2. **Retention Calculation**: Set `retention_until = deleted_at + 14 days`
3. **Quota Update**: Do NOT update `storage_used_bytes` (data counts until hard deletion)
4. **Hard Deletion**: CLI cleanup command deletes blobs where `retention_until < NOW()`
5. **Data Recovery**: 14-day grace period for users to recover accidentally deleted blobs

### Cleanup Command
```bash
$ scrtchbk-ctl cleanup
# Deletes blobs where retention_until < NOW()
# Updates user_quotas.storage_used_bytes (subtracting deleted blob sizes)
```

### GDPR Right to Erasure
1. User requests deletion via support or UI
2. Execute soft delete (step 1 above)
3. Wait 14 days for automatic hard deletion
4. Or execute cleanup command immediately (user consent)
5. Verify all blob copies deleted from Bunny Storage
6. Update user quota to reflect removed storage

## Quota Tracking

### Cumulative Model
- **Storage Used**: Never resets, always accumulates
- **Blob Count**: Never resets, always accumulates
- **Limit Enforcement**: Hard limit at configured maximum
- **Soft Warning**: Alert at 80% threshold
- **No Reset**: No monthly or periodic reset of quotas

### Cost Calculation
- **Real-Time**: API calls to Bunny/DigitalOcean for current usage
- **Cached**: Results cached for 1 hour
- **Background Refresh**: Cache cleared and recalculated every hour
- **Memoization**: Using `cached` proc macro for function-level caching