# Database

The Postgres tables behind Docup, how drafts and live pages are stored, and how migrations work.

Postgres is the source of truth for everything in Docup. The schema is defined with Drizzle, and the client helper builds a database client per request over Hyperdrive.

## Entity overview

```text
user ──< member >── organization ──< project ──1 docs_site
                        │               ├──< doc_page
                        │               ├──< sync_run
                        │               └──< site_asset
                        └──< github_installation
```

## Application tables

### `project`

One repository and one docs site. Columns include the owning `organization_id`, `name`, `source` (`public_url` or `github_app`), `github_repo_id`, `full_name`, `default_branch`, `is_private`, `installation_id`, `status` and `last_synced_sha`. A repository is unique per workspace.

`status` moves `creating` → `generating` (the first run) → `draft_ready`. `last_synced_sha` is the commit the docs describe, and updates compare from it.

### `docs_site`

The hosted site of a project, exactly one per project. It holds the unique `subdomain`, `visibility`, `published_at`, and all site settings:

- AI steering: `instructions`, `ignore_paths`
- Search: `seo_title`, `seo_description`, `allow_indexing`, `allow_ai_crawlers`, `llms_enabled`, `robots_extra`, `og_mode`
- Brand: `accent_color`, `heading_font`, `body_font`, `default_theme`, `show_site_name`
- Features: `ask_enabled`
- Custom domains: `custom_domain` (the verified host), `custom_domain_id` (Cloudflare SaaS hostname ID), `custom_domain_active_at`, `custom_domain_added_at`, `custom_domain_checked_at`

`published_at` is null until the first publish.

### `doc_page`

One page. The key columns come in pairs, draft and live:

| Draft | Live |
| --- | --- |
| `markdown` | `published_markdown` |
| `path` | `published_path` |
| `position` | `published_position` |
| | `published_at` (null while not live) |

Other columns: `title`, `summary`, `source_paths`, `status` (`pending`, `written`, `failed`), `error`, `seo_title`, `seo_description`, `noindex`, `ai_markdown` (the last text the AI wrote), `edited`, `suggested_markdown`, `suggested_sha`, `sources_removed` and `updated_at`. `(project_id, path)` is unique.

Publishing runs as a transaction that copies `markdown` to `published_markdown` and sets `published_at` for pages with text that need publishing. It also copies `path` to `published_path` and `position` to `published_position` for all live pages in the project so their live paths never clash. See [Drafts and live pages](/concepts/drafts-and-live).

Unpublishing a site clears `published_markdown`, `published_path`, `published_position`, and `published_at` across all pages of the project, and resets the site's `published_at` timestamp. Unpublishing a single page clears its live columns; if no live pages remain, the site is unpublished as well.

### `sync_run`

One run of the pipeline. `kind` is `sync`, `page_retry` or `update`. It records `status`, `step`, `from_sha` and `to_sha`, `files_read` and `files_skipped`, the `changes` of an update as JSON, and an `error`. `page_path` names the page of a retry. It also tracks AI usage with `tokens_in` and `tokens_out` to measure the cost of the run.

### `site_asset`

 A row per uploaded image (`og`, `favicon`, `logo`, `logo_dark`) with its content type and upload time. The bytes are in R2 under a site folder. The upload time is used for cache busting.

### `github_installation`

A GitHub App installation a workspace connected: `installation_id`, `account_login` and `account_type`. Saved only after the signed-in user proved to GitHub that they can see it.

## Authentication tables

Generated by Better Auth: `user`, `session`, `account`, `verification`, `organization`, `member` and `invitation`. Regenerate with `pnpm db:auth-schema` when the plugin list changes. Keep it in sync with the CLI auth configuration file.

## Migrations

Migrations are SQL files, generated by drizzle-kit.

```bash
pnpm db:generate   # create a migration from schema changes
pnpm db:migrate    # apply migrations
pnpm db:studio     # browse the data
```

The scripts load `.dev.vars` first.

## Connections

A Worker cannot reuse a connection pool across requests, so a small `pg` pool (maximum 4 connections with a 1000ms idle timeout) is built per request and shared. React's `cache()` shares this pool while rendering a page (such as layouts and components), while `cacheForRequest` shares it across the wider request lifecycle so API routes and server actions reuse the same pool instead of creating their own. Hyperdrive keeps the real connections warm near the database. Tasks on Trigger.dev connect directly through their own pool.