Tenant Provisioning Runbook

Broker (tenant)
  └── Employers (companies the broker serves)
        └── Pension Schemes
              └── Members
                    └── Contributions, KFH records, compliance documents

# Option A: psql direct connection (requires DATABASE_URL_UNPOOLED from Vercel env)
psql $DATABASE_URL_UNPOOLED

# Option B: Drizzle Studio (browser UI — easier for non-SQL workflows)
npm run db:studio
# Opens at https://local.drizzle.studio

-- Generate a new UUID to use as the brokerId
SELECT gen_random_uuid();
-- Example output: 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
-- Copy this value — you will use it in Steps 3 and 6

# Generate a random temporary password
node -e "console.log(require('crypto').randomBytes(16).toString('hex'))"
# Output: a 32-character hex string — use this as the temporary password

# Generate a bcrypt hash of the temporary password (work factor 12)
node -e "const bcrypt = require('bcryptjs'); bcrypt.hash('PASTE_TEMP_PASSWORD_HERE', 12).then(h => console.log(h))"

INSERT INTO users (
  id,
  email,
  password_hash,
  role,
  broker_id,
  full_name,
  created_at,
  updated_at
) VALUES (
  gen_random_uuid(),
  'admin@brokerfirm.ie',            -- replace with actual broker contact email
  '$2b$12$...paste_hash_here...',   -- bcrypt hash from command above
  'BrokerAdmin',
  'a1b2c3d4-e5f6-7890-abcd-ef1234567890',  -- replace with brokerId from Step 2
  'Firstname Lastname',             -- replace with actual full name
  NOW(),
  NOW()
);

-- Confirm the insert
SELECT id, email, role, broker_id, full_name, created_at
FROM users
WHERE email = 'admin@brokerfirm.ie';

Your PensionsPortal.ie account has been created.

Login URL:          https://app.pensionsportal.ie
Email:              [broker contact email]
Temporary password: [temporary password]

IMPORTANT: Change your password immediately after first login.
Navigate to: Account Settings → Change Password

Getting started guide: https://docs.pensionsportal.ie/quickstart

If you have any issues logging in, contact: engineering@pensionsportal.ie

-- Verify audit log entries were created during verification
SELECT action, entity_type, timestamp
FROM audit_logs
WHERE actor_id = (SELECT id FROM users WHERE email = 'admin@brokerfirm.ie')
ORDER BY timestamp DESC
LIMIT 20;

INSERT INTO audit_logs (
  actor_id,
  action,
  entity_type,
  entity_id,
  notes,
  timestamp
) VALUES (
  (SELECT id FROM users WHERE role = 'SuperAdmin' LIMIT 1),  -- use your SuperAdmin user ID
  'TENANT_PROVISIONED',
  'broker',
  'a1b2c3d4-e5f6-7890-abcd-ef1234567890',  -- brokerId from Step 2
  'New broker onboarded: [Broker Firm Legal Name] — DPA signed [date] — primary contact: [email]',
  NOW()
);

-- Remove the partially-created user (within the last hour as a safety guard)
DELETE FROM users
WHERE broker_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
  AND created_at > NOW() - INTERVAL '1 hour';

-- Remove any employers created during testing (if Step 5 was partially completed)
DELETE FROM employers
WHERE broker_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
  AND created_at > NOW() - INTERVAL '1 hour';

INSERT INTO audit_logs (
  actor_id,
  action,
  entity_type,
  entity_id,
  notes,
  timestamp
) VALUES (
  (SELECT id FROM users WHERE role = 'SuperAdmin' LIMIT 1),
  'TENANT_PROVISIONING_ROLLED_BACK',
  'broker',
  'a1b2c3d4-e5f6-7890-abcd-ef1234567890',
  'Provisioning failed at step [N] — rolled back. Reason: [describe failure]',
  NOW()
);

# Generate a full data export for the broker
# Export scheme and member data scoped to this brokerId
pg_dump $DATABASE_URL_UNPOOLED \
  --format=custom \
  --no-owner \
  --no-acl \
  --file="broker-export-$(date +%Y%m%d).dump"

# Planned: A SuperAdmin export endpoint at /api/admin/export/[brokerId] is a roadmap item.
# Until implemented, export manually via pg_dump as above, then filter by brokerId
# when delivering the export to the broker.

-- Anonymise all members belonging to this broker's schemes
-- WARNING: Irreversible. Confirm legal hold status before executing.
UPDATE members SET
  first_name            = 'DELETED',
  last_name             = 'DELETED',
  email                 = NULL,
  phone                 = NULL,
  pps_number_encrypted  = NULL,
  date_of_birth         = NULL,
  updated_at            = NOW()
WHERE scheme_id IN (
  SELECT s.id FROM schemes s
  JOIN employers e ON e.id = s.employer_id
  WHERE e.broker_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
);

UPDATE users
SET role = 'Disabled', updated_at = NOW()
WHERE broker_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';

-- Read-only verification that records are retained
SELECT COUNT(*) AS schemes_retained
FROM schemes s
JOIN employers e ON e.id = s.employer_id
WHERE e.broker_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';

INSERT INTO audit_logs (
  actor_id,
  action,
  entity_type,
  entity_id,
  notes,
  timestamp
) VALUES (
  (SELECT id FROM users WHERE role = 'SuperAdmin' LIMIT 1),
  'TENANT_OFFBOARDED',
  'broker',
  'a1b2c3d4-e5f6-7890-abcd-ef1234567890',
  'Broker offboarded: [Broker Firm Legal Name]. Data exported [date], PII anonymised, users disabled. Scheme records retained under 7-year statutory requirement.',
  NOW()
);