Spaces:
Running
Running
| """Deletable private audio payloads, separate from append-only research events.""" | |
| SCHEMA_SQL = """ | |
| CREATE TABLE IF NOT EXISTS raw_audio_consents ( | |
| audio_consent_id TEXT PRIMARY KEY, | |
| participant_id TEXT NOT NULL, | |
| consent_id TEXT NOT NULL REFERENCES consents(consent_id), | |
| app_id TEXT NOT NULL, | |
| policy_sha256 TEXT NOT NULL, | |
| policy_json TEXT NOT NULL, | |
| retention_days INTEGER NOT NULL, | |
| created_at TEXT NOT NULL, | |
| revoked_at TEXT | |
| ); | |
| CREATE TABLE IF NOT EXISTS raw_audio_recordings ( | |
| audio_id TEXT PRIMARY KEY, | |
| participant_id TEXT NOT NULL, | |
| audio_consent_id TEXT NOT NULL REFERENCES raw_audio_consents(audio_consent_id), | |
| game_id TEXT NOT NULL, | |
| media_type TEXT NOT NULL, | |
| nonce BLOB, | |
| ciphertext BLOB, | |
| created_at TEXT NOT NULL, | |
| expires_at TEXT NOT NULL, | |
| erased_at TEXT, | |
| erasure_reason TEXT | |
| ); | |
| CREATE INDEX IF NOT EXISTS raw_audio_participant ON raw_audio_recordings(participant_id); | |
| CREATE INDEX IF NOT EXISTS raw_audio_expiry ON raw_audio_recordings(expires_at); | |
| CREATE TABLE IF NOT EXISTS raw_audio_sessions ( | |
| audio_session_id TEXT PRIMARY KEY, | |
| participant_id TEXT NOT NULL, | |
| audio_consent_id TEXT NOT NULL REFERENCES raw_audio_consents(audio_consent_id), | |
| game_id TEXT NOT NULL, | |
| confirmation_json TEXT NOT NULL, | |
| created_at TEXT NOT NULL, | |
| expires_at TEXT NOT NULL, | |
| ended_at TEXT | |
| ); | |
| CREATE INDEX IF NOT EXISTS raw_audio_session_owner ON raw_audio_sessions(participant_id); | |
| CREATE TABLE IF NOT EXISTS raw_audio_recording_sessions ( | |
| audio_id TEXT PRIMARY KEY REFERENCES raw_audio_recordings(audio_id), | |
| audio_session_id TEXT NOT NULL REFERENCES raw_audio_sessions(audio_session_id) | |
| ); | |
| """ | |
| def erase_participant_audio(connection, participant_id, when): | |
| # Same transaction as withdrawal: concurrent uploads either precede erasure | |
| # or observe the canonical participant tombstone and cannot commit. | |
| connection.execute( | |
| "UPDATE raw_audio_sessions SET ended_at = COALESCE(ended_at, ?) WHERE participant_id = ?", | |
| (when, participant_id), | |
| ) | |
| connection.execute( | |
| "UPDATE raw_audio_consents SET revoked_at = COALESCE(revoked_at, ?) WHERE participant_id = ?", | |
| (when, participant_id), | |
| ) | |
| connection.execute( | |
| "UPDATE raw_audio_recordings SET ciphertext = NULL, nonce = NULL, " | |
| "erased_at = ?, erasure_reason = 'withdrawal' WHERE participant_id = ? AND erased_at IS NULL", | |
| (when, participant_id), | |
| ) | |