## Comic Geek Speak CBDB API

This file documents endpoints that query `api/cbdb.sqlite`. It is intentionally separate from the root `API.md`.

**Master copy:** `api/cbdb.sqlite` on comicgeekspeak.com is the source of truth for the Metron catalog cache. Local MetronSync `series.sqlite` files are working caches only; the Mac app pushes upserts here after each Metron retrieval via the v2 submit endpoints below.

**Durable issue/entity capture:** `endpoint_entity_cache.payload_json` stores the full Metron list/detail JSON so catalog crawls (especially multi-month **issue** pulls) do not need to be repeated. Entity uploads should include `payload_json` whenever available. `detail_fetched = 1` means a validated detail body was stored, not merely that HTTP succeeded.

---

## CBDB

### `GET /api/searchCbdbTitle.php`

Searches the `series_cache` table in `api/cbdb.sqlite` by the `title` field.

**Query parameters**

- **search**: `string` (optional)  
  Case-insensitive substring match on `title` (implemented as `INSTR(LOWER(title), needle)` where `needle` is the lowercased search text). Results are ordered by relevance on a **sort base**: the title with a trailing `(YYYY)` suffix removed (four-digit year in parentheses at the end), so `Batman (2024)` is treated as `Batman` for ranking. Order is: exact match on sort base, then prefix match on sort base, then other matches; ties use shorter sort base and then title alphabetically.  
  If omitted or empty, returns all rows paginated (`ORDER BY title`).

- **offset**: `int` (optional)  
  Zero-based pagination offset (default `0`).

- **limit**: `int` (optional)  
  Page size (default `40`, max `100`).

**Response**

JSON array of matching `series_cache` rows. The endpoint returns a fixed set of columns (all public metadata for the row). **`payload_json` is omitted** — it is not part of this response.

| Field | Type | Notes |
|-------|------|--------|
| **series_key** | string | Primary key |
| **metron_id** | int or null | Metron series id |
| **title** | string | |
| **start_year** | int or null | Series start year from Metron/metadata |
| **end_year** | int or null | Series end year when completed (otherwise null) |
| **description** | string or null | |
| **publisher_id** | int or null | |
| **publisher_name** | string or null | |
| **imprint_name** | string or null | Imprint label when known |
| **series_type_id** | int or null | |
| **series_type** | string or null | e.g. series type label |
| **status** | string or null | |
| **status_id** | int or null | |
| **issue_count** | int or null | |
| **image_url** | string or null | Cover/thumbnail URL when known |
| **cv_id** | int or null | Comic Vine id when present |
| **resource_url** | string or null | |
| **modified_at** | string or null | Source modified timestamp |
| **volume** | int or null | |
| **first_seen_at** | string | |
| **updated_at** | string | |

```json
[
  {
    "series_key": "metron:series:8477",
    "metron_id": 8477,
    "title": "Absolute Batman (2024)",
    "start_year": 2024,
    "end_year": null,
    "description": null,
    "publisher_id": 1,
    "publisher_name": "DC Comics",
    "imprint_name": null,
    "series_type_id": 2,
    "series_type": "Ongoing Series",
    "status": "Current",
    "status_id": 1,
    "issue_count": 12,
    "image_url": "https://example.com/cover.jpg",
    "cv_id": null,
    "resource_url": "https://metron.news/series/8477/",
    "modified_at": "2026-05-01T00:00:00Z",
    "volume": 1,
    "first_seen_at": "2026-05-06T20:12:23Z",
    "updated_at": "2026-05-06T20:12:23Z"
  }
]
```

Example values above are illustrative; real rows follow the same keys.

---

### `GET /api/searchCbdbArc.php`

Searches story arcs in `endpoint_entity_cache` (`endpoint_key = 'arc'`) in `api/cbdb.sqlite`.

**Query parameters**

- **search**: `string` (optional) — case-insensitive substring match on `display_name` (same relevance ordering approach as title search).
- **limit**: `int` (optional) — default `20`, max `100`.
- **offset**: `int` (optional) — default `0`.

**Response**

JSON array:

```json
[
  {
    "arc_key": "metron:arc:177",
    "metron_id": "177",
    "name": "A Court of Owls"
  }
]
```

---

### `GET /api/getCbdbArcIssues.php`

Returns issues for a story arc from `arc_issue_cache` in `cbdb.sqlite` when populated. On a cache miss, fetches once from Metron `GET /api/arc/{id}/issue_list/`, stores rows in `arc_issue_cache`, and returns them. Requires server Metron credentials on the first fetch for each arc — same as CBDB sync: `METRON_USERNAME` / `METRON_PASSWORD` in the PHP environment, or optional `api/cbdb_metron_config.local.php` (see `cbdb_metron_config.example.php`). Legacy aliases `METRON_API_USER` / `METRON_API_PASS` are also accepted.

**Query parameters**

- **arc_key**: `string` (required) — e.g. `metron:arc:177`.

**Response**

JSON array shaped for Full Run `ComicItem` creation (`issue_key`, `title`, `volume`, `publisher_name`, `issue_number`, `start_year`, …).

---

### `POST /api/v2/submitCbdbSeries.php`

Batch upserts rows into `series_cache` in `api/cbdb.sqlite`. Call only via the v2 wrapper (not the parent `submitCbdbSeries.php` directly).

**Authentication**

Same as other v2 write endpoints: allowlisted browser `Origin`, or app token via `X-CGS-App-Token`, `Authorization: Bearer …`, or `?appToken=`. See `api/v2/_auth.php`.

**Request**

`Content-Type: application/json`

| Field | Type | Notes |
|-------|------|--------|
| **records** | array | Required. At most **100** objects per request. |

Each record uses the same field names as the `searchCbdbTitle` response. Per row:

