Introduction to SQL Queries on the LiteFarm Database

This article covers:

An introduction to SQL queries
The overall structure of the LiteFarm database schema
Navigation of the LiteFarm schema using foreign keys
Common and particularly valuable queries for extracting data from LiteFarm

No prior knowledge is necessary, though a basic understanding of relational databases will help.

Things You'll Need

A Postgres client — DBeaver (free, cross-platform) or pgAdmin work well
Read-only access to the beta environment — talk to the LiteFarm data team
The LiteFarm SchemaSpy reference — an interactive, always-current view of every table, column, and foreign key relationship in the database

Reference Videos

Nathan Williams has a great SQL intro playlist. The most relevant videos are linked below. He uses a different dataset and tool (pgAdmin), but the concepts transfer directly.

What Is a Database Schema?

A database schema is a structured collection of tables. Each table represents a concept — like a task, farm, or user — and has columns that describe attributes of that concept. Each row is one instance (record) of that concept.

For example, the users table might look like:

user_id	email	first_name	last_name

user_id	email	first_name	last_name
abc123	jdoe@gmail.com	Jane	Doe
def456	zeus@olympus.gr	Zeus	—

Where things get powerful is joining tables together using primary keys and foreign keys — covered in the Joins video above, and essential for working with LiteFarm data.

A Note on Soft Deletes

LiteFarm almost always uses soft deletes: when a user "deletes" something in the app, the database row isn't removed — instead a deleted flag is set to true. You must always exclude these records or risk pulling incorrect results.

Add this to every table you query:

AND <table>.deleted = false

The Golden Rules of Querying

Before writing any query, internalize these five rules.

Rule 1: Always Use the `real_farm` View (or Its Equivalent)

About 1,050 farms in the database are test or fake entries created by the team during development. Querying the raw farm table will pollute your results.

The real_farm view filters these out. Use it instead of farm in all queries:

SELECT * FROM real_farm rf JOIN ...

If you need to build the filter inline (e.g., in a CTE), here's the full version:

WITH real_farms AS (
    SELECT f.* FROM farm f
    WHERE f.sandbox_farm = false AND f.deleted = false
      AND NOT (
           lower(f.farm_name) LIKE '%test%'
        OR lower(f.farm_name) LIKE '%fake%'
        OR lower(f.farm_name) LIKE '%prueba%'
        OR lower(f.farm_name) LIKE '%sample%'
        OR lower(f.farm_name) LIKE '%demo%'
        OR lower(f.farm_name) LIKE '%dummy%'
        OR lower(f.farm_name) LIKE '%delete%'
        OR lower(f.farm_name) LIKE '%litefarm%'
        OR lower(f.farm_name) LIKE '%example%'
        OR lower(f.farm_name) ~ '\y(hola|none|wrong)\y'
        OR lower(f.farm_name) IN ('abc','abcd','123','1234','12345')
        OR regexp_replace(lower(f.farm_name),'[^a-z]','','g') ~ '^(.)\1{2,}$'
        OR regexp_replace(lower(f.farm_name),'[^a-z]','','g') IN ('asdf','qwerty','zxcv')
      )
      AND f.farm_id NOT IN (
        SELECT uf.farm_id FROM "userFarm" uf
        JOIN users u ON uf.user_id = u.user_id
        GROUP BY uf.farm_id
        HAVING COUNT(*) = COUNT(*) FILTER (WHERE lower(u.email) LIKE '%@litefarm.org')
      )
)

Important: Emails ending in @pseudo.com are real users whose data has been pseudonymized for GDPR compliance. Never filter them out.

Rule 2: Always Filter Soft Deletes

WHERE t.deleted = false
  AND f.deleted = false
  AND l.deleted = false

Rule 3: Always Filter `sandbox_user = false` on the Users Table

WHERE u.sandbox_user = false

Rule 4: Double-Quote camelCase Table Names

PostgreSQL folds unquoted identifiers to lowercase. Tables like userFarm must be quoted or the query will error:

