75 lines
3.2 KiB
PL/PgSQL
75 lines
3.2 KiB
PL/PgSQL
-- Make core.users.discord_id optional.
|
|
--
|
|
-- Discord is the first client, not the only possible one. A web frontend, a
|
|
-- Matrix bridge or a CLI would each create users who have no Discord account,
|
|
-- and NOT NULL would have forced a fake value or a schema change under
|
|
-- pressure.
|
|
--
|
|
-- The surrogate id was already the primary key for this reason; this finishes
|
|
-- the job by making discord_id what it always was conceptually - one LINK to an
|
|
-- identity rather than the identity itself.
|
|
|
|
ALTER TABLE core.users ALTER COLUMN discord_id DROP NOT NULL;
|
|
|
|
COMMENT ON COLUMN core.users.discord_id IS 'Discord snowflake, or NULL for a user who reached the backend some other way. The UNIQUE constraint still holds for non-NULL values: Postgres treats NULLs as distinct in a unique index, so any number of rows may have none.';
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Deletion by surrogate id.
|
|
--
|
|
-- core.forget_user(discord_id) can only reach users who have one. This is the
|
|
-- same cascade addressed by primary key, so /forget-me works regardless of how
|
|
-- the user arrived.
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
CREATE FUNCTION core.forget_user_by_id(p_user_id bigint)
|
|
RETURNS TABLE (deleted_user boolean, deleted_qsos bigint)
|
|
LANGUAGE plpgsql
|
|
SET search_path = pg_catalog, core, logbook
|
|
AS $$
|
|
DECLARE
|
|
v_qsos bigint := 0;
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM core.users WHERE id = p_user_id) THEN
|
|
RETURN QUERY SELECT false, 0::bigint;
|
|
RETURN;
|
|
END IF;
|
|
|
|
SELECT count(*) INTO v_qsos FROM logbook.qsos WHERE user_id = p_user_id;
|
|
|
|
DELETE FROM core.users WHERE id = p_user_id;
|
|
|
|
RETURN QUERY SELECT true, v_qsos;
|
|
END;
|
|
$$;
|
|
|
|
COMMENT ON FUNCTION core.forget_user_by_id(bigint) IS 'Hard-deletes a user and all dependent rows by surrogate id. Caller must also purge Redis keys for this user.';
|
|
|
|
GRANT EXECUTE ON FUNCTION core.forget_user_by_id(bigint) TO hammy_app;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Guard against rows nobody can reach.
|
|
--
|
|
-- With discord_id nullable and no other identifier column, a row with a NULL
|
|
-- discord_id and no callsign cannot be found by any lookup the application
|
|
-- performs: it is a leak, not a user. Require at least one way in.
|
|
--
|
|
-- Widen this CHECK as identifiers are added - or replace it with a core.identities
|
|
-- table, which is the right shape once there is genuinely a second client:
|
|
--
|
|
-- CREATE TABLE core.identities (
|
|
-- user_id bigint NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,
|
|
-- provider text NOT NULL,
|
|
-- external_id text NOT NULL,
|
|
-- linked_at timestamptz NOT NULL DEFAULT now(),
|
|
-- PRIMARY KEY (provider, external_id)
|
|
-- );
|
|
--
|
|
-- Not worth doing yet: every lookup becomes a join, and Discord is the only
|
|
-- client. The migration when the time comes is mechanical - create the table,
|
|
-- INSERT INTO identities SELECT id, 'discord', discord_id FROM users WHERE
|
|
-- discord_id IS NOT NULL, drop the column.
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
ALTER TABLE core.users
|
|
ADD CONSTRAINT users_reachable
|
|
CHECK (discord_id IS NOT NULL OR callsign IS NOT NULL); |