Database schema and deployment

What the database is for

The database stores the research workspace around the simulator. It answers practical questions such as: who is signed in, which projects can they see, what may they edit, which experiment version was run and which replay belongs to it.

We chose Supabase because we needed authentication and a proper relational database without spending the sprint building an identity service from scratch. Postgres is a good fit because projects, members, experiments and replays have clear relationships, while experiment configuration itself is flexible JSON. Supabase also gives us Row Level Security, which is important because hiding a button in JavaScript is not real permission control.

The slightly less formal explanation is that it gives us the boring but important parts in one place. We can focus on the research workflow while Supabase handles accounts, sessions and the database API. The trade-off is that the SQL schema, browser code and deployed Supabase project must agree exactly. When they drift, users see confusing errors such as a missing function even though sign-in still works.

Two database modes

Mode	Used for	Storage
Hosted mode	The public researcher platform	Supabase Auth and Postgres
Local mode	Development and isolated demonstrations	Python server and SQLite

The modes implement similar concepts but do not share IDs or automatically copy data. Creating an account locally does not create a Supabase account, and an existing SQLite project does not appear on the hosted site.

Hosted relationship map

auth.users
    |
    | 1 to 1
    v
profiles
    |
    +--------------------+
    |                    |
    v                    v
paradigms 1------many paradigm_memberships
    |
    | 1 to many
    v
experiments
    |
    | 1 to many
    v
replays

audit_log records important changes across project entities

The application uses the word project in the interface and the historical table name paradigms in the database. They refer to the same research-project concept.

Hosted enums

Type	Values	Purpose
app_role	researcher, instructor	Account-wide role. Project ownership is not stored here.
paradigm_access_level	viewer, editor	Basic access inside one project.
experiment_status	draft, published, archived	Experiment lifecycle.

A user who creates a project becomes the lead of that project through paradigms.lead_user_id. This is why every account can remain an ordinary researcher at account level while leading one project and observing another.

Full hosted table schema

The following schema combines the Supabase structure supplied by the team with the newest project-scope migration. Nullability and defaults should be verified against the deployed project using the read-only checks later on this page.

profiles

Column	Type	Meaning and constraint
id	uuid	Primary key and reference to auth.users.id; deleted with the Auth user.
email	text	Account email copied from Supabase Auth. Nullable in the supplied schema.
role	app_role	Account-wide researcher or instructor.
active	boolean	Whether the account may use project functions.
created_at	timestamptz	Creation timestamp.
updated_at	timestamptz	Last profile update timestamp.

The signup trigger creates or repairs this row after a Supabase Auth account is created. ensure_my_profile() provides a recovery path when an Auth user exists but the profile row is missing.

paradigms

Column	Type	Meaning and constraint
id	uuid	Primary key, normally generated with gen_random_uuid().
name	text	Research-project name. Project creation limits it to 1 to 100 characters.
description	text	Optional researcher-facing description.
lead_user_id	uuid	Reference to the profile that currently leads the project.
customization_scopes	text[]	Modules enabled for the project.
archived	boolean	Hides a retired project from active work.
created_by	uuid	Profile that created the project.
created_at	timestamptz	Creation timestamp.
updated_at	timestamptz	Last project update timestamp.

Allowed project scopes are all, arena, light, odour, rodent, simulation, sound and treatment. all must appear alone when it is used.

paradigm_memberships

Column	Type	Meaning and constraint
paradigm_id	uuid	Project reference and first part of the composite primary key.
user_id	uuid	Profile reference and second part of the composite primary key.
access_level	paradigm_access_level	viewer for an observer or editor for a researcher or lead.
customization_scopes	text[]	Modules this specific member may edit. Added by the latest migration.
created_at	timestamptz	Membership creation timestamp.
created_by	uuid	Profile that assigned the membership.

The effective edit scope is the intersection of the project's enabled modules and the member's assigned modules. Observers receive an empty scope array. A project lead receives the project's full scope.

experiments

Column	Type	Meaning and constraint
id	uuid	Primary key.
paradigm_id	uuid	Parent project; deleting the project deletes its experiments.
name	text	Researcher-facing experiment name.
description	text	Optional description.
version	integer	Published experiment version.
revision	integer	Optimistic-concurrency counter used to prevent silent overwrites.
status	experiment_status	draft, published or archived.
configuration	jsonb	Complete versioned experiment document used by Godot.
created_by	uuid	Profile that created the experiment.
updated_by	uuid	Profile that last changed it.
created_at	timestamptz	Creation timestamp.
updated_at	timestamptz	Last update timestamp.
published_at	timestamptz	Nullable timestamp set when a version is published.

The configuration JSON contains metadata, simulation settings, arena, materials, regions, walls, barriers, stimuli, protocol, treatment, controller, demonstration-agent settings and UI preferences. It is JSON because experiment structures need to evolve together and be passed to Godot as one validated document. IDs, ownership, lifecycle and relationships remain normal relational columns so they can be indexed and protected.

