Yeti Jobs Database Schema: Tables and Relationships

Yeti Jobs is backed by a normalized PostgreSQL schema that enforces data integrity at every layer — from enum-constrained columns and cascading foreign keys to a GIN-indexed tsvector column for full-text job search. The schema is split across 14 numbered SQL files and executed in order during migration, ensuring every dependency (extensions → enums → tables → indexes → triggers) is created before it is referenced.

Extensions

Before any tables or types are created, the pgcrypto extension is loaded. This enables gen_random_uuid(), which every table uses to generate uuid primary keys by default.

create extension if not exists "pgcrypto";

Enums

All enumerated types are created with DO $$ BEGIN ... EXCEPTION WHEN duplicate_object THEN null END $$ guards so that re-running the file against an existing database is safe.

role_type — User roles

Controls what actions a user can perform across the platform.

create type "role_type" as enum ('guest', 'admin', 'recruiter');

Value	Description
`guest`	Default role for new sign-ups; can browse and apply to jobs
`admin`	Platform administrator; can manage companies and users
`recruiter`	Company employee; can create and manage job listings

user_degree_type — Education levels

Used on the users table and user_educations table to categorise a user’s highest qualification.

create type "user_degree_type" as enum (
  'Basic', 'Matrix', 'Undergraduation', 'Postgraduation', 'High School'
);

Value	Description
`Basic`	Default; general or unspecified education
`Matrix`	Secondary school matriculation
`High School`	High school diploma
`Undergraduation`	Bachelor’s degree
`Postgraduation`	Master’s degree or above

location_type_option — Job work arrangement

Defines where the job is physically performed.

create type "public"."location_type_option" as enum ('Remote', 'Onsite', 'Hybrid');

Value	Description
`Remote`	Fully remote, no office attendance required
`Onsite`	Full-time in-office; default for new job listings
`Hybrid`	Mix of remote and in-office

is_job_open — Job listing state

Tracks whether a job posting is accepting applications. The nightly cron job automatically flips listings from active to closed when they exceed 30 days.

create type "public"."is_job_open" as enum ('active', 'closed');

Value	Description
`active`	Listing is live and accepting applications
`closed`	Listing is expired or manually closed

application_status — Application pipeline stage

Tracks where a candidate’s application sits in the recruiter’s hiring pipeline.

create type "public"."application_status" as enum (
  'applied', 'rejected', 'hired', 'shortlisted'
);

Value	Description
`applied`	Default; application submitted, awaiting review
`shortlisted`	Moved forward for further consideration
`hired`	Candidate has been offered and accepted the role
`rejected`	Application declined

email_verification_type — Email token purpose

Distinguishes between the two token flows that share the email_verified table.

create type "public"."email_verification_type" as enum (
  'verify_mail', 'forget_password'
);

Value	Description
`verify_mail`	Token sent on sign-up to confirm the email address
`forget_password`	Token sent during a password-reset request

notifications_type — Notification categories

Categorises every in-app notification a user can receive.

create type notifications_type as enum (
  'new_jobs', 'application_status', 'job_alert', 'bookmark_reminder',
  'company_follow', 'application_recieved', 'profile_view',
  'message_recieved', 'resume_analysed', 'announcement'
);

Value	Description
`new_jobs`	A new job has been posted
`application_status`	A recruiter changed the status of an application
`job_alert`	A saved search or alert triggered
`bookmark_reminder`	Reminder about a bookmarked job
`company_follow`	A user followed a company
`application_recieved`	Recruiter received a new application
`profile_view`	Someone viewed the user’s profile
`message_recieved`	An in-platform message arrived
`resume_analysed`	ATS scoring completed for a resume
`announcement`	Platform-wide announcement

Tables

companies

The root entity. Users with the recruiter role belong to a company, and jobs are always posted under a company. Many other tables cascade-delete when a company row is removed.

create TABLE if not exists companies (
    uid         uuid        not null default gen_random_uuid(),
    name        text        not null,
    description text,
    website     text,
    created_at  timestamptz default CURRENT_TIMESTAMP,
    founded_year int2       not null default 2026,
    location    text,
    logo_url    text,
    primary key (uid)
);

Column details — companies

