Recommended database schema

Schema yang direkomendasikan untuk menyimpan user SSO adalah users + user_identities. Satu user bisa login melalui Frappe, Google, GitHub, dan password tanpa menambah kolom baru untuk setiap provider.

Mengapa struktur ini?

Membuat tabel khusus per provider (seperti frappe_subject, is_frappe) akan menambah rumit setiap kali provider baru ditambahkan. Struktur users + user_identities menghindari masalah itu — provider baru tinggal tambah row, tidak perlu ubah tabel.

Schema

CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       VARCHAR(255) NOT NULL UNIQUE,
    name        VARCHAR(255),
    password    VARCHAR(255),          -- NULL = SSO-only user
    image       TEXT,
    status      VARCHAR(20) DEFAULT 'AKTIF',
    role        VARCHAR(50) DEFAULT 'user',
    created_at  TIMESTAMPTZ DEFAULT now(),
    updated_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE user_identities (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id          UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    provider         VARCHAR(50) NOT NULL,        -- 'frappe', 'google', ...
    provider_subject VARCHAR(255) NOT NULL,        -- stable ID (`sub`)
    provider_email   VARCHAR(255),
    metadata         JSONB,
    created_at       TIMESTAMPTZ DEFAULT now(),
    updated_at       TIMESTAMPTZ DEFAULT now(),
    UNIQUE (provider, provider_subject)
);

Urutan match/create

Selalu subject dulu, email sebagai fallback:

1. Cari user_identities dengan provider = 'frappe' dan provider_subject = profile.subject.
2. Jika tidak ada, cari users.email = profile.email.
3. Jika user ditemukan lewat email, link-kan identity Frappe ke user tersebut.
4. Jika tetap tidak ada, buat users + user_identities.
5. Cek status user. Jangan buat session untuk user nonaktif.
6. Buat session aplikasi.

const identity = await UserIdentity.findOne({
  where: { provider: 'frappe', provider_subject: profile.subject },
});

let user = identity?.user;

if (!user) {
  user = await User.findOne({ where: { email: profile.email } });
  if (user) {
    await UserIdentity.create({
      userId: user.id,
      provider: 'frappe',
      providerSubject: profile.subject,
      providerEmail: profile.email,
      metadata: { roles: profile.roles, raw: profile.raw },
    });
  }
}

if (!user) {
  user = await User.create({
    email: profile.email,
    name: profile.name,
    image: profile.image,
    password: null,
  });
  await UserIdentity.create({
    userId: user.id,
    provider: 'frappe',
    providerSubject: profile.subject,
    providerEmail: profile.email,
  });
}

if (user.status !== 'AKTIF') throw new Error('User is not active');

Keputusan desain

• password nullable: user SSO-only tidak punya password.
• UNIQUE(provider, provider_subject): satu akun Frappe tidak membuat duplicate identity.
• Subject diprioritaskan karena stabil; email bisa berubah.
• metadata menyimpan roles dan raw profile tanpa migration baru.
• Jangan buat kolom is_frappe; keberadaan identity sudah cukup.
• Access/refresh token bukan bagian user_identities; gunakan table dedicated dan encrypt token.