> ## Documentation Index
> Fetch the complete documentation index at: https://blue-chip.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

# Database & Migrations

The schema lives in `supabase/migrations/` as plain SQL. `apps/api` only maps
existing tables (`app/models/db.py`) and views (`app/services/discovery_repo.py`);
nothing in the app creates or alters the schema.

## Tables

| Table | Purpose |
| - | - |
| `app.profiles` | Per-user profile (display name, avatar); email stays in `auth.users` |
| `app.organizations` | Companies, projects, labs, funds, schools, clubs (with cached `scout_grade`, `scouting_report`, `search_tsv`, `summary_embedding`) |
| `app.persons` | Founders, operators, advisors, students (with `headline`, `tags`, `bio_embedding` vector, `search_tsv`) |
| `app.scouting_reports` | Editorial scouting evaluations for organizations (grade 0-100, status, markdown evaluation, sub-grade breakdown) |
| `app.affiliations` | Dated education / employment / advisory / investor links |
| `app.entity_identities` | External handles, one per `(platform, external_id)` |
| `app.entity_signals` | Weighted discrete signals: hackathon, accelerator, press, fundraise, ... |
| `app.signals_timeseries` | Append-only numeric metric observations (headcount, stars, ...) |
| `app.source_records` | Raw scraper output for audit / replay |

Users themselves live in Supabase Auth (`auth.users`). User-owned rows reference
`auth.users.id` with `on delete cascade`, so deleting the auth user removes their data.

Account deletion goes through the Supabase **Auth Admin API**
(`DELETE /auth/v1/admin/users/{id}`) using the service role key. That removes the
`auth.users` row and cascades to `app.*` rows via the foreign key, and it keeps the
API's Postgres role from needing write access to `auth.users`.

## Discovery layer

The read endpoints never query the normalized graph directly. The `discovery`
schema exposes one denormalized view, `discovery.companies`, that folds the graph
joins, `merged_into_id` filtering, and 180-day signal aggregation into a single table.

The API exposes it as typed, read-only resources:

| Endpoint | Purpose |
| - | - |
| `GET /api/v1/organizations` | List companies/projects (`q`, `tag`, `stage`, `org_type`, `min_momentum`, `sort` = `momentum` \| `name`, `cursor`, `limit`) |
| `GET /api/v1/organizations/{id}` | One organization: founders, recent signals, momentum score |

These are plain `SELECT`s against the view — typed Pydantic models all the way
through — not a raw-SQL query endpoint. The list endpoint paginates with a
**keyset cursor** (`next_cursor`), not `LIMIT`/`OFFSET`, so deep pages stay
index-friendly as the graph grows.

## Growth velocity timeseries

Metric observations are appended to `app.signals_timeseries` in Postgres. The API computes rolling growth velocity directly from this table:

| Endpoint | Purpose |
| - | - |
| `GET /api/v1/metrics/velocity` | Top-growing organizations over a rolling window (`metric_type`, `days`, `min_current`, `min_growth_pct`, `limit`) |

Supported `metric_type` values: `github_stars`, `github_forks`, `commit_count`,
`headcount`, `open_jobs_count`, `website_traffic_rank`, `twitter_followers`,
`devpost_likes`. The query aggregates the earliest and latest values per organization in the window to compute absolute growth and growth percentage.

## Access model

* `app` is **backend-only**: `anon`/`authenticated` are revoked, RLS is enabled
  with no policies (default deny), and only `service_role` has grants.
* `apps/api` connects as the `postgres` role, which bypasses RLS. User-owned
  queries (`app.profiles`) filter by the verified user id; discovery queries read
  from the `discovery.companies` view.

## Applying migrations

```bash theme={null}
supabase link --project-ref <project-ref>
supabase db push
```

Or paste the file into the Supabase SQL editor. It is idempotent and safe to re-run.

## Connecting

Point `POSTGRES_*` at the Supabase **session pooler** (port `5432`), not the
transaction pooler (`6543`), which breaks prepared statements. `SUPABASE_URL` must
be set for token verification. See [Configuration](https://blue-chip.mintlify.site/docs/configuration.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.