68 lines
3.7 KiB
Python
68 lines
3.7 KiB
Python
"""Startup equivalents of migrations 19–23 (kept in sync by tests)."""
|
||
|
||
FEATURE_SCHEMA = (
|
||
'''CREATE TABLE IF NOT EXISTS push_devices (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
session_id INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
|
||
device_id UUID NOT NULL UNIQUE,
|
||
token TEXT NOT NULL UNIQUE CHECK (length(token) BETWEEN 20 AND 4096),
|
||
platform VARCHAR(16) NOT NULL CHECK (platform = 'android'),
|
||
app_version VARCHAR(32) NOT NULL DEFAULT '',
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
last_seen_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_push_devices_user ON push_devices(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_push_devices_session ON push_devices(session_id);''',
|
||
'''CREATE TABLE IF NOT EXISTS bug_report_submissions (
|
||
id UUID PRIMARY KEY,
|
||
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
state VARCHAR(16) NOT NULL DEFAULT 'new'
|
||
CHECK (state IN ('new', 'sending', 'success', 'failed', 'unknown')),
|
||
issue_number INTEGER,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
submitted_at TIMESTAMP,
|
||
expires_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + INTERVAL '24 hours'
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_bug_report_submissions_user
|
||
ON bug_report_submissions(user_id, submitted_at);''',
|
||
'''CREATE TABLE IF NOT EXISTS notification_preferences (
|
||
user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
||
language VARCHAR(2) NOT NULL DEFAULT 'de' CHECK (language IN ('de', 'en')),
|
||
friend_request BOOLEAN NOT NULL DEFAULT TRUE,
|
||
direct_message BOOLEAN NOT NULL DEFAULT TRUE,
|
||
event_invitation BOOLEAN NOT NULL DEFAULT TRUE
|
||
);
|
||
CREATE TABLE IF NOT EXISTS push_notifications (
|
||
id UUID PRIMARY KEY,
|
||
device_id BIGINT NOT NULL REFERENCES push_devices(id) ON DELETE CASCADE,
|
||
session_id INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
|
||
recipient_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
actor_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
kind VARCHAR(32) NOT NULL CHECK (kind IN ('friend_request', 'direct_message', 'event_invitation')),
|
||
object_id BIGINT NOT NULL,
|
||
event_key TEXT NOT NULL,
|
||
token_hash VARCHAR(64) NOT NULL,
|
||
state VARCHAR(16) NOT NULL DEFAULT 'pending' CHECK (state IN ('pending', 'sent', 'dropped', 'failed')),
|
||
attempts SMALLINT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
expires_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP + INTERVAL '1 hour',
|
||
available_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(device_id, event_key)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_push_notifications_pending
|
||
ON push_notifications(available_at) WHERE state='pending';''',
|
||
'''CREATE UNIQUE INDEX IF NOT EXISTS idx_user_badges_registration_cohort
|
||
ON user_badges(user_id) WHERE badge_code IN ('alpha_tester', 'beta_tester', 'early_bird');''',
|
||
'''CREATE TABLE IF NOT EXISTS followed_users (
|
||
follower_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
followed_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (follower_id, followed_id),
|
||
CHECK (follower_id <> followed_id)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_followed_users_followed
|
||
ON followed_users(followed_id, follower_id);''',
|
||
)
|