learning-platform/pb_migrations/1781000000_team_members_to_auth.js

/// <reference path="../pb_data/types.d.ts" />
//
// Issue #16 — Azure (Entra ID) login.
//
// Replaces the PIN-based `base` collection `team_members` with a PocketBase
// `auth` collection that authenticates against Azure Entra ID via OIDC.
//
// The existing PIN-based team_members are intentionally dropped (sign-off from
// the product owner — no migration of current users required). The knowledge
// base, generated tests and micro-learnings live in OTHER collections and are
// left completely untouched by this migration.
//
// OIDC provider credentials are read from the environment at apply time, so no
// secrets are committed to git:
//   ENTRA_TENANT_ID, ENTRA_CLIENT_ID, ENTRA_CLIENT_SECRET
// (set via the Ansible-rendered .env, see infra/*/site/compose.yml).
//
// To rotate credentials or change the tenant later, update the env and either
// re-configure the provider from the PocketBase admin UI (Settings → Auth
// providers) or ship a follow-up migration.
migrate((app) => {
  // 1. Detach relation fields that point at team_members. PocketBase refuses to
  //    delete a collection that other collections relate to, so before we can
  //    drop the old PIN-based team_members we convert those relations into plain
  //    text fields (the id is stored as text — consistent with how user_id is
  //    stored in test_results / on_demand_attempts). Per-user progress is reset
  //    anyway, so dropping the FK constraint here is acceptable.
  const deps = [
    { name: "micro_learning_completions", relId: "rel_team_member_id",  textId: "txt_mlc_team_member" },
    { name: "theme_session_completions",  relId: "rel_tsc_team_member", textId: "txt_tsc_team_member" },
  ];
  for (const dep of deps) {
    let c;
    try { c = app.findCollectionByNameOrId(dep.name); } catch (_) { continue; }
    // Drop any index that references the team_member_id column.
    c.indexes = (c.indexes || []).filter((idx) => idx.indexOf("team_member_id") === -1);
    // Replace the relation field with a text field of the same name.
    c.fields.removeById(dep.relId);
    c.fields.add(new Field({
      type: "text",
      id: dep.textId,
      name: "team_member_id",
      required: false,
      min: 0,
      max: 0,
    }));
    app.save(c);
  }

  // Recreate the unique (team_member_id, session_week) guard on the text column.
  try {
    const tsc = app.findCollectionByNameOrId("theme_session_completions");
    tsc.indexes.push("CREATE UNIQUE INDEX `idx_theme_session_completions_user_week` ON `theme_session_completions` (`team_member_id`, `session_week`)");
    app.save(tsc);
  } catch (_) { /* collection missing — skip */ }

  // 2. Drop the old PIN-based team_members (now unreferenced).
  try {
    const old = app.findCollectionByNameOrId("team_members");
    app.delete(old);
  } catch (_) {
    // Collection did not exist — nothing to drop.
  }

  const tenant = $os.getenv("ENTRA_TENANT_ID") || "common";
  const clientId = $os.getenv("ENTRA_CLIENT_ID");
  const clientSecret = $os.getenv("ENTRA_CLIENT_SECRET");

  // Only configure the OIDC provider when credentials are actually present.
  // PocketBase rejects an enabled OAuth2 provider with an empty clientId /
  // clientSecret, which would abort this migration and prevent PocketBase from
  // starting at all (502 on every /api call). When the secrets are absent the
  // collection is created without a provider; once the ENTRA_* secrets are set,
  // configure the provider via the PocketBase admin UI
  // (Settings → Auth providers → OIDC) or re-apply this migration.
  const oidcProviders = (clientId && clientSecret) ? [
    {
      name: "oidc",
      clientId: clientId,
      clientSecret: clientSecret,
      authURL:     "https://login.microsoftonline.com/" + tenant + "/oauth2/v2.0/authorize",
      tokenURL:    "https://login.microsoftonline.com/" + tenant + "/oauth2/v2.0/token",
      userInfoURL: "https://graph.microsoft.com/oidc/userinfo",
      displayName: "Microsoft",
      pkce: true,
    },
  ] : [];

  // 2. Recreate `team_members` as an auth collection (same name → all existing
  //    `user_id` references in test_results, on_demand_attempts,
  //    micro_learning_completions, leaderboard, theme_session_completions keep
  //    working unchanged).
  const collection = new Collection({
    type: "auth",
    name: "team_members",
    system: false,

    // Access rules: every legitimate user is authenticated (Azure-gated +
    // OIDC). Profiles are readable by any authenticated user (leaderboard,
    // dashboard); a user may only update their own record (onboarding flips
    // enrollment_status). Creation/deletion happen via OAuth2 / superuser only.
    listRule:   '@request.auth.id != ""',
    viewRule:   '@request.auth.id != ""',
    createRule: null,
    updateRule: '@request.auth.id = id',
    deleteRule: null,
    authRule:   "",
    manageRule: null,

    // Disable password / OTP / MFA — login is exclusively via Entra OIDC.
    passwordAuth: { enabled: false, identityFields: ["email"] },
    otp:          { enabled: false, duration: 180, length: 8 },
    mfa:          { enabled: false, duration: 600, rule: "" },

    oauth2: {
      enabled: oidcProviders.length > 0,
      // Map the OIDC userinfo claims onto our fields.
      mappedFields: {
        id: "",
        name: "name",
        username: "",
        avatarURL: "",
      },
      providers: oidcProviders,
    },

    fields: [
      // ── System auth fields ──────────────────────────────────────────────
      {
        "autogeneratePattern": "[a-z0-9]{15}",
        "hidden": false,
        "id": "text3208210256",
        "max": 15,
        "min": 15,
        "name": "id",
        "pattern": "^[a-z0-9]+$",
        "presentable": false,
        "primaryKey": true,
        "required": true,
        "system": true,
        "type": "text"
      },
      {
        "cost": 0,
        "hidden": true,
        "id": "password901924565",
        "max": 0,
        "min": 8,
        "name": "password",
        "pattern": "",
        "presentable": false,
        "required": true,
        "system": true,
        "type": "password"
      },
      {
        "autogeneratePattern": "[a-zA-Z0-9]{50}",
        "hidden": true,
        "id": "text2504183744",
        "max": 60,
        "min": 30,
        "name": "tokenKey",
        "pattern": "",
        "presentable": false,
        "primaryKey": false,
        "required": true,
        "system": true,
        "type": "text"
      },
      {
        "exceptDomains": null,
        "hidden": false,
        "id": "email3885137012",
        "name": "email",
        "onlyDomains": null,
        "presentable": false,
        "required": true,
        "system": true,
        "type": "email"
      },
      {
        "hidden": false,
        "id": "bool1547992806",
        "name": "emailVisibility",
        "presentable": false,
        "required": false,
        "system": true,
        "type": "bool"
      },
      {
        "hidden": false,
        "id": "bool256245529",
        "name": "verified",
        "presentable": false,
        "required": false,
        "system": true,
        "type": "bool"
      },
      // ── Application fields ───────────────────────────────────────────────
      {
        "autogeneratePattern": "",
        "hidden": false,
        "id": "text1579384326",
        "max": 255,
        "min": 0,
        "name": "name",
        "pattern": "",
        "presentable": false,
        "primaryKey": false,
        "required": false,
        "system": false,
        "type": "text"
      },
      {
        "autogeneratePattern": "",
        "hidden": false,
        "id": "text1466534506",
        "max": 0,
        "min": 0,
        "name": "role",
        "pattern": "",
        "presentable": false,
        "primaryKey": false,
        "required": false,
        "system": false,
        "type": "text"
      },
      {
        "hidden": false,
        "id": "date_curriculum_started_at",
        "name": "curriculum_started_at",
        "presentable": false,
        "required": false,
        "system": false,
        "type": "date"
      },
      {
        "autogeneratePattern": "",
        "hidden": false,
        "id": "text_enrollment_status",
        "max": 0,
        "min": 0,
        "name": "enrollment_status",
        "pattern": "",
        "presentable": false,
        "primaryKey": false,
        "required": false,
        "system": false,
        "type": "text"
      },
      {
        "hidden": false,
        "id": "autodate2990389176",
        "name": "created",
        "onCreate": true,
        "onUpdate": false,
        "presentable": false,
        "system": false,
        "type": "autodate"
      },
      {
        "hidden": false,
        "id": "autodate3332085495",
        "name": "updated",
        "onCreate": true,
        "onUpdate": true,
        "presentable": false,
        "system": false,
        "type": "autodate"
      }
    ],

    indexes: [
      "CREATE UNIQUE INDEX `idx_tokenKey_team_members` ON `team_members` (`tokenKey`)",
      "CREATE UNIQUE INDEX `idx_email_team_members` ON `team_members` (`email`) WHERE `email` != ''"
    ],
  });

  app.save(collection);
}, (app) => {
  // Down: drop the auth collection and recreate the original PIN-based base
  // collection (without data — the original records are gone).
  try {
    const c = app.findCollectionByNameOrId("team_members");
    app.delete(c);
  } catch (_) { /* already gone */ }

  const base = new Collection({
    type: "base",
    name: "team_members",
    listRule: "",
    viewRule: "",
    createRule: "",
    updateRule: "",
    deleteRule: "",
    fields: [
      { "type": "text", "name": "id", "system": true, "primaryKey": true, "required": true,
        "min": 15, "max": 15, "pattern": "^[a-z0-9]+$", "autogeneratePattern": "[a-z0-9]{15}",
        "id": "text3208210256" },
      { "type": "text", "name": "name", "required": true, "min": 0, "max": 0, "id": "text1579384326" },
      { "type": "text", "name": "pin", "required": false, "min": 0, "max": 0, "id": "text3045404147" },
      { "type": "text", "name": "role", "required": false, "min": 0, "max": 0, "id": "text1466534506" },
      { "type": "date", "name": "curriculum_started_at", "required": false, "id": "date_curriculum_started_at" },
      { "type": "text", "name": "enrollment_status", "required": false, "min": 0, "max": 0, "id": "text_enrollment_status" },
    ],
  });
  app.save(base);
});