Module 2.2 — The database (Supabase)

🧱 Run the migrations

The migrations are files .sql versioned in agent/supabase/migrations/ that describe the entire database. Pushing them to your Supabase project creates, all at once, the 14 tables and enable the extension pgvector — everything through the first migration, the 0001_init.sql. You don't create a table by hand: the schema comes ready in the blueprint.

🟢 New here?

Migration — a numbered SQL file that applies a change to the database. Running them all in order rebuilds the schema from scratch in any project.
Supabase CLI — the Supabase command-line tool. It turns on your local folder to your project in the cloud and pushes the migrations over there.

📊 How to read: from left to right, the SQL files become the actual database. The blue boxes with a glow are where the action happens (the CLI and the result); the cyan ones are the source and destination. The key is that the schema lives in a file, not in your memory.

🎯 Goal: connect your folder to the Supabase project and push the migrations (creates the 14 tables + pgvector).

# 1) na raiz do repo, ligue ao seu projeto (pede a senha do banco)
supabase link --project-ref <seu-project-ref>

# 2) empurre TODAS as migrations de agent/supabase/migrations/
supabase db push

✅ How to verify: the CLI lists the applied migrations (including 0001_init.sql) without errors. In the Supabase dashboard, under Table Editor, the 14 tables appear; in Database → Extensions, vector is enabled.

💡 Where the password comes from

O supabase link uses the SUPABASE_DB_PASSWORD that you saved in the ~/.env (Module 2.1). The <seu-project-ref> is the project identifier—it appears in the URL https://<ref>.supabase.co and in Settings.

Key concepts

Migration

Numbered SQL that rebuilds the schema.

0001_init.sql

Create the 14 tables and enable pgvector.

db push

One command applies everything in the cloud.

Schema in a file

Reproducible in any project.

📋 The 14 tables — overview

Everything you log becomes row in a table. The schema covers the health data families — food, workouts, weight, body composition, caffeine, supplements, vital signs, lab tests, check-ins, goals, and context — plus the semantic memory of the messages. The diagram shows how the sources feed the tables and how the coach reads from them.

📊 How to read: the sources (cyan, on the left) write to the tables (center); the coach (blue) reads the snapshot from those tables and responds. The table messages stands out because it’s the only one that stores vectors (semantic memory — Topic 3).

food_log

Food → macros + flags.

workouts

Logged workouts.

weigh_ins

Weigh-ins (trend).

body_comp

Body composition.

caffeine

Caffeine throughout the day.

supplements

Supplements and timing.

vitals

BP, recovery, HRV, RHR, sleep.

lab_results

Blood markers.

checkins

Daily check-ins.

goals

Your goals.

context

Profile and fixed context.

messages 🧲

Semantic memory (vector).

These 12 data families + supporting tables (such as coach_summary, which store the recovery and its cause) complete the 14 tables that the migration creates.

Key concepts

Everything is a row

Each record becomes a row in a table.

Families

A table by data type.

Snapshot

The coach reads a summary of these tables.

messages

The only one with vectors (memory).

🧲 pgvector — memory

The migration 0001_init.sql turns on the pgvector before creating the tables. It gives the database the ability to store the semantic memory — search past messages by meaning, not by exact word.

🟢 New here? What is pgvector

pgvector is a PostgreSQL extension that adds a new column type: vector. Each message becomes a embedding (a list of numbers that represents the meaning from the text). pgvector compares these vectors and finds what's similar in meaning — this way the coach "remembers" when you talked about sleep, even if the word "sleep" doesn’t appear.

📊 How to read: the text becomes numbers (embeddings), pgvector stores them and then compares them by distance. The closer two vectors are, the more similar their meanings — that’s how memory retrieves relevant past information.

🔌 You don’t connect it manually

You don’t need to run any extra commands: the create extension vector already inside the 0001_init.sql. When you did the db push from Topic 1, pgvector is already enabled. Here, you just need to understand why it exists.

Key concepts

pgvector

Extension that stores vectors in Postgres.

Embedding

Numbers that represent the meaning.

Similarity

Finds similar items by distance.

Already enabled

The 0001_init.sql does this for you.

🪣 The photo storage bucket

The tables store numbers and text; the photos (food, lab results, body) go into a bucket of storage. HealthOS uses a bucket called health-assets, created with a single command — and it is private.

🟢 New here?

Bucket — is a file storage “folder” in Supabase, separate from the tables. Private means no one accesses a file through the public URL; only the server, with the right key, generates a temporary link to read it.

🎯 Goal: create the private bucket health-assets where the photos will live.

# um comando cria o bucket PRIVADO de fotos
python3 agent/scripts/db.py mkbucket health-assets

✅ How to verify: in the Supabase dashboard, at Storage, the bucket health-assets appears marked as Private (not public). Running it again is safe — it won’t create duplicates.

✓ Private bucket (the right choice)