replays

Column	Type	Meaning and constraint
id	uuid	Primary key.
experiment_id	uuid	Parent experiment; deleting the experiment deletes its replays.
created_by	uuid	Profile that saved the replay.
replay_data or replay	jsonb	Recorded run metadata, steps and events. See the schema-drift warning below.
random_seed	bigint	Optional indexed run seed in the supplied live schema.
duration_seconds	double precision	Optional simulated duration.
storage_path	text	Optional path if large replay data is moved to object storage later.
created_at	timestamptz	Replay creation timestamp.

The current browser code inserts and reads a column named replay. The schema inventory supplied by the team shows replay_data. The deployed table and application must use the same name before replay saving is called verified.

audit_log

Column	Type	Meaning and constraint
id	bigint identity	Primary key.
actor_user_id	uuid	Profile responsible for the action, when known.
paradigm_id	uuid	Related project, when applicable.
action	text	Stable action name.
entity_type	text	Type of changed entity.
entity_id	uuid	ID of the changed entity.
old_value	jsonb	Optional state before the change.
new_value	jsonb	Optional state after the change.
created_at	timestamptz	Event timestamp.

Audit records are evidence of application changes, not a replacement for immutable scientific run data. Sensitive values should be minimised rather than copying complete personal records into every audit row.

Functions and RPCs

Function	Caller	Purpose
ensure_my_profile()	Authenticated account	Creates or repairs the caller's profiles row.
is_instructor()	Policies and functions	Checks active account-wide instructor status.
is_paradigm_lead(uuid)	Policies and functions	Checks project leadership or instructor authority.
is_paradigm_member(uuid)	Policies and functions	Checks whether the caller may see a project.
can_edit_paradigm(uuid)	Policies and functions	Checks lead or editor access.
create_paradigm_project(text,text,text[],jsonb)	Active authenticated account	Creates a project, lead membership and baseline experiment in one transaction.
assign_paradigm_role(uuid,text,text,text[])	Project lead	Adds or updates a member, project role and module scopes.
list_paradigm_people(uuid)	Project member	Returns project members, roles and current scopes.
remove_paradigm_member(uuid,uuid)	Project lead	Removes a member but does not allow the current lead to remove themselves.
configuration_allowed(uuid,jsonb,jsonb)	Update trigger and server functions	Compares old and new configuration against effective module scopes.
clone_experiment(uuid,jsonb)	Editor or lead	Creates a new draft experiment from an allowed configuration.

The four-argument assign_paradigm_role is the current contract. If Supabase reports that it cannot find assign_paradigm_role(target_email,target_paradigm_id,target_role,target_scopes), the latest migration is missing or the PostgREST schema cache has not refreshed.

Triggers

Trigger	Table	Purpose
Profile-on-signup trigger	auth.users	Creates a profiles record for every new Auth account.
enforce_experiment_scope	experiments	Rejects changes to configuration sections outside the caller's modules.

Only one profile-on-signup trigger should be active. The migration history contains older and newer names, so a consolidated migration should remove obsolete trigger versions explicitly.

Row Level Security

RLS is enabled on all public application tables.

Table	Read rule	Write rule
profiles	A user reads their own profile; an instructor may read profiles needed for administration.	Normal users do not directly change account roles.
paradigms	Project members and permitted instructors may read.	Creation is performed by the project RPC; a lead or instructor may update the project.
paradigm_memberships	A member reads their own membership; a project lead can list the team through the protected function.	Assignment and removal happen through lead-controlled RPCs.
experiments	Project members may read.	Editors and leads may create or update; configuration scope is checked separately.
replays	Members of the replay's project may read.	Only a caller who can edit the parent project may save a replay as themselves.
audit_log	Instructor-only in the supplied policy set.	Written by trusted functions or triggers, not ordinary browser inserts.

RLS must be tested by calling the database as each role. A hidden Add member button or disabled treatment input proves only that the UI changed; it does not prove the database rejects a hand-written request.

Indexes

The schema includes indexes for common relationships:

• memberships by user_id;
• experiments by paradigm_id;
• replays by experiment_id; and
• audit records by entity where supported by the baseline schema.

Primary keys and foreign-key targets also have their normal Postgres indexes. If batch results grow large, replay metadata, seed and created-time queries should be measured before adding further indexes.

Current migration order

The application repository contains:

1. 202608230001_rodent_core.sql
2. 202608230002_access_management.sql
3. 202608230003_paradigm_roles.sql
4. 202609120001_research_projects.sql

The final migration lets any active account create a project, adds per-member customization_scopes, replaces the old three-argument assignment function with the four-argument function and tightens replay creation.

Important schema drift to resolve

The files show the history of the database, but they are not yet a proven clean bootstrap for an empty Supabase project.

