Database Schema

Overview

Pipeline uses Supabase (PostgreSQL 15+) with a carefully designed schema featuring Row-Level Security (RLS), soft deletes, audit logging, and full-text search capabilities.

Schema Version: 1.0.20 (13 migrations applied)All migrations are located in /supabase/migrations/

Core Tables

jobs

The main table storing all job applications and postings.

UUID

default:"gen_random_uuid()"

Primary key - unique job identifier

user_id

UUID

required

Foreign key to auth.users - job owner

company_name

VARCHAR(200)

required

Company name (non-empty, max 200 chars)

job_title

VARCHAR(200)

required

Position title (non-empty, max 200 chars)

job_description

TEXT

Full job description (max 50,000 chars)

job_url

TEXT

HTTPS-only URL to original posting

source

job_source

required

One of: brightermonday, fuzu, linkedin, manual

status

job_status

default:"'saved'"

One of: saved, applied, interview, offer, rejected

ai_match_score

INTEGER

AI-calculated match percentage (0-100)

ai_parsed_data

JSONB

Structured data extracted by AI (skills, requirements)

ai_reasoning

TEXT

AI explanation for the match score

applied_at

TIMESTAMPTZ

Application submission date

interview_at

TIMESTAMPTZ

Interview start date

offer_at

TIMESTAMPTZ

Offer received date

rejected_at

TIMESTAMPTZ

Rejection date

notes

TEXT

User notes (max 10,000 chars)

Check Constraints

AI match score between 0-100
Salary min ≤ max, min ≥ 30k, max ≤ 10M
Applied date before interview date
HTTPS-only URLs
Non-empty company name and job title
Max 20 tags, no empty strings

events

Immutable activity log for audit trail (append-only).

UUID

default:"gen_random_uuid()"

Primary key - unique event identifier

user_id

UUID

required

Foreign key to auth.users - event owner

job_id

UUID

Foreign key to jobs - related job (if applicable)

event_type

required

Event type enum (see Event Types below)

event_data

JSONB

Event payload (must be valid JSON object)

created_at

TIMESTAMPTZ

default:"NOW()"

Event timestamp

Events are append-only. No UPDATE or DELETE operations are allowed (enforced by RLS).

insights

AI-generated insights and recommendations with expiry management.

UUID

default:"gen_random_uuid()"

Primary key - unique insight identifier

user_id

UUID

required

Foreign key to auth.users - insight owner

insight_type

required

One of: daily_digest, pattern_detection, recommendation, weekly_summary, market_analysis

title

VARCHAR(200)

required

Insight title (non-empty)

content

TEXT

required

Insight content (non-empty)

metadata

JSONB

Additional structured data

created_at

TIMESTAMPTZ

default:"NOW()"

Creation timestamp

expires_at

TIMESTAMPTZ

Expiry timestamp (must be in the future)

scraper_runs

Scraper telemetry and statistics for monitoring.

UUID

default:"gen_random_uuid()"

Primary key - unique run identifier

user_id

UUID

Foreign key to auth.users - owner if manual run

source

job_source

required

Scraper source enum

status

scraper_status

required

One of: running, completed, failed, cancelled

started_at

TIMESTAMPTZ

default:"NOW()"

Run start timestamp

completed_at

TIMESTAMPTZ

Run completion timestamp

jobs_found

INTEGER

default:"0"

Total jobs found (≥ 0)

jobs_new

INTEGER

default:"0"

New jobs discovered (≥ 0)

jobs_imported

INTEGER

default:"0"

Jobs imported (≥ 0)

error_message

TEXT

Error details if failed

metadata

JSONB

Additional run data

audit_log

Comprehensive audit trail for compliance and debugging (system-managed).

UUID

default:"gen_random_uuid()"

Primary key

table_name

TEXT

required

Affected table name

record_id

UUID

required

Affected record ID

operation

TEXT

required

One of: INSERT, UPDATE, DELETE

old_data

JSONB

Before snapshot

new_data

JSONB

After snapshot

user_id

UUID

Acting user

created_at

TIMESTAMPTZ

default:"NOW()"

Timestamp

Audit log is service role only. Regular users cannot access directly.

Enums

job_status

CREATE TYPE job_status AS ENUM (
  'saved',      -- Draft, not yet applied
  'applied',    -- Application submitted
  'interview',  -- Interview process started
  'offer',      -- Offer received
  'rejected'    -- Rejected or not selected
);

Valid Status Transitions:

saved → applied, rejected
applied → interview, rejected, saved
interview → offer, rejected, applied, interview
offer → rejected, applied, saved
rejected → saved, applied

job_source

CREATE TYPE job_source AS ENUM (
  'brightermonday',  -- BrighterMonday Kenya
  'fuzu',            -- Fuzu Kenya
  'linkedin',        -- LinkedIn
  'manual'           -- Manually entered
);

event_type

CREATE TYPE event_type AS ENUM (
  'job_created',
  'status_changed',
  'ai_scored',
  'note_added',
  'application_sent',
  'interview_scheduled',
  'offer_received',
  'rejected',
  'gmail_auto_update',
  'calendar_event_created',
  'discord_command',
  'export_downloaded',
  'job_restored'  -- Added in migration 018
);

