Supabase Schema: Tables, RLS Policies, and Migrations

ComuniTEA’s backend runs on Supabase — a hosted PostgreSQL platform with built-in Auth, Storage, and Row Level Security (RLS). The database stores everything that must survive app reinstalls or sync across devices: user profiles, custom pictogram libraries, sentence logs for the personalised predictor, guided activity sequences, child developmental profiles, and multi-user team memberships. All static vocabulary and exercise content lives exclusively in the TypeScript bundle (offline-first), so Supabase is only contacted for personalisation, reporting, and multi-role collaboration features.

Migrations must be applied in the exact chronological order shown in the Migration History section. Each migration assumes the tables and columns introduced by earlier ones already exist. Applying them out of order will cause ALTER TABLE ... ADD COLUMN and REFERENCES errors.

Core Tables

`profiles`

Created in init_schema.sql. Every authenticated Supabase user gets a matching row here, automatically created by the handle_new_user() trigger.

CREATE TABLE public.profiles (
  id          uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL PRIMARY KEY,
  first_name  text,
  last_name   text,
  avatar_url  text,
  created_at  timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL,
  updated_at  timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL
);

Later migrations extend this table with two additional columns:

-- Added by 20260303000001: vocabulary level selection
ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS level text NOT NULL DEFAULT 'BASICO'
  CHECK (level IN ('BASICO', 'INTERMEDIO', 'AVANZADO'));

-- Added by 20260331000003: adult role in the therapeutic team
ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS role text
  CHECK (role IN ('padre_madre', 'terapeuta', 'psicologo', 'docente'));

RLS policies:

SELECT — user can only read their own row (auth.uid() = id)
UPDATE — user can only update their own row (auth.uid() = id)

There is no INSERT policy on profiles for regular users — rows are created exclusively by the handle_new_user() trigger running as SECURITY DEFINER. This prevents orphan profiles and duplicate rows.

`categories`

User-defined pictogram categories. The app’s built-in vocabulary categories are hard-coded in TypeScript; this table stores only the parent labels for custom pictogram collections.

CREATE TABLE public.categories (
  id         uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id    uuid REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  name       text NOT NULL,
  icon_name  text DEFAULT 'folder',
  color      text DEFAULT '#1A237E',
  created_at timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL
);

RLS policy: ALL operations allowed only when auth.uid() = user_id.

`custom_pictograms`

Stores pictograms uploaded by the family or therapist as part of the “Mi mundo en fotos” feature. Images are stored in the pictograms Storage bucket; image_url is the public URL.

CREATE TABLE public.custom_pictograms (
  id          uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id     uuid REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  category_id uuid REFERENCES public.categories(id) ON DELETE SET NULL,
  label       text NOT NULL,
  image_url   text NOT NULL,
  audio_url   text,
  created_at  timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL
);

Migration 20260303000002 adds a second category reference that maps directly to the app’s built-in vocabulary category IDs:

-- Allows associating a custom pictogram with a built-in category key (e.g. 'comida', 'baño')
ALTER TABLE public.custom_pictograms
  ADD COLUMN IF NOT EXISTS category_key text;

CREATE INDEX IF NOT EXISTS custom_pictograms_user_category_key_idx
  ON public.custom_pictograms (user_id, category_key);

RLS policy: ALL operations allowed only when auth.uid() = user_id.

`sentence_log`

Records every sentence the child plays. The pictogram_ids array is unnested by get_usage_report() to find the child’s most-used pictograms.

CREATE TABLE public.sentence_log (
  id            uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id       uuid REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  pictogram_ids text[] NOT NULL,
  created_at    timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL
);

CREATE INDEX sentence_log_user_created_idx
  ON public.sentence_log (user_id, created_at DESC);

TTL policy (migration 20260331000001): A purge_old_sentence_logs() function deletes rows older than 90 days. When pg_cron is available on the Supabase project it is scheduled to run at 03:00 UTC daily; otherwise it must be called manually or via an Edge Function.

CREATE OR REPLACE FUNCTION purge_old_sentence_logs()
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  DELETE FROM sentence_log
  WHERE created_at < NOW() - INTERVAL '90 days';
END;
$$;

RLS policies:

Base policy (own data): ALL operations when auth.uid() = user_id
Team read policy (Phase 7): SELECT also allowed when has_report_access(user_id) returns true

`tasks`

User-defined routine tasks with ordering and completion tracking.

