Data Model#

Database Schemas#

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

user_quotas Table#

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#

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#

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#

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#

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#

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#

$ 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