Spaces:
Sleeping
Sleeping
File size: 2,479 Bytes
f36cf07 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | """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),
)
|