learning-platform/scripts/setup-pb-collections.mjs

/**
 * Creates all PocketBase collections for the learning platform.
 * Usage: node scripts/setup-pb-collections.mjs [PB_URL] [ADMIN_EMAIL] [ADMIN_PASSWORD]
 * Defaults: http://localhost:8090, prompted if not provided
 */

const PB_URL = process.argv[2] || 'http://localhost:8090';
const ADMIN_EMAIL = process.argv[3];
const ADMIN_PASSWORD = process.argv[4];

if (!ADMIN_EMAIL || !ADMIN_PASSWORD) {
  console.error('Usage: node scripts/setup-pb-collections.mjs <PB_URL> <admin_email> <admin_password>');
  process.exit(1);
}

const OPEN_RULES = { listRule: '', viewRule: '', createRule: '', updateRule: '', deleteRule: '' };
const AUTHED = '@request.auth.id != ""';
const AUTHED_RULES = { listRule: AUTHED, viewRule: AUTHED, createRule: AUTHED, updateRule: AUTHED, deleteRule: AUTHED };

// PocketBase v0.23: created/updated must be explicitly defined as autodate fields
const AUTODATE_FIELDS = [
  { name: 'created', type: 'autodate', onCreate: true, onUpdate: false },
  { name: 'updated', type: 'autodate', onCreate: true, onUpdate: true },
];

