Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/astrxnomo/manizalesdepie/llms.txt

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

The database is the map. All coordinates are geography(point, 4326) columns managed by PostGIS. Row-level security (RLS) policies guard every table; security_invoker views ensure those policies apply even through view queries. The migration that creates the full schema is supabase/migrations/20260813000000_init.sql.
PostGIS columns serialise as WKB hex strings through PostgREST — they are not usable as map coordinates directly. Always read site and work order locations through the site_public and work_order_public views, which project longitude and latitude as numeric columns. Both views are declared security_invoker = on, which means RLS policies on the underlying tables apply to whoever is querying the view.

​
Enums

The schema defines nine PostgreSQL enum types. Enum values are never deleted once inserted — existing rows would fail validation — so the application layer is responsible for ignoring values that are no longer shown in the UI.
EnumValues
user_rolevisitor, contributor, curator
municipalitymanizales, villamaria
site_typecollection_point, shelter, blood_donation, vet_clinic, water_point, medical_post, census_point†
site_statusopen, full, closed, unknown
item_modeneeded, not_accepted, sufficient
work_order_categorydebris_removal, animal_rescue, structural_risk, supplies, water, other
work_order_statusunclaimed, claimed, attended†, closed_completed, closed_by_others, closed_rejected
resource_typedump_truck, pickup, tools, warehouse, free_transport, machinery, home_stay†, other
confirmation_resultstill_valid, changed, no_longer_valid
† Added in a later migration, not in 20260813000000_init.sql: census_point in 20260814040000_site_type_census_point.sql; attended in 20260815020000_work_order_update_kinds.sql; home_stay in 20260814130000_resource_offer_public.sql. The item_mode enum captures a three-way state for items at a site. The not_accepted value is the most operationally important: the Red Cross explicitly asks people not to donate used clothing, and that refusal has to be visible on the map card. The work_order_status enum follows the Crisis Cleanup lifecycle. Closing reasons are deliberately honest: closed_by_others records that the job was already done on arrival — useful information rather than a failure. The application derives a simpler rollup (untouched, onTheWay, partial, advanced, reopened, done, dismissed) for public display; see lib/labels.ts.

​
Tables

The schema creates ten tables. All writes to the mapped entities go through the server-side data layer using the service role; no direct INSERT or UPDATE policy is granted to anon or authenticated roles at the table level, which prevents a second, ungated write path from being opened accidentally.
TableDescription
profileUser record linked to auth.users; holds role, full_name, and whatsapp. Created automatically by the handle_new_user trigger on every new auth sign-up.
neighborhood114 official barrios of Manizales plus Villamaría with a centroid geography column. Sourced from the SIG Alcaldía de Manizales open data portal (Acuerdo Municipal 589 de 2004).
siteAid locations — shelters, collection points, blood donation centres, medical posts, census points. Carries published, confirmed_at, expires_at, and an optional merged_into_id for duplicate resolution.
site_itemItems a site needs, refuses (not_accepted), or already has enough of. Each row has a priority for ordering and a unique constraint on (site_id, label, mode).
work_orderCommunity needs (debris removal, structural risk, supplies) with an approx_location that is deliberately block-level, not exact. Status is derived by the sync_work_order_state trigger from the update thread.
work_order_updateAppend-only thread entries for a work order: on_the_way, helped, still_needed, not_real. The trigger reads these to derive work_order.status.
resource_offerNeighbour-offered resources: trucks, tools, home stays. Carries a whatsapp contact (required) and optional available_from / available_until.
animal_reportLost, found, or sighted animals with a photo URL, species, description, and contact. The location is the last-seen point (nullable) — a lost animal has no fixed location.
neighborhood_needCurator-declared per-barrio priority levels (critical, high, normal). Drives the Frentes panel layer when a curator has entered data.
The confirmation table is a single append-only log for all confirmable entities (site, work_order, resource_offer, animal_report). It uses a loose reference (entity enum + entity_id UUID, no foreign key) instead of separate confirmation tables per entity. The live counter lives denormalized on each table’s confirmed_at column, because the map reads it on every render.

​
Key triggers

set_neighborhood_from_location fires BEFORE INSERT OR UPDATE on site, work_order, and resource_offer. It uses a PostGIS point-in-polygon query against the neighborhood table to stamp neighborhood_id automatically from the row’s coordinate. The application never writes neighborhood_id directly — the trigger derives it, and it stays out of update payloads so the trigger cannot be bypassed by passing the old value. This trigger runs even when rows are loaded via direct database import (MCP spreadsheet loads), which is the reason the logic lives in the database rather than in the DAL. sync_work_order_state fires AFTER INSERT OR DELETE on work_order_update. It reads the full update thread for the work order and derives work_order.status from the entries. The application never sets work_order.status directly — if a feature ever needs to set it, it is the feature that is wrong. Closing a work order is exclusively a curator’s action (closed_completed, closed_by_others, closed_rejected), and sync_work_order_state freezes every closed state against later entries. handle_new_user fires AFTER INSERT on auth.users and creates a matching profile row. It is declared SECURITY DEFINER so it runs with the function owner’s rights rather than the triggering user’s.

​
Security model

Every table has RLS enabled. The security model has three tiers:
  1. Public reads — published rows on site, work_order, resource_offer, and animal_report are readable by anyone (including anonymous visitors). The product’s core value — finding a shelter — never requires an account.
  2. Curator reads — the is_curator() function (a SECURITY DEFINER SQL function that checks profile.role) is used in every read policy. A curator sees unpublished rows in addition to published ones, which is what makes the published = false toggle reversible without a direct database query.
  3. Sensitive data — contact details on work_order (exact address, contact name, phone) are projected into the public view and visible to anyone. The fields are optional and the report form warns that anything entered will be publicly visible.
All inserts begin with published = false in seed data. The application itself publishes reports immediately (open publication policy), but the seed data is loaded unpublished on purpose: seed coordinates are approximate and unverified, and sending someone to the wrong shelter during an emergency is worse than having no pin at all. The site_public and work_order_public views project only safe columns and are declared security_invoker = on: 
create view site_public with (security_invoker = on) as
select s.id,
       s.type,
       s.name,
       s.description,
       s.address,
       st_x(s.location::geometry) as longitude,
       st_y(s.location::geometry) as latitude,
       n.name                     as neighborhood,
       s.status,
       s.schedule,
       s.whatsapp,
       s.source_url,
       (s.verified_at is not null) as verified,
       s.confirmed_at,
       s.expires_at,
       s.published,
       s.created_by
from site s
left join neighborhood n on n.id = s.neighborhood_id
where s.merged_into_id is null;
Without security_invoker = on, the view would execute with the definer’s rights and quietly bypass every RLS policy on site. The flag is load-bearing, not a style choice.

Build docs developers (and LLMs) love

Get started for freeTalk to us