Data Access Layer
Relevant source files
The following files were used as context for generating this wiki page:
The Data Access Layer (DAL) in Vinesight is responsible for all interactions with the backend database. It is built upon Supabase, which uses a PostgreSQL database, and provides a secure, type-safe, and centralized mechanism for creating, reading, updating, and deleting farm-related data. The architecture leverages the supabase-js client library, with a dedicated service class abstracting the database operations.
A core principle of the DAL is security, enforced through Supabase's Row Level Security (RLS) to ensure users can only access their own data. Type safety is achieved by using TypeScript interfaces generated from the database schema, which minimizes runtime errors and improves developer experience. The primary entry point for application components to interact with the database is through service classes like SupabaseService and CloudDataService, which encapsulate the data-fetching logic.
Sources: AGENTS.md, context/CLAUDE.md, src/lib/export-service.ts
Architecture Overview
The DAL is structured to decouple the application's business logic from the database implementation details. The flow of data begins at the UI layer, which calls a service (e.g., ExportService), which in turn uses a data service (SupabaseService or CloudDataService) to perform the actual database operations via the Supabase client.
This diagram illustrates the high-level architecture of the Data Access Layer.
Database Schema and Security
The database schema is designed to support core farming operations as well as advanced AI-driven features. It includes tables for managing farms, user profiles, and various operational records. For Phase 3, new tables were introduced to handle AI-powered recommendations, predictions, and personalization.
Sources: context/CLAUDE.md, context/TYPES_AND_INTERFACES.md
Entity Relationship Diagram
The following diagram shows the relationships between key entities in the database, focusing on the user, farm, and new AI-related tables.
This diagram illustrates that a user can have multiple farms, and each farm can have associated AI profiles, task recommendations, and pest predictions.
Sources: context/CLAUDE.md:8-78
Row Level Security (RLS)
A critical aspect of the DAL is the enforcement of data privacy and isolation using Supabase's Row Level Security (RLS). RLS policies are defined directly on the PostgreSQL tables to ensure that users can only access data they own. This security is enforced at the database level, providing a robust safeguard against unauthorized data access.
The following table summarizes the RLS policies for the new Phase 3 tables.
| Table Name | Policy Description | SQL Condition |
|---|---|---|
farmer_ai_profiles | Users can manage their own AI profiles. | auth.uid() = user_id |
ai_task_recommendations | Users can see recommendations for their own farms. | auth.uid() = user_id OR EXISTS (...) |
pest_disease_predictions | Users can see predictions for their own farms. | EXISTS (SELECT 1 FROM farms WHERE ...) |
profitability_analyses | Users can access their own profitability data. | auth.uid() = user_id |
market_intelligence | All authenticated users can access market data. | auth.role() = 'authenticated' |
community_insights | All authenticated users can access community insights. | auth.role() = 'authenticated' |
ai_conversation_context | Users can access context from their own conversations. | EXISTS (SELECT 1 FROM ai_conversations ...) |
Sources: context/CLAUDE.md:81-120
Core Components
Data Models and Type Safety
The DAL heavily relies on TypeScript to ensure type safety. The project defines interfaces for both the application layer (using camelCase) and the database layer (using snake_case). This helps catch potential mismatches between the application code and the database schema during development.
For example, the Farm entity has distinct interfaces for the application and database layers.
// Core Farm interface (Application Layer)
export interface Farm {
id?: number;
name: string;
region: string;
area: number;
grapeVariety: string;
// ... other camelCase properties
userId?: string;
}
// Database Farm interface (Supabase Layer)
export interface DatabaseFarm {
id?: number;
user_id: string;
name: string;
region: string;
area: number;
grape_variety: string;
// ... other snake_case properties
}
This distinction is crucial for mapping data correctly as it moves between the database and the application.
Sources: context/TYPES_AND_INTERFACES.md:5-40
Data Services (CloudDataService / SupabaseService)
Data access is abstracted through service classes like CloudDataService and SupabaseService. These services provide a clean API for the rest of the application to interact with the database, hiding the underlying supabase-js calls. This centralizes data logic, making it easier to manage, debug, and modify.
The ExportService, for instance, relies on this service to fetch all necessary data for generating reports.
Key methods exposed by the data service include:
getAllFarms()getIrrigationRecords(farmId)getSprayRecords(farmId)getHarvestRecords(farmId)getExpenseRecords(farmId)getFarmById(farmId)
Sources: src/app/reports/page.tsx:75, src/lib/export-service.ts:90-125
Data Flow Example: Exporting a Report
The process of exporting a report provides a clear example of the DAL in action. The ExportService orchestrates the fetching of various data types, filters them, and then formats them into a PDF or CSV file.
This sequence diagram shows the interactions when ExportService.exportData() is called.
This flow demonstrates how the service layer uses the DAL to aggregate data from multiple tables before processing it for the user.
Sources: src/lib/export-service.ts:46-60, 65-135
Conclusion
The Data Access Layer in Vinesight is a well-defined and secure system for managing database interactions. By leveraging Supabase, PostgreSQL, and TypeScript, it provides a robust foundation for the application's features. The use of a centralized service class (SupabaseService) simplifies data logic, while Row Level Security ensures that user data remains private and isolated. This architecture is scalable and capable of supporting the complex data needs of both current features and future AI-driven enhancements.
Sources: AGENTS.md, context/CLAUDE.md