Database Schema
Relevant source files
The following files were used as context for generating this wiki page:
The database schema for the TAMV (Transporte de Alimentos y Mercancía Varia) project is designed to manage user profiles, vehicle fleets, maintenance scheduling, and regulatory documentation. The system utilizes a relational structure to ensure data integrity between users and their assigned assets, providing a centralized repository for operational tracking.
The schema is implemented using PostgreSQL and is managed via Supabase migrations. It focuses on four primary entities: Profiles, Vehicles, Maintenance Logs, and Documents, which together form the core data infrastructure for the application.
Sources: README.md:1-2
Core Entities and Relationships
The database is structured around the profiles table, which serves as the central authority for user identity, linking to the internal authentication system. Every other entity in the system maintains a relationship with either a user or a specific vehicle.
Entity Relationship Diagram
The following diagram illustrates the structural relationships between the primary tables, highlighting the foreign key constraints and cardinality.
Data Models
Profiles
The profiles table extends the basic authentication data to include application-specific user details. It is synchronized with the auth provider's unique identifiers.
| Field | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | Primary Key, References auth.users | Unique identifier linked to the auth system |
| full_name | text | Not Null | User's full name |
| text | Unique, Not Null | Primary contact email | |
| updated_at | timestamp | Default: now() | Last modification timestamp |
Sources: supabase/migrations/20240322000000_create_profiles.sql:1-15
Vehicles
The vehicles table stores the primary assets managed by the system. Each vehicle is tied to a specific profile who acts as the owner or primary operator.
| Field | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | Primary Key | Unique vehicle identifier |
| owner_id | uuid | Foreign Key (profiles.id) | Link to the user who owns the vehicle |
| plate_number | text | Unique, Not Null | Official license plate identification |
| model | text | Not Null | Vehicle manufacturer and model |
| year | integer | Not Null | Manufacturing year |
| created_at | timestamp | Default: now() | Record creation date |
Sources: supabase/migrations/20240322000001_create_vehicles.sql:1-20
Maintenance Logs
This table tracks all technical interventions and services performed on vehicles to maintain operational history and safety standards.
| Field | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | Primary Key | Unique log entry ID |
| vehicle_id | uuid | Foreign Key (vehicles.id) | Associated vehicle |
| service_date | date | Not Null | Date the maintenance was performed |
| description | text | Not Null | Details of work done |
| cost | numeric | Default: 0 | Financial cost of the service |
Sources: supabase/migrations/20240322000002_create_maintenance_logs.sql:1-18
Documents
The documents table manages digital copies of legal requirements such as insurance, technical inspections, and permits associated with each vehicle.
| Field | Type | Constraints | Description |
|---|---|---|---|
| id | uuid | Primary Key | Unique document identifier |
| vehicle_id | uuid | Foreign Key (vehicles.id) | Associated vehicle |
| document_type | text | Not Null | Type (e.g., Insurance, SOAT) |
| expiry_date | date | Not Null | Document expiration date |
| file_path | text | Not Null | Storage path for the file |
Sources: supabase/migrations/20240322000003_create_documents.sql:1-18
Row Level Security (RLS)
The system implements Row Level Security to ensure that users can only access data relevant to them. Policies are applied across all tables to enforce data isolation between different profiles.
- Profiles Policy: Users can only view and edit their own profile entry where
id = auth.uid(). - Vehicles Policy: Users can only access vehicle records where
owner_id = auth.uid(). - Logs/Documents Policy: Access is granted if the user owns the vehicle linked via the foreign key relationship.
Sources: supabase/migrations/20240322000000_create_profiles.sql:20-25, supabase/migrations/20240322000001_create_vehicles.sql:25-30
Summary
The TAMV database schema provides a robust foundation for asset management by linking users to vehicles and their subsequent operational data (maintenance and documents). The use of UUIDs, strong foreign key constraints, and RLS policies ensures that the multi-tenant nature of the application remains secure and scalable.