JOIN "userFarm" uf ON ...

Key quoted table names: "userFarm", "userLog", "farmExpense", "farmExpenseType", "organicCertifierSurvey", "supportTicket", "shiftTask", "rolePermissions".

Rule 5: Use Both Task → Farm Join Paths

Tasks have no direct farm_id. They connect to farms via two independent paths — through locations, or through management plans. A task may only exist in one path, so you need both to avoid undercounting. Either use the pre-built task_farm_map view, or union the two paths yourself:

SELECT DISTINCT task_id, farm_id FROM (
    -- Path 1: via locations
    SELECT t.task_id, l.farm_id
    FROM task t
    JOIN location_tasks lt ON t.task_id = lt.task_id
    JOIN location l ON lt.location_id = l.location_id
    WHERE t.deleted = false AND l.deleted = false

    UNION

    -- Path 2: via management plans
    SELECT t.task_id, cv.farm_id
    FROM task t
    JOIN management_tasks mt ON t.task_id = mt.task_id
    JOIN planting_management_plan pmp ON mt.planting_management_plan_id = pmp.planting_management_plan_id
    JOIN management_plan mp ON pmp.management_plan_id = mp.management_plan_id
    JOIN crop_variety cv ON mp.crop_variety_id = cv.crop_variety_id
    WHERE t.deleted = false AND mp.deleted = false
) combined

The Core Data Model

Farm = Account. Everything in LiteFarm belongs to a farm. A user can belong to multiple farms via the "userFarm" junction table.

users ─────< "userFarm" >───── farm
                                 │
                    ┌────────────┼─────────────────────┐
                    │            │                      │
                 location   crop_variety            animal
                    │            │                  animal_batch
                 figure      management_plan
                    │            │
               area/line      planting_management_plan
               /point              │
                              location_tasks ──< task >── management_tasks
                                                  │
                                          [harvest_task, plant_task,
                                           field_work_task, etc.]

For the complete interactive schema — every table, column type, and foreign key — see the LiteFarm SchemaSpy reference.

Key Join Paths (cheat sheet)

Goal	Join Path

Goal	Join Path
User → Farm	`users → "userFarm" → farm`
Task → Farm	`task → location_tasks → location → farm`
Task → Farm (alt)	`task → management_tasks → planting_management_plan → management_plan → crop_variety → farm`
Task → Crop	`task → management_tasks → planting_management_plan → management_plan → crop_variety → crop`
Management Plan → Farm	`management_plan → crop_variety → farm`
Management Plan → Location	`management_plan → planting_management_plan → location`
Location → Geometry	`location → figure → area / line / point`

Users, `"userFarm"`, and Farms

The three most common tables you'll join are users, "userFarm", and farm.

users — primary key: user_id (VARCHAR — a mix of UUIDs, Google numeric IDs, and legacy MongoDB IDs; always treat as text)
farm — primary key: farm_id (UUID)
"userFarm" — junction table holding user_id, farm_id, role_id, and status

role_id maps to roles as follows:

role_id	Role

role_id	Role
1	Farm Owner
2	Manager
3	Worker
5	Extension Officer

Here's a query that finds a user by email or name, then shows every farm they have access to along with their role:

SELECT
    u.first_name,
    u.last_name,
    u.email,
    uf.role_id,
    f.farm_name
FROM users u
JOIN "userFarm" uf ON u.user_id = uf.user_id
JOIN farm f ON uf.farm_id = f.farm_id
WHERE u.sandbox_user = false
  AND f.deleted = false
  AND (
    u.email LIKE '%name@example.com%'  -- replace with actual email
    OR CONCAT(u.first_name, u.last_name) ILIKE '%name%'  -- replace with actual name
  );

-- tells SQL to ignore anything after it — it's how you add comments to a query.

You can reverse this query to start from a farm and find all users connected to it. For example, to find every Farm Owner in the US (country_id = 212; Canada = 37):

