Encore OS — Documentation

Status: DEFERRED - Schedule for next deployment cycle
Created: 2026-02-08
Author: AI Assistant
Target Release: 2026-02-15 (or later after code validation)

Overview

Three database migrations are required to complete HR-09-P5 implementation:

HR References: Secure token storage via hashing
Candidate Portal Sessions: Organization isolation
Job Board Integrations: Centralized credential management

Each migration requires code changes and a transition period to maintain backward compatibility.

Migration 1: hr_references - Token Hashing (Phase 5.1)

Purpose

Migrate reference submission tokens from plaintext storage to cryptographically hashed format to comply with security best practices and reduce data breach risk.

SQL Migration

-- Add access_token_hash column
ALTER TABLE hr_references 
  ADD COLUMN access_token_hash TEXT;

-- Create index for fast hash lookups during token validation
CREATE INDEX idx_hr_references_access_token_hash 
  ON hr_references(access_token_hash) 
  WHERE access_token_hash IS NOT NULL;

-- Document deprecation of plaintext token
COMMENT ON COLUMN hr_references.access_token IS 
  'DEPRECATED (v2026-02-08): Use access_token_hash for security. Plaintext tokens will be removed after migration period.';

-- Ensure audit columns exist
ALTER TABLE hr_references 
  ADD COLUMN IF NOT EXISTS created_by UUID REFERENCES pf_profiles(id),
  ADD COLUMN IF NOT EXISTS updated_by UUID REFERENCES pf_profiles(id);

-- Create trigger for updated_at maintenance
CREATE OR REPLACE FUNCTION public.update_hr_references_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_hr_references_updated_at ON hr_references;
CREATE TRIGGER update_hr_references_updated_at
  BEFORE UPDATE ON hr_references
  FOR EACH ROW
  EXECUTE FUNCTION public.update_hr_references_updated_at();

Code Changes Required

Edge Function: supabase/functions/hr-reference-submit/index.ts

// On token CREATION:
// 1. Generate random 16-byte salt
// 2. Hash token: SHA-256(token + salt)
// 3. Store: Base64(salt + hash)

const crypto = await import('https://deno.land/std/crypto/mod.ts');

function hashToken(token: string, salt: Uint8Array): string {
  const encoder = new TextEncoder();
  const tokenBytes = encoder.encode(token);
  const combined = new Uint8Array([...salt, ...tokenBytes]);
  const hash = crypto.subtle.digestSync('SHA-256', combined);
  const combined64 = btoa(String.fromCharCode(...new Uint8Array(combined)));
  return combined64; // Base64(salt + hash)
}

// On token VALIDATION:
// 1. Receive plaintext token from referee
// 2. Decode stored Base64 to get salt
// 3. Hash presented token with same salt
// 4. Compare hashes

function validateToken(presentedToken: string, storedHash: string): boolean {
  const decoded = atob(storedHash);
  const salt = new Uint8Array(16);
  for (let i = 0; i < 16; i++) {
    salt[i] = decoded.charCodeAt(i);
  }
  
  const presented = hashToken(presentedToken, salt);
  return presented === storedHash;
}

Transition Strategy

Phase 1: Dual Support (Week 1-2)

New reference requests: Use hashed tokens
Existing references: Still accept plaintext tokens
Log all token validations for audit

Phase 2: Migration (Week 3-4)

Run data migration script to hash existing plaintext tokens
Verify all existing references can still be accessed
Monitor error logs for validation failures

Phase 3: Deprecation (Week 5+)

Stop generating plaintext tokens
Require hashed tokens for all new validations
Schedule column removal after 30-day deprecation period

Rollback Plan

If issues arise before column removal:

Keep access_token plaintext column indefinitely
Continue dual-path validation (hash first, then plaintext)
Document why hashing was reverted

Migration 2: hr_candidate_portal_sessions - Organization Isolation

Purpose

Add organization_id column to enforce tenant isolation and enable RLS policies. Currently sessions may lack org context, preventing proper multi-tenant access control.

