Sync

PostGIS data model

-- TET Companion — PostgreSQL 16 + PostGIS schema (reference)
CREATE SCHEMA IF NOT EXISTS extensions;
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA extensions;

CREATE TYPE group_role AS ENUM ('leader', 'sweep', 'member');
CREATE TYPE bike_category AS ENUM ('heavy', 'dual', 'light');
CREATE TYPE hazard_type AS ENUM ('sheepdog', 'tree', 'river', 'gate', 'washout', 'sos');

CREATE TABLE expedition_groups (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  is_private boolean NOT NULL DEFAULT true,
  privacy_zone_m integer NOT NULL DEFAULT 500,
  created_by uuid NOT NULL REFERENCES auth.users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE group_memberships (
  group_id uuid REFERENCES expedition_groups(id) ON DELETE CASCADE,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  role group_role NOT NULL DEFAULT 'member',
  bike text, bike_cat bike_category,
  PRIMARY KEY (group_id, user_id)
);
-- exactly one leader per group
CREATE UNIQUE INDEX one_leader_per_group ON group_memberships(group_id) WHERE role = 'leader';

CREATE TABLE expedition_trips (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id uuid NOT NULL REFERENCES expedition_groups(id) ON DELETE CASCADE,
  title text NOT NULL,
  started_at timestamptz, ended_at timestamptz,
  share_token text UNIQUE
);

CREATE TABLE rider_tracks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  trip_id uuid NOT NULL REFERENCES expedition_trips(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES auth.users(id),
  source text CHECK (source IN ('fit','gpx','live')),
  geom extensions.geometry(LineStringZ, 4326),
  distance_m double precision, ascent_m double precision
);
CREATE INDEX rider_tracks_geom ON rider_tracks USING gist (geom);

CREATE TABLE telemetry_points (
  track_id uuid NOT NULL REFERENCES rider_tracks(id) ON DELETE CASCADE,
  recorded_at timestamptz NOT NULL,
  geom extensions.geometry(PointZ, 4326) NOT NULL,
  speed_kmh real, heart_rate smallint, cadence smallint, battery_pct real,
  PRIMARY KEY (track_id, recorded_at)
);
CREATE INDEX telemetry_geom ON telemetry_points USING gist (geom);
CREATE INDEX telemetry_time ON telemetry_points USING brin (recorded_at);

CREATE TABLE trip_hazards (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  client_id text UNIQUE,              -- idempotency key from offline outbox
  trip_id uuid REFERENCES expedition_trips(id) ON DELETE SET NULL,
  reported_by uuid NOT NULL REFERENCES auth.users(id),
  type hazard_type NOT NULL,
  severity smallint NOT NULL CHECK (severity BETWEEN 1 AND 3),
  geom extensions.geometry(Point, 4326) NOT NULL,
  note text, status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX trip_hazards_geom ON trip_hazards USING gist (geom);

-- RBAC helper
CREATE FUNCTION group_role_of(_group uuid, _user uuid) RETURNS group_role
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT role FROM group_memberships WHERE group_id = _group AND user_id = _user
$$;

ALTER TABLE expedition_groups ENABLE ROW LEVEL SECURITY;
ALTER TABLE group_memberships ENABLE ROW LEVEL SECURITY;
ALTER TABLE expedition_trips ENABLE ROW LEVEL SECURITY;
ALTER TABLE trip_hazards ENABLE ROW LEVEL SECURITY;

CREATE POLICY "members read group" ON expedition_groups FOR SELECT TO authenticated
  USING (NOT is_private OR group_role_of(id, auth.uid()) IS NOT NULL);
CREATE POLICY "leader manages members" ON group_memberships FOR ALL TO authenticated
  USING (group_role_of(group_id, auth.uid()) = 'leader');
CREATE POLICY "members read trips" ON expedition_trips FOR SELECT TO authenticated
  USING (group_role_of(group_id, auth.uid()) IS NOT NULL);
CREATE POLICY "leader/sweep edit trips" ON expedition_trips FOR UPDATE TO authenticated
  USING (group_role_of(group_id, auth.uid()) IN ('leader','sweep'));
CREATE POLICY "anyone logs hazards" ON trip_hazards FOR INSERT TO authenticated
  WITH CHECK (reported_by = auth.uid());
CREATE POLICY "hazards are public" ON trip_hazards FOR SELECT TO authenticated USING (true);