Column	Type	Nullable	Default	Notes
`uid`	`uuid`	NOT NULL	`gen_random_uuid()`	Primary key
`name`	`text`	NOT NULL	—	Company display name; indexed with btree for fast lookups
`description`	`text`	nullable	—	Free-text company bio
`website`	`text`	nullable	—	Company website URL
`created_at`	`timestamptz`	nullable	`CURRENT_TIMESTAMP`	Row creation timestamp
`founded_year`	`int2`	NOT NULL	`2026`	Year the company was founded
`location`	`text`	nullable	—	Headquarters location
`logo_url`	`text`	nullable	—	Supabase Storage public URL for the company logo

users

Stores all platform users regardless of role. A company_id foreign key links recruiters to their employer; it is NULL for job-seeking guests.

create TABLE if not exists users (
    uid             uuid             not null default gen_random_uuid(),
    fname           text             not null,
    lname           text,
    education       user_degree_type not null default 'Basic'::user_degree_type,
    email           text,
    password        text,
    role            role_type        not null default 'guest'::role_type,
    company_id      uuid,
    resume_url      text,
    profile_pic_url text,
    skills          _text,
    experience      int4,
    constraint users_company_id_fkey
        foreign key (company_id) references companies(uid) ON DELETE cascade,
    primary key (uid)
);

Column details — users

Column	Type	Nullable	Default	Notes
`uid`	`uuid`	NOT NULL	`gen_random_uuid()`	Primary key
`fname`	`text`	NOT NULL	—	First name
`lname`	`text`	nullable	—	Last name
`education`	`user_degree_type`	NOT NULL	`'Basic'`	Highest qualification enum
`email`	`text`	nullable	—	Unique (enforced by `users_email_key` index); used for login
`password`	`text`	nullable	—	bcrypt-hashed password
`role`	`role_type`	NOT NULL	`'guest'`	Access control role
`company_id`	`uuid`	nullable	—	FK → `companies.uid`; cascades on delete
`resume_url`	`text`	nullable	—	Supabase Storage URL of uploaded resume PDF
`profile_pic_url`	`text`	nullable	—	Supabase Storage URL of profile picture
`skills`	`text[]`	nullable	—	Postgres text array of skill tags
`experience`	`int4`	nullable	—	Years of work experience

jobs

The core listing table. Every job belongs to a company and is created by a user (recruiter). The search_title column is a tsvector automatically maintained by the vector_search_update trigger.

create TABLE if not exists jobs (
    uid              uuid                  not null default gen_random_uuid(),
    title            text                  not null,
    description      text,
    salary           int8                  not null,
    job_type         location_type_option  default 'Onsite'::location_type_option,
    is_job_open      is_job_open           not null default 'active'::is_job_open,
    company_id       uuid,
    created_by       uuid                  not null,
    search_title     tsvector,
    skills           _text,
    total_job_views  int8                  not null default '0'::bigint,
    created_at       date                  default CURRENT_DATE,
    experience_years int4,
    location         text,
    expired_at       date                  default (CURRENT_DATE + 30),
    constraint jobs_company_id_fkey
        foreign key (company_id) references companies(uid),
    constraint jobs_created_by_fkey
        foreign key (created_by) references users(uid),
    primary key (uid)
);

Column details — jobs

Column	Type	Nullable	Default	Notes
`uid`	`uuid`	NOT NULL	`gen_random_uuid()`	Primary key
`title`	`text`	NOT NULL	—	Job title; GIN-indexed via `search_title` tsvector
`description`	`text`	nullable	—	Full job description
`salary`	`int8`	NOT NULL	—	Annual salary figure
`job_type`	`location_type_option`	nullable	`'Onsite'`	Work arrangement enum
`is_job_open`	`is_job_open`	NOT NULL	`'active'`	Listing state; cron job closes expired listings
`company_id`	`uuid`	nullable	—	FK → `companies.uid`
`created_by`	`uuid`	NOT NULL	—	FK → `users.uid` (the recruiter who posted the job)
`search_title`	`tsvector`	nullable	—	Auto-populated by trigger; powers GIN full-text search
`skills`	`text[]`	nullable	—	Required skill tags for the role
`total_job_views`	`int8`	NOT NULL	`0`	View counter incremented on each listing visit
`created_at`	`date`	nullable	`CURRENT_DATE`	Posting date
`experience_years`	`int4`	nullable	—	Minimum years of experience required
`location`	`text`	nullable	—	Office or city location for the role
`expired_at`	`date`	nullable	`CURRENT_DATE + 30`	Auto-close date; set 30 days from creation

