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
AuthContextprovider. - Protected Routes: The
ProtectedRoutecomponent 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 thesupabase.auth.updateUsermethod.