CREATE TABLE public.tasks (
  id        uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id   uuid REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  label     text NOT NULL,
  emoji     text NOT NULL DEFAULT '📝',
  duration  integer,          -- minutes (nullable)
  completed boolean NOT NULL DEFAULT false,
  position  integer NOT NULL DEFAULT 0,
  created_at  timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL,
  updated_at  timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL
);

CREATE INDEX tasks_user_position_idx ON public.tasks (user_id, position);

An updated_at trigger (set_updated_at()) automatically refreshes the timestamp on every UPDATE. RLS: ALL for own user.

`usage_stats`

Event log for reporting. Tracks four event types that drive the usage report.

CREATE TABLE public.usage_stats (
  id         uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id    uuid REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  event_type text NOT NULL
    CHECK (event_type IN ('pictogram_tap', 'sentence_play', 'free_text', 'session_start')),
  event_data jsonb,
  created_at timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL
);

CREATE INDEX usage_stats_user_type_date_idx
  ON public.usage_stats (user_id, event_type, created_at DESC);

RLS policies:

Base policy: ALL for own user
Team read policy (Phase 7): SELECT also allowed via has_report_access(user_id)

`parental_settings`

One row per user. Stores the daily screen-time limit and a rolling counter of seconds used today. The counter resets automatically in application logic when date_today differs from the current date.

CREATE TABLE public.parental_settings (
  user_id               uuid PRIMARY KEY REFERENCES public.profiles(id) ON DELETE CASCADE,
  daily_limit_minutes   int NOT NULL DEFAULT 30,
  used_seconds_today    int NOT NULL DEFAULT 0,
  date_today            date NOT NULL DEFAULT (CURRENT_DATE AT TIME ZONE 'UTC')
);

RLS: ALL for own user.

`child_profiles`

Clinical and communicative profile of the child, captured during onboarding. This is the central table for child-specific configuration.

CREATE TABLE public.child_profiles (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id              uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,

  -- Basic data
  name                 text NOT NULL,
  birth_date           date,
  gender               text CHECK (gender IN ('masculino','femenino','otro','prefiero_no_decir')),
  avatar_url           text,

  -- Clinical and communicative profile
  diagnosis            text CHECK (diagnosis IN
                         ('dsm5_nivel1','dsm5_nivel2','dsm5_nivel3','sin_diagnostico')),
  communication_level  text NOT NULL DEFAULT 'sin_lenguaje'
                       CHECK (communication_level IN
                         ('sin_lenguaje','palabras_aisladas','frases_simples','frases_complejas')),

  -- Usage context
  environments         text[] NOT NULL DEFAULT '{}',
  preferred_activities text[] NOT NULL DEFAULT '{}',

  -- Sensory configuration
  sound_sensitive      boolean NOT NULL DEFAULT false,

  -- Onboarding state
  onboarding_completed boolean NOT NULL DEFAULT false,

  created_at           timestamptz NOT NULL DEFAULT now()
);

Migration 20260423120000 adds a free-text array for important people’s names (used to populate the PERSONAS_ROW with personalised pictograms):

ALTER TABLE public.child_profiles
  ADD COLUMN IF NOT EXISTS important_people text[] NOT NULL DEFAULT '{}';

RLS: ALL for own user (auth.uid() = user_id).

`child_progress`

Gamification progress table — one row per child_profile. Tracks XP, level, daily sentence count, activity streak, and overall accuracy.

CREATE TABLE public.child_progress (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  child_profile_id   uuid NOT NULL UNIQUE REFERENCES public.child_profiles(id) ON DELETE CASCADE,
  xp                 integer NOT NULL DEFAULT 0 CHECK (xp >= 0),
  level              integer NOT NULL DEFAULT 1 CHECK (level >= 1),
  sentences_today    integer NOT NULL DEFAULT 0 CHECK (sentences_today >= 0),
  streak_days        integer NOT NULL DEFAULT 0 CHECK (streak_days >= 0),
  last_active_date   date,
  achievements       text[]  NOT NULL DEFAULT '{}',
  correct_attempts   integer NOT NULL DEFAULT 0 CHECK (correct_attempts >= 0),
  total_attempts     integer NOT NULL DEFAULT 0 CHECK (total_attempts >= 0),
  updated_at         timestamptz NOT NULL DEFAULT now()
);

RLS: ALL when a child_profiles row exists with cp.user_id = auth.uid() — access is checked through the parent profile, not directly.

