Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• supabase-schema.sql
• supabase/migrations/20250903_phase3_ai_schema.sql
• src/lib/supabase-types.ts
• context/TYPES_AND_INTERFACES.md
• CLAUDE.md
• supabase/migrations/20250903184110_phase3_ai_schema.sql

The VineSight database is built on Supabase, utilizing PostgreSQL as its core. It is designed to store all data related to farm management, user operations, and advanced AI-driven features. The schema is divided into two main parts: a core schema for fundamental record-keeping (Phase 1 & 2) and an advanced AI schema for personalization, predictions, and community intelligence (Phase 3).

A key architectural feature is the extensive use of Row Level Security (RLS) to ensure that users can only access and manipulate data related to their own farms. This provides a secure, multi-tenant data architecture. The SQL schema is tightly coupled with the application's frontend through auto-generated TypeScript types, ensuring type safety across the stack.

Core Schema (Phases 1 & 2)

The core schema establishes the foundational tables for managing farms and recording essential agricultural activities. It centers around the farms table, which is linked to a user, and various record tables that store operational data.

Sources: supabase-schema.sql

Entity Relationship Diagram (Core Schema)

This diagram illustrates the relationships between the main entities in the core database schema.

Key Tables

farms

This is the central table, representing a user's vineyard. All other operational records are linked to a farm.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Unique identifier for the farm.
name	VARCHAR(255)	NOT NULL	Name of the farm.
region	VARCHAR(255)	NOT NULL	Geographical region of the farm.
area	DECIMAL(10,2)	NOT NULL	Farm area in hectares.
grape_variety	VARCHAR(255)	NOT NULL	The variety of grape grown.
planting_date	DATE	NOT NULL	The date the vines were planted.
user_id	UUID	REFERENCES auth.users(id)	Foreign key linking to the owner in auth.users.
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Timestamp of creation.
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Timestamp of the last update.

Sources: supabase-schema.sql:8-20, context/TYPES_AND_INTERFACES.md:7-21

irrigation_records

Stores data for each irrigation event.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Unique identifier for the record.
farm_id	BIGINT	REFERENCES farms(id)	Links to the associated farm.
date	DATE	NOT NULL	Date of the irrigation.
duration	DECIMAL(5,2)	NOT NULL	Duration of irrigation in hours.
growth_stage	VARCHAR(100)	NOT NULL	Crop growth stage during irrigation.
system_discharge	DECIMAL(8,2)	NOT NULL	System discharge in liters per hour.

Sources: supabase-schema.sql:23-33, src/lib/supabase-types.ts:16-27

expense_records

Tracks all farm-related expenses.

Column	Type	Constraints	Description
id	BIGSERIAL	PRIMARY KEY	Unique identifier for the expense.
farm_id	BIGINT	REFERENCES farms(id)	Links to the associated farm.
date	DATE	NOT NULL	Date of the expense.
type	VARCHAR(20)	CHECK (...)	Category of expense (labor, materials, etc.).
description	TEXT	NOT NULL	Detailed description of the expense.
cost	DECIMAL(12,2)	NOT NULL	The cost of the expense.

Sources: supabase-schema.sql:68-77, src/lib/supabase-types.ts:68-77

Other core tables include spray_records, fertigation_records, harvest_records, task_reminders, soil_test_records, petiole_test_records, and calculation_history which follow a similar structure, linking back to a farm_id.

Sources: supabase-schema.sql:36-105

Advanced AI Schema (Phase 3)

The Phase 3 schema introduces a set of tables designed to support advanced AI functionalities, including farmer personalization, predictive analytics, and community-driven insights. These tables build upon the core schema to enable a proactive and intelligent user experience.

Sources: supabase/migrations/20250903_phase3_ai_schema.sql, CLAUDE.md

Entity Relationship Diagram (AI Schema)

This diagram shows the new tables introduced in Phase 3 and their relationships with the core farms and auth.users tables.

Key AI Tables

farmer_ai_profiles

Stores personalized AI profiles for each farmer to enable adaptive and context-aware recommendations.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Unique identifier for the profile.
user_id	UUID	REFERENCES auth.users(id)	Links to the user.
farm_id	INTEGER	REFERENCES farms(id)	Links to the farm.
risk_tolerance	REAL	DEFAULT 0.5	A score from 0 (risk-averse) to 1 (risk-taking).
decision_patterns	JSONB	DEFAULT '{}'	Stores historical decision preferences.
success_metrics	JSONB	DEFAULT '{}'	Tracks metrics like yield and profitability.
learning_preferences	JSONB	DEFAULT '{}'	User's preferred communication style (voice, text, etc.).

