File size: 3,698 Bytes
3454374 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | -- Supabase Database Schema for Species Storage
-- Run this in your Supabase SQL Editor to create the required tables
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Create species table (combined mushrooms and plants)
CREATE TABLE IF NOT EXISTS public.species (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
sno INTEGER NOT NULL,
species_name TEXT NOT NULL,
species_type TEXT NOT NULL CHECK (species_type IN ('plant', 'mushroom')),
location TEXT NOT NULL,
latitude NUMERIC NOT NULL,
longitude NUMERIC NOT NULL,
added_by TEXT DEFAULT 'system',
notes TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
UNIQUE(species_name, location)
);
-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_species_type ON public.species(species_type);
CREATE INDEX IF NOT EXISTS idx_species_name ON public.species(species_name);
CREATE INDEX IF NOT EXISTS idx_location ON public.species(location);
-- Create recognition logs table
CREATE TABLE IF NOT EXISTS public.recognition_logs (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
species_name TEXT NOT NULL,
confidence NUMERIC NOT NULL,
location TEXT DEFAULT 'N/A',
latitude TEXT DEFAULT 'N/A',
longitude TEXT DEFAULT 'N/A',
status TEXT NOT NULL,
identified_by TEXT DEFAULT 'BioCLIP',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);
-- Create new species submissions table
CREATE TABLE IF NOT EXISTS public.species_submissions (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
species_name TEXT NOT NULL,
species_type TEXT NOT NULL CHECK (species_type IN ('plant', 'mushroom')),
location TEXT NOT NULL,
user_id TEXT,
user_email TEXT,
notes TEXT,
image_url TEXT,
status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);
-- Enable Row Level Security (RLS) - Public read access for species
ALTER TABLE public.species ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.recognition_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.species_submissions ENABLE ROW LEVEL SECURITY;
-- Allow public read access to species (everyone can view MCC species)
CREATE POLICY "Species are viewable by everyone"
ON public.species FOR SELECT
USING (true);
-- Allow public insert for new species submissions (will be reviewed)
CREATE POLICY "Anyone can submit new species"
ON public.species_submissions FOR INSERT
WITH CHECK (true);
-- Allow public read of own submissions
CREATE POLICY "Anyone can view submissions"
ON public.species_submissions FOR SELECT
USING (true);
-- Allow public insert for recognition logs
CREATE POLICY "Anyone can log recognitions"
ON public.recognition_logs FOR INSERT
WITH CHECK (true);
-- Create function to handle updated_at
CREATE OR REPLACE FUNCTION handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Create triggers for updated_at
DROP TRIGGER IF EXISTS set_updated_at ON public.species;
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON public.species
FOR EACH ROW
EXECUTE FUNCTION handle_updated_at();
DROP TRIGGER IF EXISTS set_updated_at_submissions ON public.species_submissions;
CREATE TRIGGER set_updated_at_submissions
BEFORE UPDATE ON public.species_submissions
FOR EACH ROW
EXECUTE FUNCTION handle_updated_at();
-- Success message
DO $$
BEGIN
RAISE NOTICE 'Database schema created successfully!';
RAISE NOTICE 'Next step: Run migrate_csv_to_supabase.py to import your existing data';
END $$;
|