applications

Records a user’s application to a job. Recruiters update status to move candidates through the hiring pipeline.

create TABLE if not exists applications (
    user_id         uuid,
    job_id          uuid,
    status          application_status default 'applied'::application_status,
    applied_at      timestamptz        default CURRENT_TIMESTAMP,
    uid             uuid               not null default gen_random_uuid(),
    cover_letter    text,
    notice_period   int4               not null default 0,
    expected_salary int8               not null default 0,
    why_hire        text,
    constraint applications_job_id_fkey
        foreign key (job_id) references jobs(uid),
    constraint applications_user_id_fkey
        foreign key (user_id) references users(uid),
    primary key (uid)
);

Column details — applications

Column	Type	Nullable	Default	Notes
`uid`	`uuid`	NOT NULL	`gen_random_uuid()`	Primary key
`user_id`	`uuid`	nullable	—	FK → `users.uid` (the applicant)
`job_id`	`uuid`	nullable	—	FK → `jobs.uid`
`status`	`application_status`	nullable	`'applied'`	Current pipeline stage
`applied_at`	`timestamptz`	nullable	`CURRENT_TIMESTAMP`	Submission timestamp
`cover_letter`	`text`	nullable	—	Optional cover letter body
`notice_period`	`int4`	NOT NULL	`0`	Days notice required before starting
`expected_salary`	`int8`	NOT NULL	`0`	Candidate’s salary expectation
`why_hire`	`text`	nullable	—	Candidate’s pitch statement

saved_jobs

Bookmarks linking a user to a job they want to revisit. Denormalises company_id to avoid a join when listing bookmarks with company details.

create TABLE if not exists saved_jobs (
    uid        uuid        not null default gen_random_uuid(),
    company_id uuid,
    job_id     uuid,
    created_at timestamptz default CURRENT_TIMESTAMP,
    user_id    uuid,
    constraint saved_jobs_company_id_fkey
        foreign key (company_id) references companies(uid),
    constraint saved_jobs_job_id_fkey
        foreign key (job_id) references jobs(uid) on delete cascade,
    constraint saved_jobs_user_id_fkey
        foreign key (user_id) references users(uid),
    primary key (uid)
);

Column details — saved_jobs

Column	Type	Nullable	Default	Notes
`uid`	`uuid`	NOT NULL	`gen_random_uuid()`	Primary key
`user_id`	`uuid`	nullable	—	FK → `users.uid`
`job_id`	`uuid`	nullable	—	FK → `jobs.uid`; cascades on delete
`company_id`	`uuid`	nullable	—	FK → `companies.uid`; denormalised for fast bookmark queries
`created_at`	`timestamptz`	nullable	`CURRENT_TIMESTAMP`	Bookmark creation timestamp

user_companies_follows

Join table between users and the companies they follow. A unique partial index on (user_id, company_id) prevents duplicate follow rows.

create TABLE if not exists user_companies_follows (
    uid        uuid        not null default gen_random_uuid() primary key,
    user_id    uuid        not null,
    company_id uuid        not null,
    created_at timestamptz default CURRENT_TIMESTAMP,
    constraint user_companies_follows_company_id_fkey
        foreign key (company_id) references companies(uid) on delete cascade,
    constraint user_companies_follows_user_id_fkey
        foreign key (user_id) references users(uid) on delete cascade
);

Column details — user_companies_follows

Column	Type	Nullable	Default	Notes
`uid`	`uuid`	NOT NULL	`gen_random_uuid()`	Primary key
`user_id`	`uuid`	NOT NULL	—	FK → `users.uid`; cascades on delete
`company_id`	`uuid`	NOT NULL	—	FK → `companies.uid`; cascades on delete
`created_at`	`timestamptz`	nullable	`CURRENT_TIMESTAMP`	Follow creation timestamp

email_verified

Stores short-lived numeric verification codes for both email confirmation and password reset flows. Each code expires 15 minutes after creation.

