Database Schema & Types

This section provides a technical overview of the data architecture for the Japanese ATS. The system utilizes Supabase (PostgreSQL) for data storage and provides full TypeScript definitions for the database schema to ensure type safety across the frontend and Edge Functions.

Database Schema

The core of the application resides in the applicants table, which stores candidate information, resume metadata, and automated screening results.

The applicants Table

This table tracks the lifecycle of an application, from the initial submission to the automated keyword matching process.

| Column | Type | Description | | :--- | :--- | :--- | | id | uuid | Primary key (unique identifier). | | full_name | text | The legal name of the applicant. | | email | text | Contact email address for notifications. | | date_of_birth | date | Applicant's date of birth. | | highest_degree | text | Education level (e.g., Bachelor's, Master's). | | years_of_experience| number | Total years of relevant professional experience. | | preferred_course | text | The specific JLPT or Japanese course applied for. | | cv_file_path | text | The path to the PDF file in Supabase Storage (cvs bucket). | | cv_extracted_text | text | Raw text extracted from the PDF during background parsing. | | keyword_match_score| number | Calculated score (0-100) based on keyword matching. | | matched_keywords | text[] | Array of specific keywords found within the resume. | | status | enum | Current workflow state (see Enums below). | | comments | text | Optional internal notes provided by the applicant or admin. | | created_at | timestamp| Record creation timestamp. | | updated_at | timestamp| Last modification timestamp. |

Enumerated Types

application_status

The system uses a custom PostgreSQL enum to manage the application workflow:

• pending: The default state upon submission.
• reviewed: The admin has opened and inspected the application.
• accepted: The applicant has been approved for the course.
• rejected: The application has been declined.

TypeScript Definitions

The project uses auto-generated types from the Supabase CLI, located at src/integrations/supabase/types.ts. This allows for strict typing when performing CRUD operations.

Usage Example

To use the applicant type within a React component or service:

import { Database } from "@/integrations/supabase/types";

// Extract the Row type for the applicants table
export type Applicant = Database["public"]["Tables"]["applicants"]["Row"];

// Example: Fetching a typed applicant
const fetchApplicant = async (id: string): Promise<Applicant | null> => {
  const { data, error } = await supabase
    .from("applicants")
    .select("*")
    .eq("id", id)
    .single();

  if (error) throw error;
  return data;
};

Insert and Update Types

Supabase provides specific types for inserting (where id might be optional) and updating (where all fields are optional).

import { TablesInsert, TablesUpdate } from "@/integrations/supabase/types";

// Type for creating a new application
const newApplication: TablesInsert<"applicants"> = {
  full_name: "John Doe",
  email: "john@example.com",
  cv_file_path: "path/to/resume.pdf",
  // status defaults to 'pending' in DB
};

Storage Schema

Resumes are stored as binary objects in a dedicated Supabase Storage bucket.

• Bucket Name: cvs
• File Format: Restricted to application/pdf.
• Naming Convention: Files are typically prefixed with a timestamp (e.g., 1715634000-resume.pdf) to prevent naming collisions.
• Access: Managed via RLS (Row Level Security) policies, ensuring only authenticated administrators can download files via the cv_file_path reference.

Internal Processing Fields

The following fields are populated asynchronously by the parse-cv Edge Function after a user submits their form:

• cv_extracted_text
• keyword_match_score
• matched_keywords

These fields should be treated as read-only in the frontend ApplicantForm and are only modified by the admin or the automated background worker.