synctv/migrations/20260426022_create_user_media_provider_credentials.sql

CREATE TABLE IF NOT EXISTS user_media_provider_credentials (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    user_id BIGINT NOT NULL,
    provider SMALLINT NOT NULL,

    server_id VARCHAR(64) NOT NULL,

    provider_instance_name VARCHAR(64),

    credential_data JSONB NOT NULL,

    expires_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT user_media_provider_credentials_user_fk
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT user_media_provider_credentials_instance_fk
        FOREIGN KEY (provider_instance_name) REFERENCES media_provider_instances(name) ON DELETE CASCADE,
    CONSTRAINT user_media_provider_credentials_user_provider_server_unique
        UNIQUE(user_id, provider, server_id),
    CONSTRAINT user_media_provider_credentials_credential_data_object
        CHECK (jsonb_typeof(credential_data) = 'object'),
    CONSTRAINT user_media_provider_credentials_server_id_not_empty
        CHECK (length(trim(server_id)) > 0)
);

CREATE INDEX IF NOT EXISTS idx_user_media_provider_credentials_user
    ON user_media_provider_credentials(user_id);
CREATE INDEX IF NOT EXISTS idx_user_media_provider_credentials_provider
    ON user_media_provider_credentials(provider);
CREATE INDEX IF NOT EXISTS idx_user_media_provider_credentials_instance
    ON user_media_provider_credentials(provider_instance_name);
CREATE INDEX IF NOT EXISTS idx_user_media_provider_credentials_expires
    ON user_media_provider_credentials(expires_at)
    WHERE expires_at IS NOT NULL;

CREATE TRIGGER update_user_media_provider_credentials_updated_at
    BEFORE UPDATE ON user_media_provider_credentials
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();