Database Schema & Supabase

Database Architecture Overview

TEIRAC leverages Supabase (PostgreSQL) as its primary database and authentication provider. The system is designed around a relational hierarchy where a central Project entity serves as the anchor for multiple specialized data modules.

All data access is governed by Supabase Row Level Security (RLS), ensuring users only interact with project data they are authorized to view or manage.

Core Schema

Profiles Table

The profiles table extends the default Supabase auth.users table. It stores application-specific metadata for users.

| Field | Type | Description | | :--- | :--- | :--- | | id | uuid | Primary Key (links to auth.users.id) | | full_name | text | User's legal name | | display_name | text | Name shown in the UI | | role | text | Professional role (e.g., Project Manager) | | department | text | Organizational unit (Engineering, Finance, etc.) | | site_location | text | Physical site assignment | | timezone | text | Preferred timezone for reporting |

Projects Table

The projects table is the central hub for all project intelligence. It stores high-level performance metrics and metadata used to calculate dashboard KPIs.

| Field | Type | Description | | :--- | :--- | :--- | | id | uuid | Primary Key | | name | text | Project title | | spi | numeric | Schedule Performance Index (1.0 = on track) | | budget_total | numeric | Allocated budget | | budget_used | numeric | Current expenditure | | timeline_pct | integer | Percentage of timeline elapsed | | status | text | Project health (on-track, at-risk, delayed, completed) | | site_zone | text | Geographical or site-specific zone |

Modular Data Structure

TEIRAC uses a "Module" pattern where specific project details (Tasks, Risks, Budgets) are stored in dedicated tables. Each module table includes project_id and user_id foreign keys to maintain relational integrity.

Data Modules & Tables

The following tables store the granular data displayed in the Project Detail views:

| Module Name | Database Table | Key Attributes | | :--- | :--- | :--- | | Task List | project_tasks | wbs_code, task, owner, status | | Budget Items | project_budget_items | category, budget_amount, spent_amount | | Risk Tracking | project_risks | risk_level, description | | Resource Plan | project_resource_plan | role, allocation_pct, availability | | Issues | project_issues | issue_id_label, priority, raised_date | | RACI Matrix | project_raci | responsible, accountable, consulted, informed |

Data Integration & CSV Mapping

The platform supports bulk data entry via CSV imports. The ImportModal component maps CSV headers to specific database columns. When importing data, the system automatically injects the active project_id and the current user_id into every row.

Usage Example: Querying Project Data

Developers can interact with the schema using the Supabase client:

import { supabase } from '../lib/supabaseClient'

/**
 * Fetch all tasks for a specific project
 */
async function getProjectTasks(projectId: string) {
  const { data, error } = await supabase
    .from('project_tasks')
    .select('*')
    .eq('project_id', projectId)
    .order('created_at', { ascending: true })

  if (error) throw error
  return data
}

Authentication Integration

Authentication is handled via Supabase Auth.

• Session Management: Handled via the AuthContext provider.
• Protected Routes: The ProtectedRoute component intercepts navigation to dashboard and project pages, ensuring a valid session exists before granting access to database resources.
• Password Recovery: Users can trigger password reset flows via the ResetPasswordPage, which utilizes the supabase.auth.updateUser method.