# Listings from D1

> Build from the approved rows in D1 instead of Markdown: the seed, the read token, the four build variables, the log line to check, and the fallback trap.

You are reading one page of the ZeroDirs documentation. ZeroDirs is a paid Astro + Cloudflare Workers starter for directory sites: every page is rendered to static HTML at build time, the site ships no client JavaScript outside `/search/`, and one file — `site.config.ts` — carries roughly 80% of the customisation.

Two things to hold on to before you act on anything below:

- `site.config.ts` is validated by a zod schema with ten cross-field rules. After any edit to it, run `pnpm check:config`; every problem is reported as `site.config.ts › <path>: <message>` and the whole file is checked at once.
- The repository ships its own `AGENTS.md` with twenty hard rules, and a machine-checked test suite behind them. If you are working inside a ZeroDirs project, read that file first — it overrides anything general you infer from this page.

Source: https://zerodirs.com/docs/deploy/listings-from-d1/

---

In D1 mode the build reads every approved row from D1 over the REST API instead of
`src/content/listings/`. What changes is the loader and four build variables; `site.config.ts`
and the deploy on push stay as they are. You need it the moment a submission approved in
`/admin/` should appear on the site: approvals write rows, and only a build reads them.

> **Free edition**
>
> The free edition's `src/loaders/index.ts` knows files only; nothing on this page applies to it.

## Before you start