SELECT
    u.first_name,
    u.last_name,
    u.email,
    f.farm_name
FROM real_farm f
JOIN "userFarm" uf ON f.farm_id = uf.farm_id
JOIN users u ON uf.user_id = u.user_id
WHERE f.country_id = 212
  AND uf.role_id = 1
  AND uf.status = 'Active'
  AND u.sandbox_user = false;

Note on country_id: Farms created before May 2021 may have a null country_id due to a historical bug. For older farms, use farm.grid_points (a JSON column with lat and lng keys) or farm.address to infer geography.

Locations & Geography

Farms consist of many named places: fields, gardens, greenhouses, barns, gates, and more. The location data is split across several tables:

location  (the name and type of a place)
    └── figure  (1:1, describes the geometry type)
            ├── area    (polygon: total_area, perimeter, grid_points)
            ├── line    (fence/watercourse: length, width, line_points)
            └── point   (gate/valve/pin: lat/lng JSON)

Each specific location type (e.g., field, garden, barn) also has its own table sharing the same location_id, containing type-specific columns.

All areas are stored in m². All lengths are stored in metres. To convert m² to hectares, divide by 10,000.

Count locations on a farm

SELECT COUNT(*)
FROM location
WHERE farm_id = 'your-farm-id-here'
  AND deleted = false;

List all locations on a farm with area

SELECT
    l.name,
    fig.type,
    ROUND(a.total_area / 10000, 4) AS "Total Area (Ha)",
    ROUND(a.perimeter) AS "Perimeter (m)",
    l.notes
FROM location l
JOIN figure fig ON fig.location_id = l.location_id
LEFT JOIN area a ON a.figure_id = fig.figure_id  -- LEFT JOIN: not all figures are areas
WHERE l.farm_id = 'your-farm-id-here'
  AND l.deleted = false;

Count all location types across the entire platform

SELECT fig.type, COUNT(*) AS count
FROM location l
JOIN figure fig ON fig.location_id = l.location_id
JOIN real_farm rf ON l.farm_id = rf.farm_id
WHERE l.deleted = false
GROUP BY fig.type
ORDER BY count DESC;

Field area statistics by farm

SELECT
    rf.farm_name,
    COUNT(*) AS field_count,
    ROUND(SUM(a.total_area) / 10000, 2) AS "Total Area (Ha)",
    ROUND(AVG(a.total_area) / 10000, 4) AS "Avg Field Size (Ha)",
    ROUND(MAX(a.total_area) / 10000, 4) AS "Largest Field (Ha)",
    ROUND(MIN(a.total_area) / 10000, 4) AS "Smallest Field (Ha)"
FROM real_farm rf
JOIN location l ON rf.farm_id = l.farm_id
JOIN figure fig ON l.location_id = fig.location_id
LEFT JOIN area a ON fig.figure_id = a.figure_id
WHERE fig.type = 'field'
  AND l.deleted = false
GROUP BY rf.farm_name
ORDER BY field_count DESC;

Crops & Management Plans

Crop data flows through a hierarchy:

crop  (global catalog — system crops + user-added)
    └── crop_variety  (farm-specific varieties)
            └── management_plan  (a plan to grow a variety)
                    └── planting_management_plan  (plan at a specific location)

Management plan status is derived from two date columns:

Status	Condition

Status	Condition
In Progress	`complete_date IS NULL AND abandon_date IS NULL`
Completed	`complete_date IS NOT NULL`
Abandoned	`abandon_date IS NOT NULL`

Top crops by number of management plans

SELECT
    c.crop_common_name,
    c.crop_group,
    COUNT(DISTINCT mp.management_plan_id) AS plan_count,
    COUNT(DISTINCT cv.farm_id) AS farms_growing
FROM crop c
JOIN crop_variety cv ON c.crop_id = cv.crop_id
JOIN management_plan mp ON cv.crop_variety_id = mp.crop_variety_id
WHERE c.deleted = false AND cv.deleted = false AND mp.deleted = false
GROUP BY c.crop_common_name, c.crop_group
ORDER BY plan_count DESC
LIMIT 20;

