Trackr

Database Architecture

Comprehensive overview of the Trackr database architecture, schema, indexes, relationships, and Row Level Security policies.

Database Architecture

The Trackr application relies on a highly optimized, robust PostgreSQL database to manage users, job applications, companies, interviews, and real-time analytics. This document provides a complete guide to our database systems, including schema definitions, indexes, relationships, Row Level Security (RLS) policies, and our migration strategy. Our database is designed to handle millions of job applications concurrently, providing low-latency queries and strict data isolation across tenants.

Overview

We use PostgreSQL 16+ as our primary relational database. It is managed via Prisma ORM for type-safe schema definitions and query building in our application layer, while utilizing native PostgreSQL features for advanced functionality like Full Text Search and JSONB document storage.

Our database architecture follows a multi-tenant design where all tenants (users/organizations) share the same database and schema, but their data is strictly isolated using PostgreSQL Row Level Security (RLS). This approach allows for efficient connection pooling, simplified schema migrations, and optimized resource utilization.

Core Schema & Tables

The Trackr database is normalized to the Third Normal Form (3NF) to reduce redundancy, with specific denormalizations introduced only where required for critical read-path performance.

users

The central table for authentication, authorization, and profile management.

  • id (UUID, Primary Key): Unique identifier for the user.
  • email (VARCHAR(255), Unique): The user's email address.
  • password_hash (VARCHAR(255)): Argon2 hashed password.
  • first_name (VARCHAR(100)): User's first name.
  • last_name (VARCHAR(100)): User's last name.
  • role (ENUM('user', 'admin', 'moderator')): Role-based access control. Default is 'user'.
  • created_at (TIMESTAMPTZ): Timestamp of account creation.
  • updated_at (TIMESTAMPTZ): Timestamp of the last update.
  • settings (JSONB): User-specific preferences (e.g., dark mode, notification settings).

Relationships:

  • 1:N with job_applications
  • 1:N with resumes
  • 1:N with user_sessions

companies

Stores information about the companies that users are applying to. This is a global table, but users can also create custom company entries that are private to them.

  • id (UUID, Primary Key)
  • name (VARCHAR(255)): Name of the company.
  • domain (VARCHAR(255)): Website domain, used for logo fetching and deduplication.
  • industry (VARCHAR(100)): E.g., 'Software', 'Finance'.
  • created_by (UUID, Foreign Key): Links to users.id if the company is user-created. NULL if global.
  • created_at (TIMESTAMPTZ)

Indexes:

  • idx_companies_name on name (B-Tree) for autocomplete lookups.
  • idx_companies_domain on domain (B-Tree) for uniqueness checks.

job_applications

The core transactional table storing the lifecycle of a job application.

  • id (UUID, Primary Key)
  • user_id (UUID, Foreign Key): The owner of the application.
  • company_id (UUID, Foreign Key): The company applied to.
  • role_title (VARCHAR(255)): The title of the job.
  • status (ENUM('bookmarked', 'applied', 'interviewing', 'offered', 'rejected', 'withdrawn')): Current state.
  • applied_date (DATE): When the user applied.
  • salary_range (INT4RANGE): PostgreSQL range type for expected salary.
  • location (VARCHAR(255)): Job location.
  • is_remote (BOOLEAN): Whether the role is remote.
  • notes (TEXT): Free-form markdown notes.
  • created_at (TIMESTAMPTZ)
  • updated_at (TIMESTAMPTZ)

Relationships:

  • N:1 with users
  • N:1 with companies
  • 1:N with interviews
  • 1:N with application_events

interviews

Tracks individual interview rounds associated with a job application.

  • id (UUID, Primary Key)
  • application_id (UUID, Foreign Key): Link to job_applications.id.
  • round_name (VARCHAR(100)): E.g., 'Phone Screen', 'Onsite', 'Technical'.
  • scheduled_at (TIMESTAMPTZ): When the interview takes place.
  • duration_minutes (INTEGER): Expected length.
  • format (ENUM('video', 'phone', 'in_person'))
  • feedback (TEXT): Notes post-interview.

resumes

Stores references to uploaded resumes (stored in object storage like AWS S3).

  • id (UUID, Primary Key)
  • user_id (UUID, Foreign Key)
  • file_name (VARCHAR(255))
  • s3_key (VARCHAR(512)): The path in the S3 bucket.
  • parsed_data (JSONB): Extracted text and entities using our AI parser.

Indexing Strategy