SQL Migration

-- Add organization_id column (initially nullable for existing rows)
ALTER TABLE hr_candidate_portal_sessions
  ADD COLUMN organization_id UUID;

-- Create foreign key constraint
ALTER TABLE hr_candidate_portal_sessions
  ADD CONSTRAINT fk_hr_candidate_portal_sessions_organization_id
  FOREIGN KEY (organization_id) REFERENCES pf_organizations(id) ON DELETE CASCADE;

-- Create index for fast org-scoped lookups
CREATE INDEX idx_hr_candidate_portal_sessions_organization_id 
  ON hr_candidate_portal_sessions(organization_id);

-- Backfill organization_id from candidate relationship
-- (assumes hr_candidates table has organization_id)
UPDATE hr_candidate_portal_sessions s
SET organization_id = c.organization_id
FROM hr_candidates c
WHERE s.candidate_id = c.id
  AND s.organization_id IS NULL;

-- Update RLS policies for organization isolation
DROP POLICY IF EXISTS "Sessions org isolation" ON hr_candidate_portal_sessions;
CREATE POLICY "Sessions org isolation" 
  ON hr_candidate_portal_sessions
  USING (
    organization_id = (
      current_setting('request.jwt.claims', true)::json->>'organization_id'
    )::uuid
  );

-- Make organization_id NOT NULL after backfill succeeds
ALTER TABLE hr_candidate_portal_sessions
  ALTER COLUMN organization_id SET NOT NULL;

-- Create trigger for updated_at maintenance
CREATE OR REPLACE FUNCTION public.update_hr_candidate_portal_sessions_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_hr_candidate_portal_sessions_updated_at ON hr_candidate_portal_sessions;
CREATE TRIGGER update_hr_candidate_portal_sessions_updated_at
  BEFORE UPDATE ON hr_candidate_portal_sessions
  FOR EACH ROW
  EXECUTE FUNCTION public.update_hr_candidate_portal_sessions_updated_at();

Code Changes Required

Hook: src/cores/hr/hooks/useCandidatePortalSession.ts

// BEFORE: No organization_id required
const createSession = async (candidateId: string) => {
  return supabase
    .from('hr_candidate_portal_sessions')
    .insert({ candidate_id: candidateId });
};

// AFTER: Include organization_id
const createSession = async (candidateId: string, organizationId: string) => {
  return supabase
    .from('hr_candidate_portal_sessions')
    .insert({ 
      candidate_id: candidateId,
      organization_id: organizationId  // NEW
    });
};

Portal Component: Ensure organization context is passed when creating sessions.

Transition Strategy

Pre-Migration:

Code changes deployed (accepting but not requiring organization_id)
All new sessions created WITH organization_id
Existing sessions continue to work

Migration Day:

Run SQL backfill
Verify RLS policies work correctly
Test portal access with org-scoped sessions
Make organization_id NOT NULL

Post-Migration:

Enforce organization_id in application code
Monitor RLS errors in logs
Remove code paths that don’t set organization_id

Migration 3: hr_job_board_integrations - Credential Encryption

Purpose

Move API keys, secrets, and refresh tokens to centralized pf_credentials table for encryption at rest and audit logging. Plaintext credentials in hr_job_board_integrations pose security risk.

SQL Migration

-- Add credential_id column (initially nullable)
ALTER TABLE hr_job_board_integrations
  ADD COLUMN credential_id UUID;

-- Create foreign key constraint
ALTER TABLE hr_job_board_integrations
  ADD CONSTRAINT fk_hr_job_board_integrations_credential_id
  FOREIGN KEY (credential_id) REFERENCES pf_credentials(id) ON DELETE SET NULL;

-- Create index for credential lookups
CREATE INDEX idx_hr_job_board_integrations_credential_id 
  ON hr_job_board_integrations(credential_id);