Multilingual crops: The same crop may appear under several names across languages (e.g., corn, maize, maíz, milho). Use regex with word boundaries to match all variants without false positives:
AND lower(c.crop_common_name) ~ '\\y(corn|maize|maíz|milho)\\y'

Tasks

All farm work in LiteFarm is represented as tasks. The task table (58,000+ rows) is the central hub — what kind of task it is comes from task_type_id. Each type also has its own extension table (e.g., harvest_task, plant_task) sharing the same task_id.

Task status follows the same pattern as management plans:

Status	Condition

Status	Condition
Pending	`complete_date IS NULL AND abandon_date IS NULL`
Completed	`complete_date IS NOT NULL`
Abandoned	`abandon_date IS NOT NULL`

Task completion rate across the platform

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE complete_date IS NOT NULL) AS completed,
    COUNT(*) FILTER (WHERE abandon_date IS NOT NULL) AS abandoned,
    COUNT(*) FILTER (WHERE complete_date IS NULL AND abandon_date IS NULL) AS pending,
    ROUND(100.0 * COUNT(*) FILTER (WHERE complete_date IS NOT NULL) / NULLIF(COUNT(*), 0), 1) AS completion_pct
FROM task
WHERE deleted = false;

Top farms by completed tasks (last 30 days)

SELECT
    rf.farm_name,
    COUNT(tfm.task_id) AS completed_tasks
FROM real_farm rf
JOIN task_farm_map tfm ON tfm.farm_id = rf.farm_id
JOIN task t ON tfm.task_id = t.task_id
WHERE t.complete_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY rf.farm_id, rf.farm_name
ORDER BY completed_tasks DESC
LIMIT 10;

Harvest quantities for a specific crop

SELECT
    c.crop_common_name,
    COUNT(*) AS harvest_count,
    ROUND(AVG(ht.actual_quantity)::numeric, 2) AS avg_kg,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ht.actual_quantity) AS median_kg
FROM harvest_task ht
JOIN task t ON ht.task_id = t.task_id
JOIN management_tasks mt ON t.task_id = mt.task_id
JOIN planting_management_plan pmp ON mt.planting_management_plan_id = pmp.planting_management_plan_id
JOIN management_plan mp ON pmp.management_plan_id = mp.management_plan_id
JOIN crop_variety cv ON mp.crop_variety_id = cv.crop_variety_id
JOIN crop c ON cv.crop_id = c.crop_id
WHERE t.deleted = false AND mp.deleted = false
  AND ht.actual_quantity IS NOT NULL
  AND ht.actual_quantity > 0
  AND ht.actual_quantity < 100000  -- exclude outliers (max in DB is ~184M kg)
  AND ht.actual_quantity_unit = 'kg'
  AND lower(c.crop_common_name) ~ '\y(corn|maize|maíz|milho)\y'
GROUP BY c.crop_common_name
ORDER BY harvest_count DESC;

Active Farms

A farm is considered active in a given period if it has any of: tasks created or completed, management plans started, or user logins.

Monthly Active Farms

WITH monthly_activity AS (
    SELECT date_trunc('month', t.complete_date)::date AS month, l.farm_id
    FROM task t
    JOIN location_tasks lt ON t.task_id = lt.task_id
    JOIN location l ON lt.location_id = l.location_id
    WHERE t.deleted = false
      AND t.complete_date IS NOT NULL
      AND t.complete_date > '2001-01-01'

    UNION

    SELECT date_trunc('month', mp.start_date)::date, cv.farm_id
    FROM management_plan mp
    JOIN crop_variety cv ON mp.crop_variety_id = cv.crop_variety_id
    WHERE mp.deleted = false AND mp.start_date IS NOT NULL

    UNION

    SELECT date_trunc('month', ul.created_at)::date, ul.farm_id
    FROM "userLog" ul
    WHERE ul.created_at > '2001-01-01'
)
SELECT
    ma.month,
    COUNT(DISTINCT ma.farm_id) AS active_farms