`guided_activities` and `activity_sessions`

Adults create structured activity sequences (guided_activities); each run is recorded in activity_sessions for the progress report.

CREATE TABLE guided_activities (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  type       TEXT NOT NULL CHECK (type IN ('rutina_visual','practica_vocabulario','pregunta')),
  steps      JSONB NOT NULL DEFAULT '[]',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE activity_sessions (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  activity_id     UUID NOT NULL REFERENCES guided_activities(id) ON DELETE CASCADE,
  steps_total     INT NOT NULL,
  steps_completed INT NOT NULL,
  completed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

RLS: ALL for own user on both tables.

`child_team` and `share_tokens` (Phase 7 — Multi-role)

Supports multiple adults (parents, therapists, teachers) collaborating on a single child profile.

CREATE TABLE public.child_team (
  id                  uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  child_id            uuid REFERENCES public.child_profiles(id) ON DELETE CASCADE NOT NULL,
  invited_by          uuid REFERENCES public.profiles(id)        ON DELETE CASCADE NOT NULL,
  user_id             uuid REFERENCES public.profiles(id)        ON DELETE CASCADE,  -- NULL until accepted
  role                text NOT NULL CHECK (role IN ('padre_madre','terapeuta','psicologo','docente')),
  can_view_reports    boolean NOT NULL DEFAULT true,
  can_edit_vocabulary boolean NOT NULL DEFAULT false,
  invite_token        text UNIQUE NOT NULL DEFAULT encode(gen_random_bytes(12), 'hex'),
  invite_email        text,
  status              text NOT NULL DEFAULT 'pending'
                      CHECK (status IN ('pending','active','revoked')),
  created_at          timestamptz NOT NULL DEFAULT now(),
  accepted_at         timestamptz
);

CREATE TABLE public.share_tokens (
  id         uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id    uuid REFERENCES public.profiles(id)       ON DELETE CASCADE NOT NULL,
  child_id   uuid REFERENCES public.child_profiles(id) ON DELETE CASCADE NOT NULL,
  token      text UNIQUE NOT NULL DEFAULT encode(gen_random_bytes(18), 'hex'),
  expires_at timestamptz NOT NULL DEFAULT (now() + interval '7 days'),
  created_at timestamptz NOT NULL DEFAULT now()
);

RLS on child_team:

The invited_by owner can perform all operations.
The invited user_id can read their own pending/active row.

RLS on share_tokens: ALL for own user_id.

Row Level Security (RLS)

Every table in ComuniTEA has RLS enabled. The guiding principle is strict isolation by default, opt-in sharing through explicit team membership.

RLS is always on

Every CREATE TABLE statement is followed by ALTER TABLE ... ENABLE ROW LEVEL SECURITY. No table is left without policies.

Own-data baseline

Every table has at least one policy that restricts access to rows where auth.uid() matches the user_id (or id for profiles). Anonymous or mismatched users get zero rows.

Team read escalation

Phase 7 adds has_report_access(p_owner_id) — a STABLE SECURITY DEFINER helper function that checks whether the calling user is an active team member with can_view_reports = true for any child profile belonging to p_owner_id. This function is used in SELECT policies on usage_stats, game_sessions, and sentence_log.

Storage Buckets

INSERT INTO storage.buckets (id, name, public)
VALUES ('pictograms', 'pictograms', true)
ON CONFLICT (id) DO NOTHING;

The pictograms bucket is public — all uploaded images are accessible via their public URL without authentication. Upload, update, and delete are restricted to the owning user via auth.uid() = owner policies on storage.objects.

Because the bucket is public, avoid storing sensitive personal data (e.g., unblurred photos identifying children) in it. ComuniTEA uses it for custom pictogram artwork only.

Triggers and Functions

`handle_new_user()` — Auto-profile creation

Fires AFTER INSERT ON auth.users and inserts a matching row into public.profiles, copying first_name, last_name, and avatar_url from the user’s raw_user_meta_data. The function runs as SECURITY DEFINER so it can insert without an INSERT RLS policy being needed for end-users.

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, first_name, last_name, avatar_url)
  VALUES (
    new.id,
    new.raw_user_meta_data->>'first_name',
    new.raw_user_meta_data->>'last_name',
    new.raw_user_meta_data->>'avatar_url'
  );
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

`set_updated_at()` — Automatic timestamp trigger

Used on the tasks table to keep updated_at current on every update:

CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = timezone('utc', now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

`get_usage_report(p_days)` — Reporting aggregate

Returns a jsonb object with usage statistics for the authenticated user over the last p_days days. Includes event-type counts, hourly distribution, top 10 pictograms by frequency (derived from sentence_log), and a human-readable Spanish description.

CREATE OR REPLACE FUNCTION public.get_usage_report(p_days integer DEFAULT 7)
RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public
AS $$ ... $$;

GRANT EXECUTE ON FUNCTION public.get_usage_report(integer) TO authenticated;

The returned JSON shape:

{
  "description":    "En los últimos 7 días hubo 3 sesiones. Se reprodujeron 12 frases...",
  "byEventType":    { "session_start": 3, "sentence_play": 12, "pictogram_tap": 47 },
  "byHour":         { "9": 8, "10": 15, "16": 24 },
  "topPictograms":  [{ "id": "quiero", "count": 18 }, { "id": "manzana", "count": 11 }],
  "sessions":       3,
  "sentencePlays":  12,
  "pictogramTaps":  47,
  "periodDays":     7
}

`get_daily_usage(p_days)` — Daily breakdown

Returns one row per day with session and interaction counts — used by the report chart component:

CREATE OR REPLACE FUNCTION get_daily_usage(p_days integer DEFAULT 7)
RETURNS TABLE (
  day            date,
  sessions       bigint,
  sentence_plays bigint,
  pictogram_taps bigint,
  ai_expands     bigint
)
LANGUAGE sql SECURITY DEFINER AS $$ ... $$;

`accept_team_invite(p_token)` — Team membership

Accepts a pending child_team invitation by token, setting user_id = auth.uid(), status = 'active', and accepted_at = now(). Raises an exception if the token is invalid or already used.

CREATE OR REPLACE FUNCTION public.accept_team_invite(p_token text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ ... $$;

GRANT EXECUTE ON FUNCTION public.accept_team_invite(text) TO authenticated;

`has_report_access(p_owner_id)` — Team RLS helper

CREATE OR REPLACE FUNCTION public.has_report_access(p_owner_id uuid)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER AS $$
  SELECT EXISTS (
    SELECT 1
    FROM   public.child_team  ct
    JOIN   public.child_profiles cp ON cp.id = ct.child_id
    WHERE  ct.user_id          = auth.uid()
      AND  ct.status           = 'active'
      AND  ct.can_view_reports = true
      AND  cp.user_id          = p_owner_id
  );
$$;

`purge_old_sentence_logs()` — TTL cleanup

Deletes sentence_log rows older than 90 days. Scheduled via pg_cron at 0 3 * * * (03:00 UTC) when the extension is available.

Migration History

init_schema.sql — Foundation

Creates profiles, categories, custom_pictograms. Adds the pictograms Storage bucket and its access policies. Defines the handle_new_user() trigger.

20260303000001 — Phase 1: Tasks & Usage Level

Adds level column to profiles (default 'BASICO'). Creates tasks table with ordering and completion tracking. Creates usage_stats table for the four core event types.

20260303000002 — Phase 4: Custom Pictograms Category Key

Adds category_key text column to custom_pictograms so custom pictograms can be associated with built-in vocabulary category IDs (e.g., 'comida', 'baño').

20260303000003 — Phase 7: AI Expand Stat

Adds the 'ai_expand' event type to the usage_stats.event_type CHECK constraint.

20260304000001 — Phase 8: Sentence Log

Creates the sentence_log table with pictogram_ids text[] and a user/date compound index.

20260307000001 — Parental Settings

Creates parental_settings with daily screen-time limit and rolling usage counter.

20260307000002 — Report Usage Function

Creates get_usage_report(p_days) — the main reporting stored procedure.

20260331000001 — Phase 0: Sentence Log TTL

Adds idx_sentence_log_created_at index. Defines purge_old_sentence_logs() and optionally schedules it via pg_cron.

20260331000002 — Phase 0: Fix Storage Policies

Corrects storage bucket access policies from init_schema.

20260331000003 — Phase 1: Child Profiles

Adds role column to profiles. Creates child_profiles table with DSM-5 diagnosis, communication level, environments, and sensory configuration.

20260331000004 — Phase 3: Parental Sensory Config

Extends parental and sensory configuration options.

20260331000005 — Phase 4: Guided Activities

Creates guided_activities (JSONB steps) and activity_sessions (completion records).

20260331000006 — Phase 5: Game Sublevel

Creates the game_sessions table for tracking sublevel results and accuracy percentages.

20260331000007 — Phase 6: Report Improvements

Creates daily_usage view and get_daily_usage(p_days) / get_game_history(p_limit) functions.

20260331000008 — Phase 7: Multi-role

Creates child_team and share_tokens. Defines accept_team_invite() and has_report_access(). Adds team-read SELECT policies to usage_stats, game_sessions, and sentence_log.

20260331000009 — Phase 8: AI Engine

AI-engine tables and functions for personalised pictogram suggestions.

20260423120000 — Child Profiles: Important People

Adds important_people text[] column to child_profiles for storing the names of the child’s key relationships (used to populate PERSONAS_ROW with personalised pictograms).

20260423140000 — Child Progress

Creates child_progress table for XP, level, streak, achievements, and attempt accuracy per child profile.

Quick Reference — Table Index

Table	Primary concern	RLS scope
`profiles`	Authenticated user identity & settings	Own row
`categories`	Custom pictogram category labels	Own user
`custom_pictograms`	Uploaded pictogram images & audio	Own user
`tasks`	Daily routine task list	Own user
`usage_stats`	Event log (taps, sentences, sessions)	Own user + team read
`parental_settings`	Screen-time limit & daily counter	Own user
`sentence_log`	Phrase history for predictor & reports	Own user + team read
`child_profiles`	Child’s clinical & communicative profile	Own user
`child_progress`	Child XP, streak, achievements	Via child profile
`guided_activities`	Adult-created activity sequences	Own user
`activity_sessions`	Activity run records	Own user
`child_team`	Multi-adult collaboration memberships	Owner + invited member
`share_tokens`	Time-limited report share links	Own user

Data & State

Feature Modules

Development

Supabase Schema: Tables, RLS Policies, and Migrations

Core Tables

`profiles`

`categories`

`custom_pictograms`

`sentence_log`

`tasks`

`usage_stats`

`parental_settings`

`child_profiles`

`child_progress`

`guided_activities` and `activity_sessions`

`child_team` and `share_tokens` (Phase 7 — Multi-role)

Row Level Security (RLS)

Storage Buckets

Triggers and Functions

`handle_new_user()` — Auto-profile creation

`set_updated_at()` — Automatic timestamp trigger

`get_usage_report(p_days)` — Reporting aggregate

`get_daily_usage(p_days)` — Daily breakdown

`accept_team_invite(p_token)` — Team membership

`has_report_access(p_owner_id)` — Team RLS helper

`purge_old_sentence_logs()` — TTL cleanup

Migration History

Quick Reference — Table Index

Build docs developers (and LLMs) love

Data & State

Feature Modules

Development

Documentation Index

​Core Tables

​profiles

​categories

​custom_pictograms

​sentence_log

​tasks

​usage_stats

​parental_settings

​child_profiles

​child_progress

​guided_activities and activity_sessions

​child_team and share_tokens (Phase 7 — Multi-role)

​Row Level Security (RLS)

​Storage Buckets

​Triggers and Functions

​handle_new_user() — Auto-profile creation

​set_updated_at() — Automatic timestamp trigger

​get_usage_report(p_days) — Reporting aggregate

​get_daily_usage(p_days) — Daily breakdown

​accept_team_invite(p_token) — Team membership

​has_report_access(p_owner_id) — Team RLS helper

​purge_old_sentence_logs() — TTL cleanup

​Migration History

​Quick Reference — Table Index

Build docs developers (and LLMs) love

Core Tables

`profiles`

`categories`

`custom_pictograms`

`sentence_log`

`tasks`

`usage_stats`

`parental_settings`

`child_profiles`

`child_progress`

`guided_activities` and `activity_sessions`

`child_team` and `share_tokens` (Phase 7 — Multi-role)

Row Level Security (RLS)

Storage Buckets

Triggers and Functions

`handle_new_user()` — Auto-profile creation

`set_updated_at()` — Automatic timestamp trigger

`get_usage_report(p_days)` — Reporting aggregate

`get_daily_usage(p_days)` — Daily breakdown

`accept_team_invite(p_token)` — Team membership

`has_report_access(p_owner_id)` — Team RLS helper

`purge_old_sentence_logs()` — TTL cleanup

Migration History

Quick Reference — Table Index