.. _database:

Database
========

LinguaAI uses **PostgreSQL** hosted on `Supabase <https://supabase.com>`_.
The schema uses a hybrid approach: relational tables for structured data and
JSONB columns (planned) for flexible AI output such as error logs and
pronunciation feedback.

Migrations live in ``backend/supabase/migrations/``.

Custom Types (Enums)
--------------------

.. code-block:: sql

   CREATE TYPE proficiency_level AS ENUM (
       'Beginner',
       'Elementary',
       'Intermediate',
       'Advanced',
       'Fluent'
   );

   CREATE TYPE goal_enum AS ENUM (
       'Travel & Tourism',
       'Business & Work',
       'Education',
       'Daily Conversation',
       'Culture & Entertainment',
       'Family & Friends'
   );

   CREATE TYPE focus_area_enum AS ENUM (
       'Speaking',
       'Listening',
       'Reading',
       'Writing',
       'Vocabulary',
       'Grammar'
   );

Tables
------

profiles
~~~~~~~~

Stores user-facing profile data. The ``id`` column is a foreign key to
Supabase's managed ``auth.users`` table.

.. list-table::
   :header-rows: 1
   :widths: 22 18 10 50

   * - Column
     - Type
     - Nullable
     - Notes
   * - ``id``
     - ``uuid``
     - No
     - Primary key; references ``auth.users(id)``
   * - ``email``
     - ``text``
     - No
     - Unique
   * - ``full_name``
     - ``text``
     - No
     - ``CHECK (length(full_name) >= 3)``
   * - ``native_language``
     - ``text``
     - Yes
     -
   * - ``english_proficiency``
     - ``proficiency_level``
     - No
     - Default: ``'Beginner'``
   * - ``goals``
     - ``goal_enum[]``
     - Yes
     - Array of selected learning goals
   * - ``focus_areas``
     - ``focus_area_enum[]``
     - Yes
     - Array of selected focus areas
   * - ``avatar_url``
     - ``text``
     - Yes
     -
   * - ``provider``
     - ``text``
     - No
     - Default: ``'email'``; also ``'google'``
   * - ``google_id``
     - ``text``
     - Yes
     - Unique where not null
   * - ``created_at``
     - ``timestamptz``
     - No
     - Default: ``now()``
   * - ``updated_at``
     - ``timestamptz``
     - Yes
     -

**Indexes:**

.. code-block:: sql

   -- Enforced by UNIQUE constraint
   profiles_email_key ON profiles (email);

   -- Partial unique index (only when google_id is not null)
   profiles_google_id_unique ON profiles (google_id)
       WHERE google_id IS NOT NULL;

**Row Level Security (RLS):**

.. list-table::
   :header-rows: 1
   :widths: 20 25 55

   * - Operation
     - Policy name
     - Rule
   * - ``SELECT``
     - Public profiles are viewable
     - Allow all (``USING (true)``)
   * - ``INSERT``
     - Users can insert own profile
     - ``WITH CHECK (auth.uid() = id)``
   * - ``UPDATE``
     - Users can update own profile
     - ``USING (auth.uid() = id)``

field_specific
~~~~~~~~~~~~~~

A vocabulary dictionary table used to serve word definitions, examples, and
difficulty metadata to the learning module.

.. list-table::
   :header-rows: 1
   :widths: 22 18 10 50

   * - Column
     - Type
     - Nullable
     - Notes
   * - ``id``
     - ``bigint``
     - No
     - Primary key, auto-increment
   * - ``word``
     - ``text``
     - No
     -
   * - ``definition``
     - ``text``
     - No
     -
   * - ``example_sentence``
     - ``text``
     - Yes
     -
   * - ``part_of_speech``
     - ``text``
     - Yes
     - e.g. ``noun``, ``verb``
   * - ``difficulty_level``
     - ``text``
     - No
     - ``'Beginner'`` \| ``'Intermediate'`` \| ``'Advanced'``
   * - ``category``
     - ``text``
     - No
     - Topic category (e.g. ``'Travel'``, ``'Business'``)
   * - ``created_at``
     - ``timestamptz``
     - No
     - Default: ``now()``

**Indexes:**

.. code-block:: sql

   idx_dictionary_word     ON field_specific (word);
   idx_dictionary_category ON field_specific (category);

Database Triggers
-----------------

handle_new_user
~~~~~~~~~~~~~~~

Automatically creates a ``profiles`` row whenever a new user registers via
Supabase Auth, preventing orphaned ``auth.users`` records.

.. code-block:: sql

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

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

PostgreSQL Extensions
---------------------

The following extensions are enabled on the Supabase project:

.. list-table::
   :header-rows: 1
   :widths: 30 70

   * - Extension
     - Purpose
   * - ``uuid-ossp``
     - UUID generation (``uuid_generate_v4()``)
   * - ``pgcrypto``
     - Cryptographic functions
   * - ``pg_graphql``
     - Automatic GraphQL API (Supabase feature)
   * - ``pg_stat_statements``
     - Query performance statistics
   * - ``supabase_vault``
     - Encrypted secret storage

Planned Tables
--------------

The following tables are planned for future modules:

.. list-table::
   :header-rows: 1
   :widths: 30 70

   * - Table
     - Purpose
   * - ``sessions``
     - AI tutor conversation sessions
   * - ``messages``
     - Individual messages within a session
   * - ``pronunciation_attempts``
     - Audio attempts with phoneme-level scores (JSONB)
   * - ``vocabulary_progress``
     - Spaced-repetition state per word per user
   * - ``xp_events``
     - Gamification XP ledger
   * - ``badges``
     - Badge definitions
   * - ``user_badges``
     - Badges earned by each user