FROM monthly_activity ma
JOIN real_farm rf ON ma.farm_id = rf.farm_id
WHERE ma.month >= '2022-01-01'
GROUP BY ma.month
ORDER BY ma.month;

Growth: New Signups Over Time

Monthly new farms with cumulative total

SELECT
    date_trunc('month', f.created_at)::date AS month,
    COUNT(*) AS new_farms,
    SUM(COUNT(*)) OVER (ORDER BY date_trunc('month', f.created_at)) AS cumulative_farms
FROM real_farm f
WHERE f.created_at > '2001-01-01'
GROUP BY date_trunc('month', f.created_at)
ORDER BY month;

Legacy timestamps: Records created before ~2021 may have a default timestamp of 2000-01-01 00:00:00+00 — not a real date. Always filter created_at > '2001-01-01' for time-series work.

Farms by country

SELECT c.country_name, COUNT(*) AS farm_count
FROM real_farm rf
JOIN countries c ON rf.country_id = c.id
GROUP BY c.country_name
ORDER BY farm_count DESC;

Financials

Revenue

Revenue is recorded in the sale table, with line items in crop_variety_sale. All values are in the farm's local currency (stored in farm.units->>'currency'). Never aggregate monetary values across different currencies.

SELECT
    f.farm_name,
    f.units->>'currency' AS currency,
    COUNT(s.sale_id) AS sale_count,
    ROUND(SUM(cvs.sale_value)::numeric, 2) AS total_revenue
FROM real_farm f
JOIN sale s ON f.farm_id = s.farm_id
JOIN crop_variety_sale cvs ON s.sale_id = cvs.sale_id
WHERE s.deleted = false
GROUP BY f.farm_id, f.farm_name, f.units->>'currency'
ORDER BY total_revenue DESC;

Expenses

Expenses are in "farmExpense" (the table name must be quoted). The value column has extreme outliers — a maximum of ~3.5 trillion — due to data entry errors. Always apply a reasonable upper bound when aggregating.

SELECT
    f.units->>'currency' AS currency,
    SUM(fe.value) AS total_expenses
FROM "farmExpense" fe
JOIN real_farm f ON fe.farm_id = f.farm_id
WHERE fe.deleted = false
  AND fe.value < 1000000  -- exclude obvious data-entry errors (median is ~610)
GROUP BY f.units->>'currency'
ORDER BY total_expenses DESC;

Data Quality Gotchas

Working with real-world farm data means some records are messy. Here's a summary of the most common issues:

Issue	Detail	Mitigation

Issue	Detail	Mitigation
Test/fake farms	~1,050 test entries in `farm`	Always use `real_farm` view
Soft deletes	Most tables have `deleted` boolean	Always filter `deleted = false`
Legacy timestamps	Pre-2021 records may have `2000-01-01` default dates	Filter `created_at > '2001-01-01'`
Expense outliers	`"farmExpense".value` max ≈ 3.5 trillion	Apply `WHERE value < 1000000`
Harvest outliers	`harvest_task.actual_quantity` max ≈ 184M kg	Filter `< 100000` or use median
Currency mixing	All values are in local currency	Never `SUM()` across currencies
Mixed user IDs	`user_id` is a mix of UUIDs, Google IDs, MongoDB IDs	Always treat as VARCHAR
`@pseudo.com` emails	GDPR-pseudonymized real users	Never filter these out
Multilingual crop names	Same crop in many languages	Use regex word-boundary matching
Null `country_id`	~96% null on older farms	Use `grid_points` JSON for geography
Extreme location areas	Some user-entered areas are implausibly large	Apply reasonable upper bounds (e.g., < 500,000,000 m²)

Where to Go Next

LiteFarm SchemaSpy — interactive reference for all tables, columns, and foreign keys
SQL deep dives: Subqueries and Common Table Expressions (CTEs)