| Check | Where | You should see |
| --- | --- | --- |
| `pnpm cf:setup` has run: the database exists, the migrations are applied | `wrangler.jsonc` | a `database_id` under `d1_databases` — [Go live](https://zerodirs.com/docs/deploy/go-live/) |
| Deploys happen on push | Workers Builds, or the shipped GitHub Actions workflow | a build and a deploy after a push — [Deploy on git push](https://zerodirs.com/docs/deploy/git-push/) |

1. Put approved rows in the database.

   ```sh title="Terminal"
   pnpm db:seed:remote --with-images --with-favicons
   ```

   It reads `src/content/listings/` (`--dataset <name>` for a sample dataset), inserts every file
   as an approved row — `status = 'approved'`, `source = 'seed'`, submitter `seed@localhost` —
   turns `popularity` into a `clicks` row, and runs the SQL through
   `wrangler d1 execute DB --remote` under your wrangler login. Then the logos, each pass skipping
   rows that already have one: `--with-images` stores each listing's own `https://` logo in R2;
   `--with-favicons` fetches the icon each remaining site publishes; `--with-images` last uploads
   the `/logos/` files for the rest. A re-run deletes only the rows it seeded before; submissions
   and imports stay.

   ```text
   db:seed — src/content/listings → D1 (remote)
     stored N dataset logo URL(s) in R2 in 40s
     fetched N favicon(s) into R2 in 90s (no icon: unreachable=N)
     uploaded N /logos/ file(s) to R2
     N listing(s), N click row(s) in N statement(s); largest N bytes (limit 90000)
   seeded N listing(s) into the remote database
   ```

   `--dry-run` prints the SQL and touches nothing. Rows also arrive by approving a submission in
   [`/admin/`](https://zerodirs.com/docs/submissions/admin/), or from `pnpm listings:import my-data.csv --to d1 --remote`.

2. Create the read token.

   Cloudflare dashboard → **My Profile → API Tokens → Create Token → Create Custom Token**.
   Permissions: one row, **Account → D1 → Read**; Account Resources: the account that owns the
   database; nothing else. **Continue to summary → Create Token**, and copy the value now — it is
   shown once. The summary names one permission, D1 Read.

   It is a build secret: not the token Workers Builds deploys with, not the deploy token in GitHub
   Actions, and never `CLOUDFLARE_API_TOKEN`, the name wrangler reads.

3. Set the four variables where the build runs.

   | Variable | Value |
   | --- | --- |
   | `LISTINGS_SOURCE` | `d1` |
   | `CLOUDFLARE_ACCOUNT_ID` | the account id `pnpm exec wrangler whoami` prints |
   | `D1_DATABASE_ID` | `database_id` in `wrangler.jsonc` |
   | `D1_READ_TOKEN` | the token from step 2, stored as a **secret** |

   Workers Builds: Workers & Pages → your Worker → **Settings → Build → Variables and secrets**.
   GitHub Actions: the shipped `deploy.yml` already sets `LISTINGS_SOURCE: d1` in its build step
   and reads the other three from repository secrets. All four are
   [build-time variables](https://zerodirs.com/docs/deploy/environment/#build-time-variables); they never reach the Worker.
   The list shows four names, `D1_READ_TOKEN` marked as a secret.

4. Try it locally first.

   ```sh title=".env"
   LISTINGS_SOURCE=d1
   CLOUDFLARE_ACCOUNT_ID=<from wrangler whoami>
   D1_DATABASE_ID=<database_id from wrangler.jsonc>
   D1_READ_TOKEN=<the token from step 2>
   ```

   `.env` is gitignored. Then:

   ```sh title="Terminal"
   pnpm build
   ```

   ```text
   [zerodirs-d1] D1: N approved listings (featured N, 30-day clicks for N, N REST requests)
   ```

   `pnpm preview`: the home page shows the database's listings, including anything approved in
   `/admin/` that has no Markdown file.

5. Push, then read the deploy's build log.

   Workers & Pages → your Worker → Deployments → that build's log, or the build step of the
   Actions run. The same `[zerodirs-d1] D1: …` line must be there; `[zerodirs-files] listings: …`
   means the variables did not reach that build — see [The trap](#the-trap).

6. Turn on publishing on approval.

   ```ts title="site.config.ts"
   providers: { rebuild: 'deploy-hook', rebuildAuto: true },
   ```

   [Publishing on approval](https://zerodirs.com/docs/submissions/publishing/) has the hook URL and the GitHub
   alternative. Until then `/admin/` reports `providers.rebuild is "none": approved listings stay
   off the site until someone publishes.`; afterwards that line is gone.

## Verify

| | Where | You should see |
| --- | --- | --- |
| The loader | the build log, local or in the deploy | `[zerodirs-d1] D1: N approved listings (…)`; a `[zerodirs-files] listings: …` line means files mode |
| The count | the home page hero, `N tools across M categories`, against the database | the same `N` as the operator's query: `pnpm exec wrangler d1 execute DB --remote --command "SELECT count(*) FROM listings WHERE status='approved' AND slug IS NOT NULL"` |
| A seeded logo | a seeded listing's page, the logo's `src` | `https://<your media host>/logos/<id>.<ext>` — the R2 key on `media.baseUrl`, not a `/logos/` path |

## The trap

The loader picks its source from `LISTINGS_SOURCE` and the three credentials:

| `LISTINGS_SOURCE` | The three credentials | The build |
| --- | --- | --- |
| unset | all three set | reads D1, silently |
| unset | any missing | prints the warning below and reads Markdown; it **succeeds** |
| `d1` | any missing | fails at once with the error below |
| `d1` | all three set | reads D1 |
| `files` | ignored | reads Markdown, no warning |
| anything else | ignored | fails: `LISTINGS_SOURCE="x" is invalid: expected "d1" or "files"` |

```text
listings: LISTINGS_SOURCE is unset and D1_READ_TOKEN is missing — using files (src/content/listings). Set LISTINGS_SOURCE=files to silence this warning.
```

```text
listings loader: LISTINGS_SOURCE=d1 but D1_READ_TOKEN is not set. The D1 REST loader needs CLOUDFLARE_ACCOUNT_ID, D1_DATABASE_ID, D1_READ_TOKEN at build time — put them in .env locally, or in Workers Builds → Settings → Build variables (D1_READ_TOKEN as a secret). D1_READ_TOKEN is an account API token with the "D1 Read" permission scoped to this one database; it is not the Workers Builds deploy token and it is not CLOUDFLARE_API_TOKEN. Set LISTINGS_SOURCE=files to build from src/content/listings instead.
```

The second row is the dangerous one: the build succeeds, the deploy goes live, and it was built
from `src/content/listings/` — every listing that exists only in the database, which is every
approval since the seed, is gone until the next build with all four variables. The usual cause is
a variable set in `.env` but not in Workers Builds. Always set `LISTINGS_SOURCE=d1` explicitly
where the build runs: a missing credential then fails the build, and a failed build leaves the
previous deploy live. A wrong token or database id fails the same way, with a line beginning
`D1 REST` and the status code.

## Logos and the move

A Markdown listing's `logo` is an `https://` URL or a `/logos/` path in `public/`. A row has
`logo_key` instead — an object in the R2 bucket bound as `MEDIA`, keyed `logos/<id>.<ext>` — which
the loader serves as `media.baseUrl` + `/` + the key. The seed's two flags fill it; without the
bucket they stop: `wrangler.jsonc has no r2_buckets entry bound as MEDIA; --with-images needs
one`. A row without a key renders the initial-letter tile, as a file without `logo` does.

Set `media.baseUrl` to the bucket's public host before the first D1 build: the default
`https://media.example.com` turns every logo into a broken image, and nothing warns. A logo on
that host is converted at build time, so `pnpm check:config` counts one more file per logo once
`LISTINGS_SOURCE=d1`. [Logos and media](https://zerodirs.com/docs/configure/logos-and-media/) covers the bucket's domain
and submitted logos.

## Configuration

| Field | What it changes | Reference |
| --- | --- | --- |
| `media.baseUrl` | the host every `logo_key` is served from; the only `site.config.ts` field this page needs | [`media`](https://zerodirs.com/docs/configure/reference/#media) |
| `LISTINGS_SOURCE`, `CLOUDFLARE_ACCOUNT_ID`, `D1_DATABASE_ID`, `D1_READ_TOKEN` | the loader and its credentials; build variables, not config | [Build-time variables](https://zerodirs.com/docs/deploy/environment/#build-time-variables) |
| `providers.rebuild`, `providers.rebuildAuto` | what an approval triggers | [`providers`](https://zerodirs.com/docs/configure/reference/#providers) |

## Files

- src/
  - loaders/
    - index.ts picks the loader from `LISTINGS_SOURCE` and the credentials
    - d1.ts the REST loader: query, paging, retries, the missing-variable error
    - normalize.ts one row into the shape a Markdown file produces
- scripts/
  - seed.ts `pnpm db:seed:local` and `pnpm db:seed:remote`, the three logo passes
- drizzle/ the migrations `pnpm cf:setup` and `pnpm db:migrate:remote` apply
- .env.example the four build variables, with comments
- wrangler.jsonc `database_id`, and the `MEDIA` bucket the seed uploads to

## Related

- [Environment and secrets](https://zerodirs.com/docs/deploy/environment/) — Every variable and secret, where it lives, and what breaks without it.
- [Deploy on git push](https://zerodirs.com/docs/deploy/git-push/) — Workers Builds or GitHub Actions — where the four variables go.
- [Publishing on approval](https://zerodirs.com/docs/submissions/publishing/) — The Deploy Hook that turns an approval into a build.
- [Logos and media](https://zerodirs.com/docs/configure/logos-and-media/) — The bucket, its domain, and what a listing without a logo shows.