- **series_key** (string, required)
- **title** (string, required)
- All other columns optional (same types as search table above)

```json
{
  "records": [
    {
      "series_key": "metron:series:8477",
      "metron_id": 8477,
      "title": "Absolute Batman (2024)",
      "start_year": 2024,
      "end_year": null,
      "description": null,
      "publisher_id": 1,
      "publisher_name": "DC Comics",
      "imprint_name": null,
      "series_type_id": 2,
      "series_type": "Ongoing Series",
      "status": "Current",
      "status_id": 1,
      "issue_count": 12,
      "image_url": "https://example.com/cover.jpg",
      "cv_id": null,
      "resource_url": "https://metron.news/series/8477/",
      "modified_at": "2026-05-01T00:00:00Z",
      "volume": 1,
      "first_seen_at": "2026-05-06T20:12:23Z",
      "updated_at": "2026-05-06T20:12:23Z"
    }
  ]
}
```

On conflict (`series_key`), existing rows are updated; **`first_seen_at`** is preserved when already set.

**Response** (`200`)

```json
{
  "success": true,
  "upserted": 1,
  "rejected": 0,
  "errors": []
}
```

- **upserted**: count of rows written successfully
- **rejected**: count of invalid rows skipped
- **errors**: `{ "index": 0, "error": "…" }` per rejected row

**Errors**

| Status | When |
|--------|------|
| 400 | Missing/invalid `records`, or more than 100 records |
| 403 | Not called via v2 or invalid auth |
| 405 | Not POST |
| 500 | Database open, migration, or transaction failure (`detail` may include the SQLite message) |

On first write, the API runs `cbdb_sqlite_migrate.php` to add any missing `series_cache` columns (for example `imprint_name`, `start_year`). Deploy `api/cbdb_sqlite_migrate.php` alongside `submitCbdbSeries.php`. The server `cbdb.sqlite` file must be writable by PHP.

---

### `POST /api/v2/submitCbdbBatch.php`

Batch upserts rows into `series_cache`, `endpoint_entity_cache`, and `publisher_cache` in `api/cbdb.sqlite`. Used by Metron mobile apps to mirror catalog data as users browse. Call only via the v2 wrapper.

**Authentication**

Same as other v2 write endpoints. See `api/v2/_auth.php`.

**Request**

`Content-Type: application/json`

| Field | Type | Notes |
|-------|------|--------|
| **series** | array | Optional. Same record shape as `submitCbdbSeries` (`series_key`, `title`, …). |
| **entities** | array | Optional. Rows for `endpoint_entity_cache`. |
| **publishers** | array | Optional. Rows for `publisher_cache`. |

At least one of `series`, `entities`, or `publishers` must be non-empty. **At most 100 records total** per request (combined across all arrays).

**Entity record fields**

| Field | Type | Required | Notes |
|-------|------|----------|--------|
| **endpoint_path** | string | yes | e.g. `/api/issue/123/` |
| **endpoint_key** | string | yes | Resource slug: `issue`, `creator`, `character`, `team`, `arc`, `universe`, `imprint`, … |
| **metron_id** | int | yes | Metron entity id |
| **display_name** | string | no | |
| **description** | string | no | |
| **detail_fetched** | int | no | `0` = list/summary, `1` = detail endpoint (default `0`) |
| **modified_at** | string | no | Metron source `modified` timestamp (ISO8601) |
| **first_seen_at** | string | no | ISO8601; set on insert |
| **updated_at** | string | no | ISO8601 |
| **payload_json** | string or object | no | Full Metron list/detail JSON. Required for lasting capture of issues and other entities. Objects are JSON-encoded server-side. Max ~2.5MB per row. |

**Publisher record fields**

| Field | Type | Required |
|-------|------|----------|
| **metron_id** | int | yes |
| **name**, **description**, **imprint** | string | no |
| **cv_id** | int | no |
| **resource_url**, **modified_at** | string | no |
| **first_seen_at**, **updated_at** | string | no |

```json
{
  "series": [
    {
      "series_key": "metron:series:8477",
      "metron_id": 8477,
      "title": "Absolute Batman (2024)",
      "start_year": 2024,
      "volume": 1
    }
  ],
  "entities": [
    {
      "endpoint_path": "/api/issue/12345/",
      "endpoint_key": "issue",
      "metron_id": 12345,
      "display_name": "Absolute Batman #1",
      "detail_fetched": 1,
      "modified_at": "2026-05-01T00:00:00Z"
    }
  ],
  "publishers": [
    {
      "metron_id": 1,
      "name": "DC Comics"
    }
  ]
}
```

**Duplicate prevention**

- `series_cache`: upsert on `series_key`; `first_seen_at` preserved on update.
- `endpoint_entity_cache`: upsert on `(endpoint_key, metron_id)`; `first_seen_at` preserved; `detail_fetched = 1` is never downgraded to `0`.
- `publisher_cache`: upsert on `metron_id`; `first_seen_at` preserved on update.

**Response** (`200`)

```json
{
  "success": true,
  "upserted": { "series": 1, "entities": 1, "publishers": 1 },
  "rejected": 0,
  "errors": []
}
```

- **errors**: `{ "kind": "entities", "index": 0, "error": "…" }` per rejected row

**Errors**

| Status | When |
|--------|------|
| 400 | All arrays empty, invalid JSON, or more than 100 records total |
| 403 | Not called via v2 or invalid auth |
| 405 | Not POST |
| 500 | Database open, migration, or transaction failure |

On first write, the API runs `cbdb_sqlite_migrate.php` to ensure `series_cache`, `endpoint_entity_cache`, and `publisher_cache` exist. Deploy `api/cbdb_upsert.php` and `api/cbdb_sqlite_migrate.php` alongside `submitCbdbBatch.php`.