Difference	Why it matters
The supplied live schema uses enum types, while the first repository migration creates some role and status columns as checked text.	Later migrations cast values to the enums and can fail if the enums do not exist.
The supplied live schema has replay_data, while the browser and first migration use replay.	Replay insert and list requests fail if the names differ.
The supplied live schema contains columns such as descriptions, timestamps and audit fields that are absent from the first migration.	A fresh database created only from the first file may not match browser queries.
Older functions use the private schema and public replacements were added later.	Running migrations out of order can cause schema "private" does not exist or missing-function errors.
The role RPC changed from three arguments to four.	PostgREST resolves functions by signature, so the UI cannot call the old version.

Before calling the deployment reproducible, create one consolidated baseline migration from the verified live schema, test it against an empty Supabase project and then keep only forward migrations after that baseline. Do not fix drift by disabling RLS or placing a service-role key in the browser.

Hosted deployment

The public researcher portal is deployed at rodent-tau.vercel.app. Vercel stores these production variables:

SUPABASE_URL=https://<project-reference>.supabase.co
SUPABASE_PUBLISHABLE_KEY=<publishable browser key>

The publishable key is designed for client use, but it is safe only in combination with correct grants and RLS. Never put service_role, database passwords or private management tokens into web/app.js, Vercel client output, documentation or Git.

Supabase Auth must also include the production website in its allowed site and redirect URLs so email confirmation and password recovery return to the correct deployment.

Deployment procedure

1. Create or select the intended Supabase project.
2. Take a backup before changing an existing schema.
3. Inspect the migration history and current columns, enums, functions and policies.
4. Resolve the documented drift or apply a tested consolidated baseline to a new project.
5. Apply later migrations in filename order.
6. Refresh or wait for the PostgREST schema cache after changing RPC signatures.
7. Set SUPABASE_URL and SUPABASE_PUBLISHABLE_KEY in Vercel Production, Preview and Development environments as appropriate.
8. Configure Supabase Auth site and recovery redirect URLs.
9. Deploy the website.
10. Test sign-up, sign-in, password reset, project creation, team assignment, scoped experiment editing, replay saving and forbidden requests.
11. Record the migration versions, deploy date, application commit and results in the private deployment log.

Read-only deployment checks

Run these in the Supabase SQL editor to describe the deployed project without exposing row contents.

Columns

select table_name, ordinal_position, column_name, data_type,
       udt_name, is_nullable, column_default
from information_schema.columns
where table_schema = 'public'
  and table_name in (
    'profiles', 'paradigms', 'paradigm_memberships',
    'experiments', 'replays', 'audit_log'
  )
order by table_name, ordinal_position;

Enum values

select type.typname as enum_name, enum.enumlabel as value
from pg_type type
join pg_enum enum on enum.enumtypid = type.oid
join pg_namespace namespace on namespace.oid = type.typnamespace
where namespace.nspname = 'public'
order by type.typname, enum.enumsortorder;

Functions

select routine_name, data_type
from information_schema.routines
where routine_schema = 'public'
order by routine_name;

RLS and policies

select schemaname, tablename, rowsecurity
from pg_tables
where schemaname = 'public'
  and tablename in (
    'profiles', 'paradigms', 'paradigm_memberships',
    'experiments', 'replays', 'audit_log'
  )
order by tablename;

select tablename, policyname, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, policyname;

Do not paste query results containing real email addresses or experiment data into the public documentation.

Local SQLite schema

The local server creates its schema from backend/schema.sql.

Table	Main columns
users	Integer ID, email, password hash, role, active flag and creation time.
paradigms	Integer ID, name, description, lead ID, archive flag and JSON project scopes.
paradigm_memberships	Composite project/user key, access level and JSON member scopes.
experiments	Project ID, name, version, status, revision, configuration JSON, authors and timestamps.
sessions	Hashed session token, hashed CSRF token, user, expiry and creation time.
password_resets	Hashed reset token, user, expiry and used flag.
audit_log	Actor, action, entity, JSON details and timestamp.
replays	Experiment, creator, replay JSON and timestamp.

Passwords and raw session tokens are not stored directly. The local mode is useful for development and automated tests, but it is not the production identity database and does not send real password-recovery email.

Research data considerations

Experiment configurations and replays may become research records. A production research deployment should define retention, backup, deletion, participant-data rules and who may export results. Large model files and long trajectories should eventually move to controlled object storage, with only identifiers, checksums and metadata in Postgres.

Reproducibility requires more than keeping the final CSV. Preserve the experiment JSON, seed, application commit, model version, adapter version, event record and any exclusions used in the analysis.

---

AI Attribution: This documentation was prepared with OpenAI Codex assistance from the team's supplied Supabase schema, SQL migrations and current application queries. The original SQL remains attributed to its actual author. No database migration was changed by this documentation update.