Database

LinguaAI uses PostgreSQL hosted on Supabase. 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)

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.

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:

-- 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):

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.

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:

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.

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:

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:

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