insight_type

CREATE TYPE insight_type AS ENUM (
  'daily_digest',
  'pattern_detection',
  'recommendation',
  'weekly_summary',
  'market_analysis'
);

scraper_status

CREATE TYPE scraper_status AS ENUM (
  'running',
  'completed',
  'failed',
  'cancelled'
);

Indexes

Performance Indexes

jobs table:

idx_jobs_user_status - User’s jobs by status
idx_jobs_user_created - User’s jobs by date (DESC)
idx_jobs_user_source - User’s jobs by source
idx_jobs_user_company - User’s companies
idx_jobs_search_weighted - Full-text search (GIN)
idx_jobs_ai_match_score - Top AI matches (DESC NULLS LAST)
idx_jobs_deleted_at - Soft deleted jobs (partial index)

events table:

idx_events_user_created - User’s events by date (DESC)
idx_events_job_id - Job’s events
idx_events_user_type - User’s events by type

insights table:

idx_insights_user_id - User’s insights
idx_insights_created_at - Recent insights (DESC)
idx_insights_expires_at - Expiring insights (partial index)

scraper_runs table:

idx_scraper_runs_started_at - Recent runs (DESC)
idx_scraper_runs_user_started - User’s runs by date

Database Functions

Full-Text Search

CREATE FUNCTION search_jobs(
  search_query TEXT,
  max_results INTEGER DEFAULT 50
)
RETURNS TABLE (
  id UUID,
  company_name TEXT,
  job_title TEXT,
  rank REAL
);

Search Weights:

A (highest): job_title, company_name
B (medium): job_description
C (lowest): tags

Soft Delete Utilities

SELECT soft_delete_record('jobs', 'job-uuid-here');

Cleanup Functions

-- Preview old deleted records (dry run)
SELECT * FROM cleanup_old_deleted_records(
  older_than_days := 30,
  dry_run := true
);

-- Actually delete old records
SELECT * FROM cleanup_old_deleted_records(
  older_than_days := 30,
  dry_run := false
);

Triggers

Automated Triggers

trigger_update_updated_at

Table: jobsEvent: BEFORE UPDATEFunction: update_updated_at_column()Purpose: Automatically updates updated_at timestamp on every job update

trigger_validate_status_transition

Table: jobsEvent: BEFORE UPDATEFunction: validate_job_status_transition()Purpose: Validates status changes follow allowed transition rules. Raises exception for invalid transitions.

trigger_log_job_changes

Table: jobsEvent: AFTER INSERT OR UPDATEFunction: log_job_changes()Purpose: Logs job changes to events table for audit trail

trigger_update_search_vector

Table: jobsEvent: BEFORE INSERT OR UPDATEFunction: update_search_vector()Purpose: Updates weighted full-text search vector for search functionality

Entity Relationship Diagram

Data Flow Examples

Job Creation Flow

Status Transition Flow

Migration History

001_tables.sql

Core tables and enums

002_indexes.sql

Performance indexes

003_triggers.sql

Automated triggers

004_rls.sql

Row-Level Security policies

005_audit.sql

Audit logging system

006_soft_deletes.sql

Soft delete utilities

007-019

Bug fixes and security enhancements

Next Steps

RLS Policies

Learn about Row-Level Security implementation

Deployment

Deploy database to production

Get Started

Core Features

Guides

Architecture

Overview

Core Tables

jobs

Check Constraints

events

insights

scraper_runs

audit_log

Enums

job_status

job_source

event_type

insight_type

scraper_status

Indexes

Performance Indexes

Database Functions

Full-Text Search

Soft Delete Utilities

Cleanup Functions

Triggers

Automated Triggers

Entity Relationship Diagram

Data Flow Examples

Job Creation Flow

Status Transition Flow

Migration History

Next Steps

RLS Policies

Deployment

Build docs developers (and LLMs) love

Get Started

Core Features

Guides

Architecture

​Overview

​Core Tables

​jobs

​Check Constraints

​events

​insights

​scraper_runs

​audit_log

​Enums

​job_status

​job_source

​event_type

​insight_type

​scraper_status

​Indexes

​Performance Indexes

​Database Functions

​Full-Text Search

​Soft Delete Utilities

​Cleanup Functions

​Triggers

​Automated Triggers

​Entity Relationship Diagram

​Data Flow Examples

​Job Creation Flow

​Status Transition Flow

​Migration History

​Next Steps

RLS Policies

Deployment

Build docs developers (and LLMs) love

Overview

Core Tables

jobs

Check Constraints

events

insights

scraper_runs

audit_log

Enums

job_status

job_source

event_type

insight_type

scraper_status

Indexes

Performance Indexes

Database Functions

Full-Text Search

Soft Delete Utilities

Cleanup Functions

Triggers

Automated Triggers

Entity Relationship Diagram

Data Flow Examples

Job Creation Flow

Status Transition Flow

Migration History

Next Steps