Database Schema

Village Data uses PostgreSQL with the prototype schema for all application tables.

Core Tables

Users & Auth

Table	Purpose
auth.users	Supabase-managed user accounts
profile	Extended user profile data
user_role	User-to-role assignments
role	Role definitions (admin, user, etc.)

Datasets

Table	Purpose
dataset	Dataset metadata (name, description, owner)
dataset_version	Version tracking with file references

Chatbots

Table	Purpose
chatbot	Chatbot configuration
chatbot_dataset	Many-to-many: chatbots to datasets
chatbot_usage	Per-message analytics

Content & Discovery

Table	Purpose
content_usage	Usage tracking for Library
content_bookmark	User bookmarks
content_list	Custom lists

Key Relationships

profile (1) ──── (n) dataset
    │
    └── (n) chatbot ──── (n) chatbot_dataset ──── (n) dataset
                │
                └── (n) chatbot_usage

Schema: prototype

All application tables live in the prototype schema:

-- Example: Query datasets
SELECT * FROM prototype.dataset WHERE owner_id = auth.uid();

Important: The prototype schema must be exposed in Supabase API settings for REST access.

Row Level Security

All tables use RLS policies. Common patterns:

-- Owner can read their own data
CREATE POLICY "Users can view own datasets"
ON prototype.dataset FOR SELECT
USING (owner_id = auth.uid());

-- Published content is public
CREATE POLICY "Published datasets are public"
ON prototype.dataset FOR SELECT
USING (status = 'published');

Migrations

Migrations are in supabase/migrations/ and follow the naming convention:

001_initial_schema.sql
002_add_chatbots.sql
003_add_content_usage.sql
...

Run migrations:

# Local
supabase db reset

# Production
supabase db push