redefined-designs/backend/migrations/1788200000000_add-social-identities.js

exports.up = (pgm) => {
  pgm.sql(`
    -- A sign-in that belongs to somebody else's identity provider (#340).
    --
    -- A table rather than columns on customers, because one customer may hold
    -- more than one: Google today, and Apple if #332 ever decides in its
    -- favour. Columns would mean a second provider is a migration and a third
    -- is an embarrassment.
    --
    -- Same shape as customer_credentials, and for the same reason: a row that
    -- links this account to something a third party can vouch for, deleted with
    -- the customer because an identity that outlived its owner could
    -- authenticate as a customer who no longer exists.
    CREATE TABLE IF NOT EXISTS customer_identities (
      id SERIAL PRIMARY KEY,
      customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,

      -- 'google' today. Not a CHECK constraint: the values come from this
      -- codebase rather than from a request, and the project has no enum types
      -- elsewhere.
      provider TEXT NOT NULL,

      -- The provider's subject claim, and **never the email**.
      --
      -- This is the whole security posture of the table in one column. An email
      -- is a display value that its owner can change and that a provider may
      -- reassign; a subject is opaque, stable for the life of the account, and
      -- means nothing outside the provider that issued it. Matching on the
      -- email would strand a customer who changed theirs and, far worse, hand
      -- their account to whoever inherited the old address.
      provider_sub TEXT NOT NULL,

      created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

      -- Null until first used, exactly as on a passkey. It is what tells two
      -- entries apart on an account page where the names are similar.
      last_used_at TIMESTAMPTZ,

      -- Unique across the pair, not on the subject alone. Two providers could
      -- in principle issue the same opaque string and it would mean nothing —
      -- but the same provider issuing one subject to two accounts here means
      -- something has gone wrong rather than that two people share an identity.
      UNIQUE (provider, provider_sub)
    );

    -- Listing what an account is linked to is the common read, and it is always
    -- scoped by owner.
    CREATE INDEX IF NOT EXISTS customer_identities_customer_id_idx
      ON customer_identities (customer_id);

    -- The change with the widest blast radius in the whole project (#332).
    --
    -- A customer who signed up through Google has no password and never will
    -- unless they ask for one, so the column has to admit that. Making it
    -- nullable is one line; what it costs is that every read of it is now a
    -- question rather than a fact, and the three that compare against it with
    -- bcrypt have been made to ask first.
    --
    -- Nothing writes a NULL yet. The first accounts without a password arrive
    -- with the sign-up path, and this is deliberately landed before them so the
    -- schema change can be reviewed on its own.
    ALTER TABLE customers ALTER COLUMN password_hash DROP NOT NULL;
  `);
};

exports.down = (pgm) => {
  pgm.sql(`
    DROP TABLE IF EXISTS customer_identities;

    -- Deliberately not restored. Re-adding NOT NULL fails outright if any
    -- passwordless customer exists by then, and a down migration that destroys
    -- accounts to satisfy a constraint would be far worse than a column that is
    -- merely more permissive than it needs to be. Reversing this properly means
    -- deciding what happens to those customers, which is not a schema decision.
    SELECT 1;
  `);
};