-- Document deprecation of plaintext columns
COMMENT ON COLUMN hr_job_board_integrations.api_key IS 
  'DEPRECATED (v2026-02-08): Use credential_id with pf_credentials table. Plaintext key will be removed after migration period.';
COMMENT ON COLUMN hr_job_board_integrations.api_secret IS 
  'DEPRECATED (v2026-02-08): Use credential_id with pf_credentials table. Plaintext secret will be removed after migration period.';
COMMENT ON COLUMN hr_job_board_integrations.refresh_token IS 
  'DEPRECATED (v2026-02-08): Use credential_id with pf_credentials table. Plaintext token will be removed after migration period.';

-- Create migration log table for audit trail
CREATE TABLE IF NOT EXISTS pf_credential_migrations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name TEXT NOT NULL,
  record_id UUID NOT NULL,
  credential_type TEXT NOT NULL,
  migrated_at TIMESTAMPTZ DEFAULT now(),
  migrated_by UUID REFERENCES pf_profiles(id),
  notes TEXT
);

ALTER TABLE pf_credential_migrations ENABLE ROW LEVEL SECURITY;

Credential Migration Helper (Manual Process)

// Script: migrate-job-board-credentials.ts
// Run after migration to move plaintext credentials to pf_credentials

import { createClient } from '@supabase/supabase-js';

const supabaseAdmin = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_SERVICE_ROLE_KEY
);

async function migrateJobBoardIntegrations() {
  // Fetch all integrations with plaintext credentials
  const { data: integrations } = await supabaseAdmin
    .from('hr_job_board_integrations')
    .select('*')
    .is('credential_id', null)
    .not('api_key', 'is', null);

  for (const integration of integrations || []) {
    // Create credential record
    const { data: credential } = await supabaseAdmin
      .from('pf_credentials')
      .insert({
        organization_id: integration.organization_id,
        credential_type: `job_board_${integration.job_board_type}`,
        name: `${integration.job_board_type} - Migrated`,
        encrypted_value: JSON.stringify({
          api_key: integration.api_key,
          api_secret: integration.api_secret,
          refresh_token: integration.refresh_token,
        }),
        created_by: integration.created_by,
      })
      .select()
      .single();

    // Link integration to credential
    await supabaseAdmin
      .from('hr_job_board_integrations')
      .update({ credential_id: credential.id })
      .eq('id', integration.id);

    // Log migration
    await supabaseAdmin
      .from('pf_credential_migrations')
      .insert({
        table_name: 'hr_job_board_integrations',
        record_id: integration.id,
        credential_type: `job_board_${integration.job_board_type}`,
        migrated_by: 'system',
        notes: `Migrated plaintext credentials to pf_credentials`,
      });
  }
}

Code Changes Required

Hook: src/cores/hr/hooks/useJobBoardIntegrations.ts

// BEFORE: Read plaintext from table
const fetchIntegrations = async (orgId: string) => {
  return supabase
    .from('hr_job_board_integrations')
    .select('id, job_board_type, api_key, api_secret')
    .eq('organization_id', orgId);
};

// AFTER: Fetch via credential relationship
const fetchIntegrations = async (orgId: string) => {
  return supabase
    .from('hr_job_board_integrations')
    .select(`
      id,
      job_board_type,
      credential_id,
      credential:pf_credentials!credential_id(id, encrypted_value)
    `)
    .eq('organization_id', orgId);
};

// Decrypt credentials when needed (server-side edge function)
async function decryptCredential(credentialId: string) {
  const { data } = await supabase
    .from('pf_credentials')
    .select('encrypted_value')
    .eq('id', credentialId)
    .single();
  
  return decryptAES256GCM(data.encrypted_value);
}

Transition Strategy

Phase 1: Dual Support (Week 1-2)

Read from credential_id if set, else fallback to plaintext columns
New integrations: Require credential_id
Existing integrations: Continue to work with plaintext

Phase 2: Migration (Week 3-4)

Run migration script to move all plaintext credentials to pf_credentials
Verify all integrations still sync correctly
Monitor edge function logs for credential lookup failures