create TABLE if not exists email_verified (
    uid           uuid                    not null default gen_random_uuid(),
    user_id       uuid,
    verified_type email_verification_type default 'verify_mail'::email_verification_type,
    created_at    timestamptz             default now(),
    expired_at    timestamptz             default (now() + '00:15:00'::interval),
    verified_code int4,
    is_verified   bool                    default false,
    constraint email_verified_user_id_fkey
        foreign key (user_id) references users(uid) on delete cascade,
    primary key (uid)
);

Column details — email_verified

Column	Type	Nullable	Default	Notes
`uid`	`uuid`	NOT NULL	`gen_random_uuid()`	Primary key
`user_id`	`uuid`	nullable	—	FK → `users.uid`; cascades on delete
`verified_type`	`email_verification_type`	nullable	`'verify_mail'`	Token purpose: account confirmation or password reset
`created_at`	`timestamptz`	nullable	`now()`	Token issue timestamp
`expired_at`	`timestamptz`	nullable	`now() + 15 min`	Token expiry — 15-minute window
`verified_code`	`int4`	nullable	—	4- or 6-digit numeric OTP sent by email
`is_verified`	`bool`	nullable	`false`	Flipped to `true` once the code is consumed

ats_scores

Stores AI-generated ATS (Applicant Tracking System) scoring results for a user’s resume. The feedback column holds structured JSON returned by the LLM.

create TABLE if not exists ats_scores (
    uid        uuid        not null default gen_random_uuid(),
    user_id    uuid,
    created_at timestamptz default CURRENT_TIMESTAMP,
    score      int4,
    feedback   jsonb,
    constraint ats_scores_user_id_fkey
        foreign key (user_id) references users(uid) on delete cascade,
    primary key (uid)
);

Column details — ats_scores

Column	Type	Nullable	Default	Notes
`uid`	`uuid`	NOT NULL	`gen_random_uuid()`	Primary key
`user_id`	`uuid`	nullable	—	FK → `users.uid`; cascades on delete
`created_at`	`timestamptz`	nullable	`CURRENT_TIMESTAMP`	When the analysis was run
`score`	`int4`	nullable	—	Numeric ATS score (0–100)
`feedback`	`jsonb`	nullable	—	Structured LLM feedback: strengths, gaps, suggestions

user_educations

Stores a user’s detailed education history. A user may have multiple rows (one per degree or institution).

create table if not exists user_educations (
    uid             uuid primary key default gen_random_uuid(),
    user_id         uuid not null references users(uid) on delete cascade,
    university_name text not null,
    degree          text not null,
    start_date      date,
    end_date        date,
    grade           text
);

Column details — user_educations

Column	Type	Nullable	Default	Notes
`uid`	`uuid`	NOT NULL	`gen_random_uuid()`	Primary key
`user_id`	`uuid`	NOT NULL	—	FK → `users.uid`; cascades on delete
`university_name`	`text`	NOT NULL	—	Name of the institution
`degree`	`text`	NOT NULL	—	Degree title or qualification name
`start_date`	`date`	nullable	—	Enrolment start date
`end_date`	`date`	nullable	—	Graduation or completion date
`grade`	`text`	nullable	—	Final grade, GPA, or CGPA

notifications

Stores all in-app notifications for users. Rows can be linked to a company, a job, or neither depending on the notification type. The read_at column is NULL until the user opens the notification.

create table notifications if not exists (
    uuid       uuid primary key,
    users_id   uuid references users(uid) on delete cascade,
    company_id uuid references companies(uid) on delete cascade,
    job_id     uuid references jobs(uid),
    type       notifications_type,
    created_at timestamp default current_timestamp,
    read_at    timestamp
);

Column details — notifications

Column	Type	Nullable	Default	Notes
`uuid`	`uuid`	NOT NULL	—	Primary key
`users_id`	`uuid`	nullable	—	FK → `users.uid`; cascades on delete; the notification recipient
`company_id`	`uuid`	nullable	—	FK → `companies.uid`; cascades on delete; set when notification is company-related
`job_id`	`uuid`	nullable	—	FK → `jobs.uid`; set when notification is job-related
`type`	`notifications_type`	nullable	—	Category enum
`created_at`	`timestamp`	nullable	`current_timestamp`	When the notification was generated
`read_at`	`timestamp`	nullable	`NULL`	Populated when the user reads the notification

Indexes

All indexes are created idempotently with IF NOT EXISTS. They are split across four concern areas.

Jobs indexes

