Platform

Data model

Additive tables on Postgres 17, defined with Drizzle in packages/db. Better Auth owns the base user table. Two vector columns and one vector table carry the AI features, and row-level security scopes both rows and vectors.

Entity relationships

Only the columns that matter for the AI features and access rules are drawn inside the entities. The full column lists are below.

Entity relationship diagram

user is the Better Auth table. Vector columns are 768 wide.

Table reference

Open a group for its columns. Types are Postgres types; UUID primary keys use gen_random_uuid().

People and roles

Table	Columns
`user_roles`	user_id UUID FK user, role user_role, approved BOOLEAN default false. PK (user_id, role). Enum user_role = super_admin, admin, trainer, trainee
`trainee_profiles`	user_id PK, qualifications JSONB, work_experience JSONB, interests TEXT[], skills TEXT[], certificates JSONB, bio, updated_at
`trainer_profiles`	user_id PK, specialization_ids UUID[] (dropdown_options), bio, competency_embedding VECTOR(768), embedding_model TEXT, updated_at

Courses and resources

Table	Columns
`courses`	id, title, description, subject_id FK dropdown_options, trainer_id FK user, status draft\|published\|archived, competency_embedding VECTOR(768), embedding_model, created_at
`enrollments`	id, course_id, trainee_id, status active\|completed\|dropped, enrolled_at. UNIQUE (course_id, trainee_id)
`learning_resources`	id, course_id, uploaded_by, resource_type lecture\|presentation\|study_material, title, file_path, created_at
`resource_chunks`	id, resource_id FK ON DELETE CASCADE, chunk_text, embedding VECTOR(768), embedding_model

Assessments

Table	Columns
`questionnaires`	id, course_id, created_by, title, deadline TIMESTAMPTZ, ai_generated, status draft\|published\|closed, created_at
`questions`	id, questionnaire_id FK ON DELETE CASCADE, prompt, options JSONB [{id, text}], correct_option_id TEXT, order_index
`questionnaire_attempts`	id, questionnaire_id, trainee_id, started_at, submitted_at, score NUMERIC, answers JSONB {question_id: option_id}. UNIQUE (questionnaire_id, trainee_id)

Feedback, certificates, notices, taxonomy

Table	Columns
`course_feedback`	id, course_id, trainee_id, rating INT CHECK 1..5, comment, created_at
`certificates`	id, trainee_id, course_id, issued_at, file_path, verification_code TEXT UNIQUE (printed as text and QR)
`notices`	id, title, body, author_id, target_roles user_role[], created_at, expires_at
`notice_reads`	notice_id, user_id, read_at. PK (notice_id, user_id)
`dropdown_groups`	id, group_key UNIQUE. Seed: subject_specialization, skill_tag, course_category, assessment_category
`dropdown_options`	id, group_id, label, sort_order, is_active

Row-level security policies

Three tables carry private text or vectors. Trainers see their own profile, resources are visible to their uploader, admins and enrolled trainees, and chunks inherit access from their parent resource.

packages/db/sql/rls.sql

ALTER TABLE trainer_profiles   ENABLE ROW LEVEL SECURITY;
ALTER TABLE learning_resources ENABLE ROW LEVEL SECURITY;
ALTER TABLE resource_chunks    ENABLE ROW LEVEL SECURITY;

CREATE POLICY trainer_own_profile ON trainer_profiles
  USING (user_id = current_setting('app.current_user_id', true)::uuid
      OR current_setting('app.current_role', true) = 'admin');

CREATE POLICY resource_owner_or_enrolled ON learning_resources
  USING (uploaded_by = current_setting('app.current_user_id', true)::uuid
      OR current_setting('app.current_role', true) = 'admin'
      OR course_id IN (SELECT course_id FROM enrollments
                       WHERE trainee_id = current_setting('app.current_user_id', true)::uuid));

-- resource_chunks has no owner column, so it defers to the parent row.
CREATE POLICY chunk_via_parent_resource ON resource_chunks
  USING (current_setting('app.current_role', true) = 'admin'
      OR resource_id IN (SELECT id FROM learning_resources
            WHERE uploaded_by = current_setting('app.current_user_id', true)::uuid
               OR course_id IN (SELECT course_id FROM enrollments
                                WHERE trainee_id = current_setting('app.current_user_id', true)::uuid)));

packages/db/sql/rls_super_admin.sql

-- Workers run as the app role super_admin and must write chunks, and refresh
-- embeddings on trainer_profiles and courses. One extra rule per table, nothing else changes.
CREATE POLICY chunk_super_admin ON resource_chunks
  USING (current_setting('app.current_role', true) = 'super_admin')
  WITH CHECK (current_setting('app.current_role', true) = 'super_admin');

Missing setting

current_setting(name, true) returns NULL when the setting is absent, so a request that skipped injectRLSContext matches no rows instead of erroring. A safe default.

Policies OR together

Several permissive policies on one table are combined with OR. Add rules; do not rewrite existing ones to widen access.

Vectors are data

Closeness of vectors reveals who resembles whom even when the text is hidden. Isolation has to cover the vector columns, which is why the competency query uses a separate role.

Postgres roles

Role	Used by	Attributes
`app_user`	API request handlers and ai-service workers	LOGIN, no BYPASSRLS, does not own the tables. RLS always applies. Workers pass app.current_role = super_admin.
`matcher`	suggestTrainers only	LOGIN, BYPASSRLS. Own connection string. Never imported by request handlers.
`migrator`	Drizzle migrations	Owns the tables. Not used at runtime.

Vector indexes

packages/db/sql/vector_index.sql

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Build AFTER content is loaded so ivfflat can train its lists on real rows.
CREATE INDEX resource_chunks_embedding_idx
  ON resource_chunks USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- Query time: how many lists to scan. Higher is slower and more accurate.
SET ivfflat.probes = 10;

Only chunks need an index

Trainers and courses number in the tens or hundreds, so an exact scan is fast enough. The chunk table grows with content and is the one that needs ivfflat.

Same operator everywhere

Use cosine distance (the <=> operator) in queries and vector_cosine_ops in the index. Mixing operators makes Postgres ignore the index.

Capacity Connect · Team Syntax Squad · SIH 2026 · PS 26075Code samples are implementation sketches.