Phase 3: Enforcement (Week 5+)

Require credential_id for all read/write operations
Remove fallback to plaintext columns
Schedule column removal after 30-day deprecation

Migration Validation Checklist

Before applying migrations:

Code changes deployed and tested
Rollback procedures documented
Data backup taken (per Supabase backup policy)
RLS policies reviewed and tested
Audit logging configured
Team notified of maintenance window
Monitoring alerts configured

During migration:

SQL scripts executed successfully
Backfill completed without errors
RLS policies tested with real user sessions
Logs monitored for errors
Rollback procedure rehearsed

After migration:

TypeScript Type Updates

After migrations are applied, regenerate Supabase types:

npx supabase gen types typescript --local > src/integrations/supabase/types.ts

Then update components to include new columns:

// hr_references.ts
export interface HRReference {
  id: string;
  access_token?: string; // DEPRECATED
  access_token_hash?: string; // NEW
  // ... other fields
}

// hr_candidate_portal_sessions.ts
export interface HRCandidatePortalSession {
  id: string;
  organization_id: string; // NEW
  candidate_id: string;
  // ... other fields
}

// hr_job_board_integrations.ts
export interface HRJobBoardIntegration {
  id: string;
  credential_id?: string; // NEW
  api_key?: string; // DEPRECATED
  api_secret?: string; // DEPRECATED
  refresh_token?: string; // DEPRECATED
  // ... other fields
}

Deployment Timeline

Target: Week of Feb 15, 2026 (flexible based on validation)

Date	Activity	Owner
Feb 8-10	Code review & testing	Dev Team
Feb 10-12	Staging migration run	DevOps
Feb 12-14	Documentation review	Tech Lead
Feb 15 (evening)	Production migration	DevOps + Dev Team on-call
Feb 16-17	Monitoring & hotfix readiness	Full Team

Rollback Procedures

Migration 1: Token Hashing

Keep plaintext access_token column
Revert edge function to plaintext token validation
RLS policies unchanged

Migration 2: Organization Isolation

Backfill organization_id from hr_candidates again
Restore RLS policies to previous (less strict) version
No data loss

Migration 3: Credential Encryption

Keep plaintext columns populated
Revert hooks to read from plaintext columns
Migrate pf_credentials back to plaintext table
No data loss

Contact & Support

Questions: Post in #database-migrations Slack channel
Issues during migration: Contact DevOps on-call
Post-migration issues: File bug report with error logs

​Overview

​Migration 1: hr_references - Token Hashing (Phase 5.1)

​Purpose

​SQL Migration

​Code Changes Required

​Transition Strategy

​Rollback Plan

​Migration 2: hr_candidate_portal_sessions - Organization Isolation

​Purpose

​SQL Migration

​Code Changes Required

​Transition Strategy

​Migration 3: hr_job_board_integrations - Credential Encryption

​Purpose

​SQL Migration

​Credential Migration Helper (Manual Process)

​Code Changes Required

​Transition Strategy

​Migration Validation Checklist

​TypeScript Type Updates

​Deployment Timeline

​Rollback Procedures

​Migration 1: Token Hashing

​Migration 2: Organization Isolation

​Migration 3: Credential Encryption

​Contact & Support

Overview

Migration 1: hr_references - Token Hashing (Phase 5.1)

Purpose

SQL Migration

Code Changes Required

Transition Strategy

Rollback Plan

Migration 2: hr_candidate_portal_sessions - Organization Isolation

Purpose

SQL Migration

Code Changes Required

Transition Strategy

Migration 3: hr_job_board_integrations - Credential Encryption

Purpose

SQL Migration

Credential Migration Helper (Manual Process)

Code Changes Required

Transition Strategy

Migration Validation Checklist

TypeScript Type Updates

Deployment Timeline

Rollback Procedures

Migration 1: Token Hashing

Migration 2: Organization Isolation

Migration 3: Credential Encryption

Contact & Support