-- GIN index for full-text search on the tsvector column
create index if not exists index_jobs_search_title
    on jobs using gin (search_title);

-- FK lookup indexes
create index if not exists idx_jobs_company_id on jobs(company_id);
create index if not exists idx_jobs_created_by on jobs(created_by);

-- Filter index for open/closed queries
create index if not exists idx_jobs_is_open on jobs(is_job_open);

-- Composite index: company dashboard open-jobs query
create index if not exists idx_jobs_company_open on jobs(company_id, is_job_open);

Index	Type	Purpose
`index_jobs_search_title`	GIN	Full-text search on job titles
`idx_jobs_company_id`	btree	Company → jobs foreign key lookup
`idx_jobs_created_by`	btree	Recruiter → posted jobs lookup
`idx_jobs_is_open`	btree	Filter active/closed listings
`idx_jobs_company_open`	btree (composite)	Company dashboard active-job count

Users and companies indexes

-- Unique email constraint (also enforces uniqueness)
create unique index if not exists users_email_key
    on users using btree(email);

-- Fast email lookup for login
create index if not exists idx_users_email on users using btree (email);

-- Company name search
create index if not exists idx_companies_name on companies using btree (name);

-- Prevents duplicate follows (one row per user+company pair)
create unique index if not exists unique_user_companies
    on user_companies_follows using btree (user_id, company_id);

Applications and saved_jobs indexes

create index if not exists idx_applications_user_id on applications(user_id);
create index if not exists idx_applications_job_id  on applications(job_id);
create index if not exists idx_applications_status  on applications(status);

create index if not exists idx_saved_jobs_user_id on saved_jobs(user_id);
create index if not exists idx_saved_jobs_job_id  on saved_jobs(job_id);

Supporting table indexes

-- Email verification lookups
create index if not exists idx_email_verified_code_user_id
    on email_verified using btree(user_id);
create index if not exists idx_verified_code_user_id
    on email_verified(verified_code);

-- Education and ATS score lookups
create index if not exists idx_user_educations_user_id on user_educations(user_id);
create index if not exists idx_ats_scores_user_id      on ats_scores(user_id);

-- Notifications: ordered by recency and read status
create index idx_notifications_user_created
    on notifications (users_id, created_at desc);
create index idx_notifications_user_read
    on notifications (users_id, read_at);

Trigger — `vector_search_update`

The search_title column on the jobs table is a tsvector that must stay in sync with the title column. Rather than requiring the application layer to manage this, a BEFORE INSERT OR UPDATE trigger handles it automatically.

create or replace function public.update_search_job_title()
  returns trigger
  language plpgsql
as $function$
  begin
    new.search_title =
      to_tsvector('english', new.title);
    return new;
  end
$function$;

do $$ begin
  if not exists (
    select 1 from pg_trigger where tgname = 'vector_search_update'
  ) then
    create trigger vector_search_update
      before insert or update on public.jobs
      for each row
      execute function update_search_job_title();
  end if;
end $$;

The trigger fires on both INSERT and UPDATE, so renaming a job title automatically refreshes the search index without any extra application code.

Entity Relationships

The diagram below shows every table and their foreign key relationships.

Foreign keys that reference companies(uid) with ON DELETE CASCADE include users, user_companies_follows, and notifications. Deleting a company row therefore cascades through the entire ownership graph — jobs, applications, follows, and notifications are all removed automatically.

Get Started

Core Features

Configuration & Deployment

Database

Security

Yeti Jobs Database Schema: Tables and Relationships

Extensions

Enums

Tables

companies

users

jobs

applications

saved_jobs

user_companies_follows

email_verified

ats_scores

user_educations

notifications

Indexes

Trigger — `vector_search_update`

Entity Relationships

Build docs developers (and LLMs) love

Get Started

Core Features

Configuration & Deployment

Database

Security

Documentation Index

​Extensions

​Enums

​Tables

​companies

​users

​jobs

​applications

​saved_jobs

​user_companies_follows

​email_verified

​ats_scores

​user_educations

​notifications

​Indexes

​Trigger — vector_search_update

​Entity Relationships

Build docs developers (and LLMs) love

Extensions

Enums

Tables

companies

users

jobs

applications

saved_jobs

user_companies_follows

email_verified

ats_scores

user_educations

notifications

Indexes

Trigger — `vector_search_update`

Entity Relationships