Sources: supabase/migrations/20250903_phase3_ai_schema.sql:5-16, context/TYPES_AND_INTERFACES.md:124-143

ai_task_recommendations

Contains AI-generated task recommendations for farmers.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Unique identifier for the recommendation.
farm_id	INTEGER	REFERENCES farms(id)	Links to the farm.
task_type	TEXT	CHECK (...)	Type of task (e.g., 'irrigation', 'spray').
recommended_date	DATE	NOT NULL	The date the task is recommended for.
priority_score	REAL	NOT NULL, CHECK (0-1)	A score indicating the task's urgency.
reasoning	TEXT	NOT NULL	AI-generated explanation for the recommendation.
status	TEXT	DEFAULT 'pending'	Status of the task (e.g., 'pending', 'accepted').

Sources: supabase/migrations/20250903_phase3_ai_schema.sql:19-34, context/TYPES_AND_INTERFACES.md:146-167

pest_disease_predictions

Stores predictive alerts for potential pest and disease outbreaks.

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Unique identifier for the prediction.
farm_id	INTEGER	REFERENCES farms(id)	Links to the farm.
pest_disease_type	TEXT	NOT NULL	The type of pest or disease predicted.
risk_level	TEXT	NOT NULL, CHECK (...)	The assessed risk level ('low', 'medium', 'high', 'critical').
probability_score	REAL	NOT NULL, CHECK (0-1)	The likelihood of the outbreak.
predicted_onset_date	DATE	NOT NULL	The expected start date of the outbreak.
recommended_treatments	JSONB	NOT NULL	Suggested chemical, organic, or cultural treatments.

Sources: supabase/migrations/20250903_phase3_ai_schema.sql:37-52, context/TYPES_AND_INTERFACES.md:170-201

Other AI tables include profitability_analyses, market_intelligence, community_insights, and ai_conversation_context, which handle financial analysis, market data, anonymous knowledge sharing, and long-term AI memory, respectively.

Sources: supabase/migrations/20250903_phase3_ai_schema.sql:55-118

Data Types and Interfaces

The application maintains a strong link between the PostgreSQL schema and its TypeScript code to ensure type safety. This is primarily managed through interfaces defined in src/lib/supabase-types.ts and context/TYPES_AND_INTERFACES.md. These interfaces mirror the structure of the database tables.

For example, the farms table in SQL is represented by the Farm interface in TypeScript.

SQL Table Definition (farms)

CREATE TABLE farms (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  region VARCHAR(255) NOT NULL,
  area DECIMAL(10,2) NOT NULL, -- in hectares
  grape_variety VARCHAR(255) NOT NULL,
  planting_date DATE NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  ...
);

Sources: supabase-schema.sql:8-20

TypeScript Interface (Farm)

// Core Farm interface (Application Layer)
export interface Farm {
  id?: number;
  name: string;
  region: string;
  area: number;
  grapeVariety: string;
  plantingDate: string;
  userId?: string;
  // ... other fields
}

Sources: context/TYPES_AND_INTERFACES.md:4-21

This tight coupling allows developers to interact with the database using fully-typed objects, reducing the risk of runtime errors caused by data inconsistencies.

Security Model: Row Level Security (RLS)

The database employs a robust security model using PostgreSQL's Row Level Security (RLS). RLS is enabled on all major tables to ensure that users can only access data associated with their own user_id.

This is achieved by creating policies on each table that check if the user_id of the current authenticated user (auth.uid()) matches the user_id on the record or on the associated farms table.

The following diagram illustrates the data access flow with RLS enabled.

Example RLS Policy

Here is the RLS policy for the farms table, which allows users to perform all CRUD operations only on their own farm records.

-- Enable RLS on the table
ALTER TABLE farms ENABLE ROW LEVEL SECURITY;

-- RLS Policies for farms table
CREATE POLICY "Users can view their own farms" ON farms FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own farms" ON farms FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own farms" ON farms FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own farms" ON farms FOR DELETE USING (auth.uid() = user_id);

Sources: supabase-schema.sql:127-135

Similar policies are applied to all related tables (e.g., irrigation_records, ai_task_recommendations) by checking for the ownership of the linked farm_id. This creates a secure, multi-tenant environment where each user's data is isolated at the database level.

Sources: supabase-schema.sql:138-261, supabase/migrations/20250903184110_phase3_ai_schema.sql:147-195

Summary

The VineSight database schema is a well-structured and secure foundation for the application. It has evolved from a simple record-keeping system to a sophisticated data model that supports complex AI features. The use of PostgreSQL, combined with Supabase's authentication and RLS capabilities, provides a scalable and secure backend. The schema is designed for performance, with appropriate indexes on foreign keys and frequently queried columns, and is built to grow with the addition of new features.