APP

Documentation

Developer Guide

App Documentation

Database Schema

The application uses Cloudflare D1, a SQLite-compatible database that runs at the edge. The schema is designed to support all the core features of the platform while maintaining performance and scalability.

Database Overview

The AutoTechJobs platform uses Cloudflare D1 (SQLite-compatible) as its database system. The schema is designed to support all core features while maintaining performance and scalability.

Core Tables

Users & Authentication

users - Core authentication and user information
roles - User roles and permissions

Profiles

jobseeker_profiles - Candidate profiles
employer_profiles - Company profiles
associate_profiles - Associate/referral profiles
staff_profiles - Staff member profiles

Job & Application Tables

Job Listings

jobs - Job postings
job_specializations - Job specialization relationships
job_certifications - Required certifications

Applications & Matches

job_applications - Application submissions
job_matches - Candidate-job matches
job_alerts - Job alert subscriptions

Communication & Engagement

Messaging

conversations - Message threads
messages - Individual messages
emails - Email communications

Notifications & Alerts

notifications - System notifications
job_alerts - Job alert subscriptions
job_matches - Match notifications

Reference Tables

Core References

locations - User locations
certifications - Professional certifications
specializations - Job specializations
job_titles - Job title catalog
shop_types - Shop type categories

Data Integrity & Security

All tables have proper foreign key constraints
Timestamps use SQLite datetime functions
Booleans are stored as INTEGER (0/1)
Indexes for performance optimization
Soft delete pattern implemented
Proper audit columns (created_at, updated_at, deleted_at)

Schema Location

The complete schema is located in db/schema.sql and is automatically applied during deployment.

-- View the complete schema in:
db/schema.sql

For more information about database management and migrations, see theSchema Documentationdocumentation.

User Management

Table	Description	Key Fields
`users`	Core user accounts with authentication credentials	`id, email, password_hash, role_id, created_at`
`roles`	User role definitions	`id, name, permissions`
`jobseeker_profiles`	Extended information for job seekers	`user_id, resume_url, skills, experience_years`
`employer_profiles`	Extended information for employers	`user_id, company_id, position, department`

Job Management

Table	Description	Key Fields
`jobs`	Core job listings with details and requirements	`id, title, company_id, description, requirements, salary_range, location, job_type, status`
`companies`	Employer company information	`id, name, description, logo_url, website, industry`
`job_titles`	Standardized job title definitions	`id, title, category, description`
`specializations`	Automotive specializations	`id, name, description, parent_id`

Application Process

Table	Description	Key Fields
`candidate_applications`	Job applications from candidates	`id, job_id, user_id, status, cover_letter, resume_version, applied_at`
`job_matches`	Algorithmic matches between jobs and candidates	`job_id, user_id, match_score, matched_at`
`interviews`	Scheduled interviews for job applications	`id, application_id, scheduled_at, interview_type, notes`

Key SQL Schema

Here's a sample of the core tables from the schema.sql file:

-- Users and Authentication
CREATE TABLE roles (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  permissions TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  role_id INTEGER NOT NULL,
  first_name TEXT,
  last_name TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- Job Management
CREATE TABLE companies (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  logo_url TEXT,
  website TEXT,
  industry TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE jobs (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  company_id INTEGER NOT NULL,
  description TEXT NOT NULL,
  requirements TEXT,
  salary_range TEXT,
  location TEXT,
  job_type TEXT,
  status TEXT DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP,
  FOREIGN KEY (company_id) REFERENCES companies(id)
);

-- Application Process
CREATE TABLE candidate_applications (
  id INTEGER PRIMARY KEY,
  job_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  status TEXT DEFAULT 'submitted',
  cover_letter TEXT,
  resume_version INTEGER,
  applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (job_id) REFERENCES jobs(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Database Access Pattern

The application accesses the database through repository classes that provide a clean API for data operations:

// Example repository method for finding jobs
async findJobsWithFilters(filters: JobFilters): Promise<Job[]> {
  let query = 'SELECT * FROM jobs WHERE status = "active"';
  const params = [];
  
  if (filters.location) {
    query += ' AND location LIKE ?';
    params.push('%' + filters.location + '%');
  }
  
  if (filters.jobType) {
    query += ' AND job_type = ?';
    params.push(filters.jobType);
  }
  
  // Add more filters as needed
  
  query += ' ORDER BY created_at DESC LIMIT ? OFFSET ?';
  params.push(filters.limit || 20, filters.offset || 0);
  
  const result = await this.db.prepare(query).bind(...params).all();
  return result.results as Job[];
}