Game sync and live services for independent game developers (targeting itch.io)
1# Data Model
2
3## Database Schemas
4
5### Main Database (scratchback-pds/data.sqlite)
6
7#### user_quotas Table
8```sql
9CREATE TABLE user_quotas (
10 did TEXT PRIMARY KEY,
11 storage_used_bytes INTEGER NOT NULL DEFAULT 0,
12 storage_limit_bytes INTEGER NOT NULL DEFAULT 2147483648, -- 2GB
13 blob_count INTEGER NOT NULL DEFAULT 0,
14 blob_limit INTEGER NOT NULL DEFAULT 1000,
15 country_code TEXT,
16 storage_backend TEXT,
17 storage_region TEXT,
18 warning_sent INTEGER DEFAULT 0,
19 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
20 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
21);
22
23CREATE INDEX idx_user_quotas_country ON user_quotas(country_code);
24CREATE INDEX idx_user_quotas_backend ON user_quotas(storage_backend);
25```
26
27**Fields:**
28- `did`: User's ATProtocol DID (primary key)
29- `storage_used_bytes`: Cumulative storage used (never resets)
30- `storage_limit_bytes`: Maximum storage allowed (default: 2GB = 2147483648 bytes)
31- `blob_count`: Number of blobs stored (cumulative)
32- `blob_limit`: Maximum number of blobs (default: 1000)
33- `country_code`: ISO 3166-1 alpha-2 country code (e.g., "US", "DE")
34- `storage_backend`: Storage backend used ("bunny" or "digitalocean")
35- `storage_region`: Region identifier (e.g., "us", "frankfurt", "nyc3")
36- `warning_sent`: Flag indicating 80% threshold warning has been sent
37- `created_at`: Timestamp when quota was first created
38- `updated_at`: Timestamp of last update
39
40#### blobs Table
41```sql
42CREATE TABLE blobs (
43 id INTEGER PRIMARY KEY AUTOINCREMENT,
44 did TEXT NOT NULL,
45 blob_id TEXT NOT NULL,
46 size_bytes INTEGER NOT NULL,
47 url TEXT NOT NULL,
48 storage_backend TEXT NOT NULL,
49 storage_region TEXT NOT NULL,
50 soft_deleted INTEGER DEFAULT 0,
51 deleted_at TIMESTAMP,
52 retention_until TIMESTAMP,
53 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
54 FOREIGN KEY (did) REFERENCES user_quotas(did) ON DELETE CASCADE,
55 UNIQUE(did, blob_id)
56);
57
58CREATE INDEX idx_blobs_did ON blobs(did);
59CREATE INDEX idx_blobs_created ON blobs(created_at);
60CREATE INDEX idx_blobs_soft_deleted ON blobs(soft_deleted);
61CREATE INDEX idx_blobs_retention ON blobs(retention_until) WHERE retention_until IS NOT NULL;
62```
63
64**Fields:**
65- `id`: Auto-increment primary key
66- `did`: User's DID (foreign key to user_quotas)
67- `blob_id`: Unique identifier for blob (UUID v4 format)
68- `size_bytes`: Size of blob in bytes
69- `url`: CDN URL for blob access
70- `storage_backend`: Which backend stores this blob
71- `storage_region`: Which region stores this blob
72- `soft_deleted`: Flag indicating soft deletion (1 = deleted, 0 = active)
73- `deleted_at`: Timestamp when soft-deleted
74- `retention_until`: Timestamp for hard deletion (14 days after soft deletion)
75- `created_at`: Timestamp when blob was created
76
77#### sessions Table
78```sql
79CREATE TABLE sessions (
80 id INTEGER PRIMARY KEY AUTOINCREMENT,
81 did TEXT NOT NULL,
82 session_token TEXT UNIQUE NOT NULL,
83 expires_at TIMESTAMP NOT NULL,
84 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
85 FOREIGN KEY (did) REFERENCES user_quotas(did) ON DELETE CASCADE
86);
87
88CREATE INDEX idx_sessions_did ON sessions(did);
89CREATE INDEX idx_sessions_expires ON sessions(expires_at);
90```
91
92**Fields:**
93- `id`: Auto-increment primary key
94- `did`: User's DID (foreign key to user_quotas)
95- `session_token`: Unique token for cookie (UUID v4 format)
96- `expires_at`: Session expiration timestamp (7 days after creation)
97- `created_at`: Timestamp when session was created
98
99#### ntfy_subscriptions Table
100```sql
101CREATE TABLE ntfy_subscriptions (
102 id INTEGER PRIMARY KEY AUTOINCREMENT,
103 did TEXT NOT NULL,
104 topic TEXT NOT NULL,
105 access_token TEXT,
106 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
107 UNIQUE(did)
108);
109
110CREATE INDEX idx_ntfy_subscriptions_did ON ntfy_subscriptions(did);
111```
112
113**Fields:**
114- `id`: Auto-increment primary key
115- `did`: User's DID (foreign key to user_quotas)
116- `topic`: ntfy.sh topic for this user (e.g., "scratchback-did:abc123")
117- `access_token`: Optional access token for private topics
118- `created_at`: Timestamp when subscription was created
119
120### Passkeys Database (passkeys/passkeys.db)
121
122#### passkeys Table
123```sql
124CREATE TABLE passkeys (
125 id INTEGER PRIMARY KEY AUTOINCREMENT,
126 username TEXT NOT NULL UNIQUE,
127 credential_id TEXT NOT NULL UNIQUE,
128 credential_data BLOB NOT NULL,
129 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
130);
131
132CREATE INDEX idx_passkeys_username ON passkeys(username);
133```
134
135**Fields:**
136- `id`: Auto-increment primary key
137- `username`: Admin username (e.g., "admin@scratchback.co")
138- `credential_id`: Base64-encoded credential ID from WebAuthn
139- `credential_data`: Serialized PasskeyCredential (BLOB)
140- `created_at`: Timestamp when passkey was registered
141
142## Data Relationships
143
144```
145user_quotas (1) ----< (1..N) ----> blobs
146 | |
147 |----< sessions (0..1) |
148 | |
149 |----< ntfy_subscriptions (0..1)
150```
151
152## Storage Key Format
153
154### Blob IDs
155- Format: UUID v4 (e.g., "550e8400-e29b-41d4-a716-446655440e000")
156- Purpose: Unique identifier for each blob
157- Storage location: `blobs.blob_id`
158
159### Session Tokens
160- Format: UUID v4 (e.g., "550e8400-e29b-41d4-a716-446655440e000")
161- Purpose: Secure session identifier for web UI
162- Storage location: `sessions.session_token`
163- Cookie name: "scratchback_session"
164
165### Passkey Credential IDs
166- Format: Base64-encoded credential ID from WebAuthn
167- Purpose: Unique identifier for each registered passkey
168- Storage location: `passkeys.credential_id`
169
170## GDPR Data Isolation
171
172### EU Data Storage
173- **EU Users**: Stored in `srtchbk-eu` bucket (Bunny frankfurt or DO fra1)
174- **Enforcement**: Automatic routing based on `user_quotas.country_code`
175- **Blocking**: Uploads blocked until country is set in profile
176- **Consent Flow**: User must consent to EU data center before uploading
177
178### Non-EU Data Storage
179- **Non-EU Users**: Stored in `srtchbk-us` bucket (Bunny us or DO nyc3)
180- **Enforcement**: Automatic routing based on detection or profile
181- **No Consent Required**: Users can upload immediately
182
183### Region Mapping
184| Region | Backend | Bunny Region | DigitalOcean Region |
185|---------|----------|--------------|---------------------|
186| EU | Bunny | frankfurt | fra1 |
187| US | Bunny | us | nyc3 |
188| Default | Config | Config | Config |
189
190## Soft Deletion Retention
191
192### Deletion Process
1931. **Soft Delete**: Set `soft_deleted = 1`, `deleted_at = NOW()`
1942. **Retention Calculation**: Set `retention_until = deleted_at + 14 days`
1953. **Quota Update**: Do NOT update `storage_used_bytes` (data counts until hard deletion)
1964. **Hard Deletion**: CLI cleanup command deletes blobs where `retention_until < NOW()`
1975. **Data Recovery**: 14-day grace period for users to recover accidentally deleted blobs
198
199### Cleanup Command
200```bash
201$ scrtchbk-ctl cleanup
202# Deletes blobs where retention_until < NOW()
203# Updates user_quotas.storage_used_bytes (subtracting deleted blob sizes)
204```
205
206### GDPR Right to Erasure
2071. User requests deletion via support or UI
2082. Execute soft delete (step 1 above)
2093. Wait 14 days for automatic hard deletion
2104. Or execute cleanup command immediately (user consent)
2115. Verify all blob copies deleted from Bunny Storage
2126. Update user quota to reflect removed storage
213
214## Quota Tracking
215
216### Cumulative Model
217- **Storage Used**: Never resets, always accumulates
218- **Blob Count**: Never resets, always accumulates
219- **Limit Enforcement**: Hard limit at configured maximum
220- **Soft Warning**: Alert at 80% threshold
221- **No Reset**: No monthly or periodic reset of quotas
222
223### Cost Calculation
224- **Real-Time**: API calls to Bunny/DigitalOcean for current usage
225- **Cached**: Results cached for 1 hour
226- **Background Refresh**: Cache cleared and recalculated every hour
227- **Memoization**: Using `cached` proc macro for function-level caching