To ensure sub-millisecond response times for common queries, we employ a rigorous indexing strategy.

  1. Primary and Foreign Keys: All primary keys (id) and foreign keys automatically receive B-Tree indexes. This ensures fast JOINs and lookups.
  2. Status and Dates: The job_applications table has a composite index on (user_id, status, applied_date) to optimize the main dashboard view, which typically filters by status and sorts by date. 
    CREATE INDEX idx_job_apps_dashboard ON job_applications (user_id, status, applied_date DESC);
  3. JSONB Indexing: We use GIN (Generalized Inverted Index) on the resumes.parsed_data and users.settings columns to allow fast querying of nested JSON attributes. 
    CREATE INDEX idx_resumes_parsed_data ON resumes USING GIN (parsed_data);
  4. Full Text Search: The notes column in job_applications and feedback in interviews are indexed using PostgreSQL's tsvector for fast keyword searches. 
    CREATE INDEX idx_job_apps_notes_search ON job_applications USING GIN (to_tsvector('english', notes));

Row Level Security (RLS)

Trackr strictly enforces multi-tenancy at the database level using PostgreSQL Row Level Security. This ensures that a bug in the application layer cannot accidentally expose one user's data to another.

RLS is enabled on all tables that contain user-specific data.

Example: Job Applications RLS Policy

First, we enable RLS on the table:

ALTER TABLE job_applications ENABLE ROW LEVEL SECURITY;

We then create policies for SELECT, INSERT, UPDATE, and DELETE:

-- Allow users to read their own applications
CREATE POLICY select_own_applications ON job_applications
FOR SELECT
USING (auth.uid() = user_id);
 
-- Allow users to insert applications for themselves
CREATE POLICY insert_own_applications ON job_applications
FOR INSERT
WITH CHECK (auth.uid() = user_id);
 
-- Allow users to update their own applications
CREATE POLICY update_own_applications ON job_applications
FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
 
-- Allow users to delete their own applications
CREATE POLICY delete_own_applications ON job_applications
FOR DELETE
USING (auth.uid() = user_id);

Note: The auth.uid() function is a custom PostgreSQL function that extracts the authenticated user's ID from a securely set session variable configured by our middleware before every transaction.

Migrations Strategy

Database schemas evolve. At Trackr, we treat database migrations as first-class citizens in our deployment pipeline. We use Prisma Migrate combined with custom raw SQL scripts for advanced PostgreSQL features.

Workflow

  1. Local Development: Developers modify the schema.prisma file. Running prisma migrate dev generates a new SQL migration file.
  2. Review: The generated .sql file is committed to version control and reviewed. DBAs check for locking operations, index creation overhead, and backward compatibility.
  3. Zero-Downtime Migrations: All migrations must be non-blocking.
    • Instead of renaming columns directly, we add the new column, dual-write to both, backfill the new column, and finally drop the old column in a subsequent release.
    • We create indexes CONCURRENTLY to prevent table locking during deployment. Note that Prisma doesn't support concurrent index creation natively, so these must be written in raw SQL.

Example Migration: Adding a column concurrently

-- Step 1: Add column (fast operation, minimal lock)
ALTER TABLE job_applications ADD COLUMN expected_equity DECIMAL(5,2);
 
-- Step 2: Create index concurrently (does not block writes)
-- Note: This cannot run inside a transaction block
CREATE INDEX CONCURRENTLY idx_job_apps_equity ON job_applications (expected_equity);

Data Retention and Archiving

To maintain high performance as the database grows, we implement partition-based archiving.

The application_events table (which logs every state change and interaction for analytics) grows rapidly. We use PostgreSQL native declarative partitioning to partition this table by month. Data older than 24 months is moved to cold storage (AWS S3) and dropped from the active PostgreSQL cluster, keeping index sizes manageable.

High Availability and Backups

  • Replication: We run a multi-AZ setup with one primary writer and two read replicas. Read-heavy analytics dashboards are routed to the read replicas to offload the primary database.
  • Backups:
    • Automated continuous archiving (WAL shipping) to S3 allows Point-in-Time-Recovery (PITR) with up to 1-second granularity for the past 30 days.
    • Daily full snapshots.
  • Connection Pooling: We use PgBouncer configured in transaction mode sitting between the application servers and the database to handle connection spikes and reduce connection overhead.

By adhering to these architectural patterns, the Trackr database remains a secure, scalable, and highly performant foundation for the entire application ecosystem.