const COLLECTIONS = [
  {
    name: 'topics',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'id',          type: 'text',   primaryKey: true, system: true, min: 1, max: 255, pattern: '^[a-zA-Z0-9_-]+$' },
      { name: 'label',       type: 'text',   required: true },
      { name: 'type',        type: 'text',   required: false },
      { name: 'description', type: 'text',   required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  {
    name: 'relations',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'source', type: 'text', required: true },
      { name: 'target', type: 'text', required: true },
      { name: 'type',   type: 'text', required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  {
    name: 'content',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'topic_id', type: 'text', required: true },
      { name: 'data',     type: 'json', required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  {
    // Question bank: one record per topic with a `questions` JSON array.
    // Shared across all users; admin-curated; powers on-demand topic tests.
    name: 'question_bank',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'topic_id',  type: 'text', required: true },
      { name: 'questions', type: 'json', required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  {
    // On-demand attempts log: one record per completed topic test.
    // Powers the weekly-cap aggregation and the Contributions feed.
    name: 'on_demand_attempts',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'user_id',        type: 'text',   required: true },
      { name: 'topic_id',       type: 'text',   required: true },
      { name: 'topic_label',    type: 'text',   required: false },
      { name: 'question_ids',   type: 'json',   required: false },
      { name: 'score',          type: 'number', required: false },
      { name: 'total',          type: 'number', required: false },
      { name: 'percentage',     type: 'number', required: false },
      { name: 'time_used',      type: 'number', required: false },
      { name: 'unseen_correct', type: 'number', required: false },
      { name: 'points_earned',  type: 'number', required: false },
      { name: 'capped',         type: 'bool',   required: false },
      { name: 'iso_week',       type: 'text',   required: false },
      { name: 'completed_at',   type: 'text',   required: false },
      { name: 'breakdown',      type: 'json',   required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  {
    name: 'sources',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'name',   type: 'text', required: true },
      { name: 'status', type: 'text', required: false },
      { name: 'error',  type: 'text', required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  // NOTE: `team_members` is an AUTH collection owned by the migration
  // pb_migrations/1781000000_team_members_to_auth.js (Azure/Entra ID login).
  // Migrations run on PocketBase start — before this script — so the entry
  // below is only a fallback for environments where migrations have not run;
  // when the auth collection already exists, this create is skipped.
  {
    name: 'team_members',
    type: 'auth',
    ...OPEN_RULES,
    // Mirror of pb_migrations/1781000002: creation only via the OAuth2
    // sign-up flow (issue #22).
    createRule: '@request.context = "oauth2"',
    // Mirror of pb_migrations/1781000003: self-update without role changes;
    // roster management (incl. role/delete) is admin-only (issue #27).
    updateRule: '(@request.auth.id = id && @request.body.role:isset = false) || @request.auth.role = "admin"',
    deleteRule: '@request.auth.role = "admin"',
    passwordAuth: { enabled: false, identityFields: ['email'] },
    fields: [
      { name: 'name', type: 'text', required: false },
      { name: 'role', type: 'text', required: false },
      { name: 'curriculum_started_at', type: 'date', required: false },
      { name: 'enrollment_status',     type: 'text', required: false },
    ],
  },
  {
    name: 'quiz_results',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'user_id',      type: 'text',   required: true },
      { name: 'week_number',  type: 'number', required: true },
      { name: 'score',        type: 'number', required: false },
      { name: 'total',        type: 'number', required: false },
      { name: 'percentage',   type: 'number', required: false },
      { name: 'time_used',    type: 'number', required: false },
      { name: 'completed_at', type: 'text',   required: false },
      { name: 'breakdown',    type: 'json',   required: false },
      { name: 'points_earned',type: 'number', required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  {
    name: 'quiz_cache',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'user_id',     type: 'text',   required: true },
      { name: 'week_number', type: 'number', required: true },
      { name: 'questions',   type: 'json',   required: false },
      { name: 'meta',        type: 'json',   required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  {
    name: 'learn_progress',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'user_id',     type: 'text',   required: true },
      { name: 'week_number', type: 'number', required: true },
      { name: 'done',        type: 'bool',   required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  {
    name: 'leaderboard',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'user_id',             type: 'text',   required: true },
      { name: 'name',                type: 'text',   required: false },
      { name: 'points',              type: 'number', required: false },
      { name: 'tests_completed',     type: 'number', required: false },
      { name: 'learnings_completed', type: 'number', required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  {
    name: 'curriculum',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'year',            type: 'number', required: true },
      { name: 'week_number',     type: 'number', required: true },
      { name: 'topic_id',        type: 'text',   required: false },
      { name: 'theme',           type: 'text',   required: false },
      { name: 'quarter',         type: 'number', required: false },
      { name: 'is_review_week',  type: 'bool',   required: false },
      { name: 'sort_order',      type: 'number', required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  {
    name: 'settings',
    type: 'base',
    ...OPEN_RULES,
    fields: [
      { name: 'key',   type: 'text', required: true },
      { name: 'value', type: 'text', required: false },
      ...AUTODATE_FIELDS,
    ],
  },
  // Onboarding track (issue #30). Owned by pb_migrations/1781200000; these are a
  // fallback for envs where migrations have not run. Authenticated-only rules
  // (mirrors the migration) — do NOT use OPEN_RULES here.
  {
    name: 'onboarding_overviews',
    type: 'base',
    ...AUTHED_RULES,
    fields: [
      { name: 'theme',              type: 'text', required: true },
      { name: 'content',            type: 'json', required: true },
      { name: 'topics_fingerprint', type: 'text', required: true },
      ...AUTODATE_FIELDS,
    ],
    indexes: ['CREATE UNIQUE INDEX `idx_onboarding_overviews_theme` ON `onboarding_overviews` (`theme`)'],
  },
  {
    name: 'onboarding_completions',
    type: 'base',
    ...AUTHED_RULES,
    fields: [
      { name: 'team_member_id', type: 'text', required: true },
      { name: 'theme',          type: 'text', required: true },
      ...AUTODATE_FIELDS,
    ],
    indexes: ['CREATE UNIQUE INDEX `idx_onboarding_completions_user_theme` ON `onboarding_completions` (`team_member_id`, `theme`)'],
  },
];

async function post(path, body, token) {
  const res = await fetch(`${PB_URL}${path}`, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      ...(token ? { Authorization: token } : {}),
    },
    body: JSON.stringify(body),
  });
  const json = await res.json();
  if (!res.ok) throw Object.assign(new Error(json.message || 'Request failed'), { status: res.status, data: json });
  return json;
}

async function main() {
  console.log(`Connecting to PocketBase at ${PB_URL}...`);

  // Authenticate as superuser (PocketBase v0.23+)
  const auth = await post('/api/collections/_superusers/auth-with-password', {
    identity: ADMIN_EMAIL,
    password: ADMIN_PASSWORD,
  });
  const token = auth.token;
  console.log('Authenticated as admin.\n');

  for (const col of COLLECTIONS) {
    try {
      await post('/api/collections', col, token);
      console.log(`  ✓ Created: ${col.name}`);
    } catch (err) {
      if (err.status === 400 && err.data?.data?.name?.code === 'validation_not_unique') {
        console.log(`  ~ Skipped: ${col.name} (already exists)`);
      } else {
        console.error(`  ✗ Failed:  ${col.name} — ${err.message}`);
      }
    }
  }

  console.log('\nDone. All collections are set up with open API rules.');
}

main().catch(err => {
  console.error('\nFatal error:', err.message);
  process.exit(1);
});