Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/ricardomb-tech/surqo/llms.txt

Use this file to discover all available pages before exploring further.

Surqo uses a Supabase-hosted PostgreSQL database. Schema management is handled through six plain SQL migration files located in backend/migrations/. There is no ORM migration runner — you execute each file manually in the Supabase SQL Editor, in the order shown below. This keeps the migration history explicit, auditable, and completely independent of any local toolchain.

​
Migration Order

FilePurpose
001_initial_schema.sqlCreates all base tables, indexes, triggers, views, and helper functions
002_analysis_quota.sqlAdds analyses_used and tokens_used columns to user_profiles
003_devices.sqlCreates the devices table for ESP32 node registration and claim-code pairing
004_analysis_user_id.sqlAdds user_id to analyses so ownership is independent of farm_id
enable_rls.sqlAdds user_id to farms, enables Row Level Security, and creates access policies on all tables
data_retention.sqlRegisters pg_cron jobs that auto-delete old sensor readings, analyses, and resolved alerts

​
Running the Migrations

1

Open the Supabase SQL Editor

Log in to your Supabase project dashboard and navigate to SQL Editor in the left sidebar. You can paste and execute each migration file directly in the editor — no local PostgreSQL client required.
2

Run 001_initial_schema.sql — base schema

This migration creates the entire foundational schema. Copy the contents of backend/migrations/001_initial_schema.sql into the SQL Editor and click Run.The following tables are created:
TableKey columns
user_profilesuser_id (PK, Supabase Auth UUID), plan, email_alerts_this_month
farmsname, crop_type, latitude, longitude, area_hectares, owner_email
sensor_readingsair_temp_c, air_humidity_pct, soil_moisture_pct, soil_temp_c, uv_index, vpd_kpa, battery_mv, rssi_dbm, device_id, farm_id
analysesalert_level, recommendations (JSONB), water_stress_index, irrigation_needed, model_used, input_tokens, output_tokens, cost_usd
alertsseverity, title, recommended_action, is_resolved, email_sent
The migration also creates two composite indexes on sensor_readings for fast time-series queries, a trg_farms_updated_at trigger, the latest_sensor_by_device and farm_daily_kpis views, and a calculate_vpd(temp, humidity) helper function using the Magnus equation.
3

Run 002_analysis_quota.sql — freemium quota columns

This migration adds the two columns that track AI analysis consumption per user on the free plan.
ALTER TABLE user_profiles
    ADD COLUMN IF NOT EXISTS analyses_used INTEGER NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS tokens_used   INTEGER NOT NULL DEFAULT 0;

CREATE INDEX IF NOT EXISTS idx_user_profiles_analyses_used
    ON user_profiles(analyses_used);
Existing rows receive a default of 0 for both columns — no data migration needed.
4

Run 003_devices.sql — ESP32 device registry

This migration creates the devices table, which stores physical ESP32 nodes. Each device registers itself by MAC address and is paired to a farm using a short claim code (the last 6 hex characters of the MAC), so farm IDs no longer need to be hardcoded in firmware.
CREATE TABLE IF NOT EXISTS devices (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    device_mac       varchar(32)  NOT NULL UNIQUE,
    claim_code       varchar(12)  NOT NULL,
    name             varchar(200),
    farm_id          uuid REFERENCES farms(id) ON DELETE SET NULL,
    user_id          uuid REFERENCES user_profiles(user_id) ON DELETE SET NULL,
    firmware_version varchar(20),
    last_seen        timestamptz,
    created_at       timestamptz NOT NULL DEFAULT now()
);
RLS is also enabled on devices — users can only SELECT and UPDATE their own devices. Backend MQTT and HTTP ingestion uses the service key, which bypasses RLS.
5

Run 004_analysis_user_id.sql — explicit analysis ownership

This migration adds a user_id column to the analyses table so that each analysis has an explicit owner, independent of whether its farm_id is still set. Without this column, analyses whose linked farm was deleted (setting farm_id = NULL) became accessible to any authenticated user who knew the analysis UUID.
ALTER TABLE analyses ADD COLUMN IF NOT EXISTS user_id uuid
    REFERENCES user_profiles(user_id) ON DELETE SET NULL;
A backfill UPDATE is included in the file to inherit the owner from the linked farm for any existing analyses.
6

Run enable_rls.sql — Row Level Security

This migration adds a user_id UUID column to farms (linking farms to Supabase Auth users), enables RLS on every table, and creates FOR ALL policies so each user can only read and write their own data.Policies are created on: user_profiles, farms, analyses, sensor_readings, and alerts. Each policy uses auth.uid() — the Supabase-provided function that returns the authenticated user’s UUID from the current JWT.After running this migration, you can verify RLS is active by running the check query included at the bottom of the file:
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename IN ('user_profiles','farms','analyses','sensor_readings','alerts');
All five rows should show rowsecurity = true.
7

Run data_retention.sql — automated cleanup

This migration registers three pg_cron scheduled jobs that automatically prune old data to keep storage within Supabase’s free tier limits:
JobScheduleDeletes
surqo-delete-old-sensor-readingsDaily at 03:00 UTCsensor_readings older than 90 days
surqo-delete-old-analysesSundays at 03:30 UTCanalyses older than 180 days
surqo-delete-old-resolved-alertsSundays at 04:00 UTCResolved alerts older than 60 days
Before running this migration, make sure the pg_cron extension is enabled in your Supabase project: Dashboard → Database → Extensions → pg_cron → Enable.Verify the jobs were registered after running:
SELECT * FROM cron.job;

​
Key Schema Details — Freemium Quota Constants

The free-plan limits are defined as class-level constants on the UserProfile SQLAlchemy model in app/models/user.py: 
# From app/models/user.py
FREE_ANALYSES_LIMIT             = 25
FREE_TOKENS_LIMIT               = 50_000   # anti-abuse ceiling; Groq is free
FREE_OUTPUT_TOKENS_PER_ANALYSIS = 1_000
PAID_OUTPUT_TOKENS_PER_ANALYSIS = 2_048
MAX_FARMS                       = 1
These constants are referenced by the can_use_ai_analysis and max_output_tokens properties at runtime. Changing the limits requires only a code edit — the database schema itself stores raw counters (analyses_used, tokens_used) and the logic lives in the application layer.
The backend automatically creates a UserProfile row the first time a user makes an authenticated request. The row is initialised with plan='free', analyses_used=0, and tokens_used=0 — no manual seed data is required.
Run the migrations in order: 001 → 002 → 003 → 004 → enable_rls.sql → data_retention.sql. Running enable_rls.sql before 001_initial_schema.sql will fail because the tables it references do not yet exist. 003_devices.sql and 004_analysis_user_id.sql must also run before enable_rls.sql so their tables are present when RLS policies are applied.

Build docs developers (and LLMs) love

Get started for freeTalk to us