✓Health photos aren't exposed through a public URL.
✓The server generates temporary links when needed.
✓It’s the pattern that the mkbucket already applies.

✗ Public bucket (don’t do this)

✗Anyone with the URL could see the photo of your test results.
✗Sensitive health data exposed to the internet.
✗You can’t “unpublish” what has already leaked.

Key concepts

Bucket

File storage, outside the tables.

health-assets

The HealthOS photo bucket.

Private

No public URL; only the server can read it.

mkbucket

One command creates everything correctly.

🔒 RLS — lock down the database

The schema connects the RLS (Row-Level Security) in every table and doesn’t create any policies. The effect is radical and simple: the public key (anon) doesn’t read nothing; only the server, with the key service-role, is crossed. It’s the default lock in HealthOS.

🟢 New here? Two keys, two worlds

RLS — a Postgres feature that decides, row by row, who can read/write. Without an allowing policy, the default is to deny.
service-role — the key for server. It bypasses RLS (bypasses it), so the agent reads and writes normally. It lives only in ~/.env, never in the browser.
anon — the key public, safe to expose. With RLS enabled and zero policies, it doesn’t read anything.

📊 How to read: both keys arrive at the same door (RLS). The service-role (green) goes through it and reaches the tables; the anon (red, dashed) hits the X and returns. That’s why the public key is safe to expose.

✓ service-role (server)

✓Reads and writes to all tables.
✓Bypasses RLS (overrides it).
✓Stays only in the ~/.env, on the server.

✗ anon (public)

✗Doesn’t read a single row (RLS without a policy).
✗Doesn’t write anything.
✓That’s why it’s holds to expose if needed.

Key concepts

RLS

Connected to everything, but without policies = denied.

service-role

The server key bypasses RLS.

anon

The public key can’t read anything.

Default lock

Private by design, with no setup required.

🔌 Test the connection

Before moving on to the agent, prove that the server can communicate with the database. The db.py is HealthOS’s database utility; a select simple confirms that the keys of the ~/.env are correct and RLS lets the server through.

🎯 Goal: confirm that the server connects and reads a real table (goals).

# lê as linhas da tabela goals usando a chave service-role do ~/.env
python3 agent/scripts/db.py select goals

✅ How to verify: it prints your goals (or an empty list [] if you haven’t changed the seed yet). No connection/authentication error = keys and RLS are OK. Change goals by <outra-tabela> to inspect any of the 14.

🧪 If there's an error

•Authentication failure? A SUPABASE_SERVICE_ROLE_KEY or the SUPABASE_URL in the ~/.env is wrong.
•Table doesn’t exist? O db push from Topic 1 didn't run to completion—try again.
•List empty? This is a successful connection — all that’s missing is the seed (Topic 7).

✅ Self-check (optional): why does the db.py select goals can read, if RLS is enabled?

Key concepts

db.py

The HealthOS database utility.

select goals

Minimal connection test.

Read = OK

Keys and RLS working.

[] is OK too

Connection is set; only the seed is missing.

🌱 Seed and sensitive data

The migration 0002_seed_example.sql plant one sample seed (generic, with no real data) just so you can see the format. The final step is to replace this example with your goals and context — and keep your real seed and your CLAUDE.md outside git.

🟢 New here?

Seed — initial data that "seeds" the database. The example is generic; your real data goes in the tables goals e context.
Outside Git — the repository is the blueprint clean. Your actual values stay only on your machine/database; they go into the .gitignore and are never committed.

✓ Can be versioned

✓The schema migrations (0001_init.sql).
✓O 0002_seed_example.sql generic.
✓O CLAUDE.md model (blank).

✗ Never version-control

✗Your seed real (goals + context filled in).
✗Your CLAUDE.md with a real profile.
✗O ~/.env and your photos.

⚠️ Health data is sensitive

A real seed committed to the repository is a permanent leak—the Git history keeps everything. Before any git add, confirm that the real goals/context, CLAUDE.md filled in and ~/.env are in the .gitignore. The public repository should remain the scrubbed blueprint, with no personal information.

Key concepts

Seed

Initial database data.

Example → yours

Replace 0002 with your goals+context.

Outside Git

Real seed data and CLAUDE.md are not version-controlled.

Clean blueprint

The public repo contains no personal data.

📋 Module summary

✓

Migrations in one command — supabase db push creates the 14 tables and enables pgvector (0001_init.sql).

✓

14 tables, one per family — food, workouts, weight, vitals, lab tests, goals, context… + semantic memory in messages.

✓

pgvector is the memory — stores embeddings and searches by meaning; it’s already enabled by the migration.

✓

Private bucket + RLS — photos in locked-down health-assets; RLS enabled with no policies: only the service role gets through.

✓

Connection tested and your seed — db.py select goals confirm everything; the real seed and CLAUDE.md stay out of git.

Next module:

2.3 — The agent and bot: bring the coach to life on Telegram, connect the agent to the database you just set up, and respond to the first message.

← Back to Step by step Next module →