Metadata-Version: 2.5
Name: kbforge-okfquery
Version: 0.2.2
Summary: SQL over an OKF v0.2 bundle, via DuckDB
Author-email: Qing <qingye779@gmail.com>
License: MIT
Keywords: duckdb,knowledge-base,okf,open-knowledge-format,sql
Requires-Python: >=3.12
Requires-Dist: duckdb>=1.1
Requires-Dist: pytz>=2024.1
Requires-Dist: pyyaml>=6
Description-Content-Type: text/markdown

# okfquery

SQL over an [OKF v0.2](https://github.com/GoogleCloudPlatform/knowledge-catalog/blob/main/okf/SPEC.md)
bundle, via DuckDB. `okfquery` loads a published bundle into an in-memory DuckDB
connection and hands the connection back — no daemon, no cache, and no
committed artifact except the navigation `index.md` you opt into with
`okfquery index`. It depends on no kbforge code at runtime, so it reads any OKF v0.2
bundle, kbforge-built or not.

## Install and use

```bash
uv pip install kbforge-okfquery   # or: pip install kbforge-okfquery
okfquery query "select count(*) from concepts" --bundle path/to/bundle
okfquery shell --bundle path/to/bundle
```

`query` runs one SQL statement and prints the result (`--format table|json|csv`).
`shell` writes a temporary `.duckdb` file, opens the `duckdb` CLI on it, and
deletes the file when the shell exits.

## `index`: a front door for agents

```bash
okfquery index --bundle path/to/bundle             # writes path/to/bundle/index.md
okfquery index --bundle . --group-by owner         # sections by a facet, not by type
okfquery index --bundle . --check                  # writes nothing; exit 1 if stale
```

An agent reading a bundle cold starts at the root `index.md` (OKF §8, progressive
disclosure): one line per concept, `* [Title](path) - description`, grouped under
one heading per `type` (or per `--group-by` facet; concepts without it go last).
It then opens only the concepts it needs. Without an index, its first step is
listing `concepts/*/overview.md` and opening files to learn what they are.

- **Derived, never edited.** The file starts with a generated-by marker; a
  hand-written `index.md` is refused unless you pass `--force`.
- **Deterministic.** No timestamps, total ordering: rerunning on an unchanged
  bundle rewrites nothing, so CI commits only when a listing changed.
- **No frontmatter**, not even the optional root `okf_version`: a reserved name
  that opens a fence is a concept, and the index would count itself.

**Run it after merge, not per kbforge run.** Each kbforge system publishes on its
own sync branch; a whole-bundle file on several branches would collide. Generate
it on the bundle repo's default branch instead. The sync branches never touch
it, so they inherit the latest one.

Both jobs share three properties, each learned from running them on a real
forge:

- **They wait for the first concept.** A bundle repo is created before anything
  merges, and `okfquery index` rejects a bundle with no `concepts/` on purpose
  (that is how a wrong `--bundle` shows). The jobs skip until one exists.
- **They index the branch as it is now.** A cold start merges chunks in quick
  succession; a job that indexed its own commit and pushed would be rejected
  when a later merge had already moved the branch. They fetch the tip, index
  it, and retry the push on a race, one job at a time.
- **The bot's commit starts nothing:** `GITHUB_TOKEN` pushes do not trigger
  workflows, and GitLab honours `[skip ci]`.

GitHub Actions:

```yaml
on: { push: { branches: [main] } }
permissions: { contents: write }
concurrency: { group: okfquery-index }   # one run at a time; the newest queued run wins
jobs:
  index:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v6
      - run: |
          git config user.name okfquery && git config user.email okfquery@users.noreply.github.com
          # Index the branch as it is NOW, not the commit that started the run: a
          # merge that lands meanwhile would make this push stale. Retry on a race.
          for attempt in 1 2 3; do
            git fetch -q origin "$GITHUB_REF_NAME" && git reset -q --hard FETCH_HEAD
            # Nothing to index until the first concept merges.
            [ -d concepts ] || { echo "no concepts/ yet; nothing to index"; exit 0; }
            uvx --from kbforge-okfquery okfquery index --bundle .
            git add index.md && git diff --cached --quiet && exit 0
            git commit -qm "chore: regenerate index.md"
            git push -q origin "HEAD:$GITHUB_REF_NAME" && exit 0
            echo "push raced another change; retrying on the new tip"
          done
          exit 1
```

GitLab CI. The script is one block scalar on purpose: a list item such as
`- git commit -m "chore: ..."` contains `: `, which YAML reads as a mapping, and
GitLab rejects the whole file. The push uses the job's own token, so no personal
token is stored anywhere; enable it once under **Settings → CI/CD → Job token
permissions → Allow Git push requests to the repository** (GitLab 17.2+), or via
the API with `ci_push_repository_for_job_token_allowed=true`:

```yaml
index:
  image: ghcr.io/astral-sh/uv:python3.12-bookworm-slim
  rules:
    # Default branch only, and only once a concept has merged: before the first
    # merge there is nothing to index, and `okfquery index` rejects a bundle
    # with no concepts/ directory on purpose (it is how a wrong --bundle shows).
    - if: '$CI_COMMIT_BRANCH == $CI_DEFAULT_BRANCH'
      exists: ['concepts/**/*.md']
  resource_group: okfquery-index   # one index job at a time
  script:
    - |
      apt-get update -qq && apt-get install -yqq git
      remote="https://gitlab-ci-token:${CI_JOB_TOKEN}@${CI_SERVER_HOST}/${CI_PROJECT_PATH}.git"
      # Index the branch as it is NOW, not the commit that started the job: a
      # merge that lands meanwhile would make this push stale. Retry on a race.
      for attempt in 1 2 3; do
        git fetch -q "$remote" "$CI_COMMIT_BRANCH" && git reset -q --hard FETCH_HEAD
        uvx --from kbforge-okfquery okfquery index --bundle .
        git add index.md && git diff --cached --quiet && exit 0
        git -c user.name=okfquery -c user.email=okfquery@example.invalid commit -qm "chore: regenerate index.md [skip ci]"
        git push -q "$remote" "HEAD:${CI_COMMIT_BRANCH}" && exit 0
        echo "push raced another change; retrying on the new tip"
      done
      exit 1
```

On an older GitLab, push with a project access token instead: store it as a
masked CI variable and use `oauth2:${YOUR_TOKEN_VARIABLE}` in place of
`gitlab-ci-token:${CI_JOB_TOKEN}`. Both jobs, exactly as printed here, were run
against a real forge: a cold start with no concepts, then two changes seconds
apart, with every run green and one index listing both.

If the default branch is protected against bot pushes, run
`okfquery index --check` in the **default-branch** pipeline instead, and when it
fails regenerate `index.md` in a small follow-up change of its own. Do not put
the check on merge requests: kbforge's sync branches never touch `index.md`, so
every request that adds or retitles a concept would fail it, and fixing that on
the sync branches makes several systems edit one whole-bundle file, the
collision this job exists to avoid.

## Schema

```
$ okfquery schema
CREATE TABLE concepts (
    path          VARCHAR NOT NULL,
    type          VARCHAR,
    title         VARCHAR,
    description   VARCHAR,
    generated_by  VARCHAR,
    -- TIMESTAMPTZ, never TIMESTAMP. Not because a naive column would corrupt
    -- the instant on this package's load path -- `load` binds an aware Python
    -- datetime through executemany, so the instant survives a naive column
    -- fine. What a naive column loses is the type: values come back with no
    -- tzinfo, so every comparison against now() (itself TIMESTAMPTZ) needs a
    -- cast, and the column asserts UTC by convention with nothing recording
    -- that it does. §4.4 law 4 exists to force an *aware* stamp; a column that
    -- cannot hold one throws away exactly the property the law buys.
    generated_at  TIMESTAMPTZ,
    facets        JSON,
    body          VARCHAR
);

CREATE TABLE sources (
    path          VARCHAR NOT NULL,
    -- A real column. kbforge's synthesize.assemble puts the OWNING anchor first
    -- and grounding anchors after it, and that ordering is the only thing
    -- telling owner from ground -- OKF has no field for it. Discarding ordinal
    -- during unnest would destroy the signal.
    ordinal       INTEGER NOT NULL,
    id            VARCHAR,
    resource      VARCHAR,
    content_hash  VARCHAR
);

CREATE TABLE links (
    path          VARCHAR NOT NULL,
    target        VARCHAR NOT NULL
);

CREATE TABLE problems (
    path          VARCHAR NOT NULL,
    kind          VARCHAR NOT NULL,
    detail        VARCHAR NOT NULL
);
```

`path` is the bundle-relative concept path and the join key throughout.
`sources.ordinal = 0` is the owning source for a kbforge-produced bundle (the
convention `synthesize.assemble` writes) — on a foreign bundle it is merely
source order, with no error to tell you that. `facets` is one JSON column, open
by construction, so `facets->>'owner'` reaches whatever a connector attached.
`problems` is additive: a file that failed to parse still gets a `concepts` row
with NULLs for what could not be read, plus one `problems` row per distinct
failure — nothing a broken concept says gets silently dropped from an audit.

## Queries, run against the messy test fixture

All four queries below were run as shown against
`packages/okfquery/tests/fixtures/messy` — 9 `.md` files under `concepts/`, 7 of
them concepts (`concepts/index.md` and `concepts/log.md` are reserved and
fenceless, so they are skipped; `concepts/claims/index.md` is reserved-*named*
but carries frontmatter, so it counts).

**What is built on a given upstream document:**

```bash
$ okfquery query "
SELECT c.path, s.ordinal
FROM concepts c JOIN sources s USING (path)
WHERE s.resource = 'https://wiki/ok';
" --bundle packages/okfquery/tests/fixtures/messy
┌─────────────────────────┬─────────┐
│          path           │ ordinal │
│         varchar         │  int32  │
├─────────────────────────┼─────────┤
│ concepts/ok/overview.md │       0 │
└─────────────────────────┴─────────┘
```

**Staleness by owning system** (`ordinal = 0` picks the owning source, not a
grounding one — see the ordinal note above):

```bash
$ okfquery query "
SELECT split_part(s.id, ':', 1) AS system,
       count(*) AS concepts,
       min(c.generated_at) AS oldest
FROM concepts c JOIN sources s USING (path)
WHERE s.ordinal = 0
GROUP BY 1 ORDER BY oldest;
" --bundle packages/okfquery/tests/fixtures/messy
┌─────────┬──────────┬──────────────────────────┐
│ system  │ concepts │          oldest          │
│ varchar │  int64   │ timestamp with time zone │
├─────────┼──────────┼──────────────────────────┤
│ wiki    │        3 │ 2026-08-23 00:00:00+00   │
└─────────┴──────────┴──────────────────────────┘
```

Only one system shows up on this fixture: most of the messy bundle's files
exist to exercise a `problems` kind and never got far enough to have a
`sources` entry at all, so they don't join into this query — which is correct,
not a bug in the query.

**Orphans — nothing links to them:**

```bash
$ okfquery query "
SELECT c.path FROM concepts c
WHERE NOT EXISTS (SELECT 1 FROM links l WHERE l.target = c.path);
" --bundle packages/okfquery/tests/fixtures/messy
┌───────────────────────────────────┐
│               path                │
│              varchar              │
├───────────────────────────────────┤
│ concepts/badyaml/overview.md      │
│ concepts/claims/index.md          │
│ concepts/incomplete/overview.md   │
│ concepts/nofence/overview.md      │
│ concepts/notmapping/overview.md   │
│ concepts/unterminated/overview.md │
└───────────────────────────────────┘
```

**Everything that failed to parse** (`--format csv` here, since the default
table renderer truncates long `detail` strings):

```bash
$ okfquery query "SELECT path, kind, detail FROM problems ORDER BY path, kind;" \
    --bundle packages/okfquery/tests/fixtures/messy --format csv
path,kind,detail
concepts/badyaml/overview.md,invalid-yaml,frontmatter is not valid YAML: ParserError
concepts/incomplete/overview.md,bad-links,'links' entry 3 is not a string
concepts/incomplete/overview.md,bad-sources,'sources' entry 0 has no 'resource' (§5.1 requires one)
concepts/incomplete/overview.md,bad-timestamp,'generated.at' 'not-a-date' is not an ISO-8601 datetime
concepts/incomplete/overview.md,missing-required,"missing required OKF keys: title, description"
concepts/nofence/overview.md,no-frontmatter,file does not open a '---' frontmatter fence
concepts/notmapping/overview.md,frontmatter-not-mapping,"frontmatter parses to list, not a mapping"
concepts/unterminated/overview.md,unterminated-frontmatter,frontmatter fence is opened but never closed by a closing '---'
```

## The mirror, and why both obvious joins are wrong

`--mirror <path>` attaches a `mirror` view over a kbforge mirror directory
(`read_json_auto('<mirror>/*.json')`) — the same `CanonicalDocument` slots
`mirror.slot_key` writes, `anchor` struct included. It answers a different
question than the bundle alone can: not "what does this concept say" but "does
it still say what its source currently does."

**Both of the joins you'd reach for first are lossy, in different ways:**

- `sources.id = mirror.doc_id` holds for every shipped connector, but
  `synthesize._source_entry` builds `id` from `anchor.system`/`anchor.native_id`
  while `doc_id` is the mirror's own field — nothing enforces the two agree for
  a third-party connector. Where they diverge, this join produces a silent
  false miss, not an error.
- `sources.content_hash = mirror.anchor.content_hash` as an **equi-join** is not
  the sound alternative either. `kbforge.pipeline.run` calls
  `commit(mirror_path, docs)` right after `kbforge_publish`, and publishing only
  opens a review request — kbforge never merges. Between publish and merge the
  mirror already holds the new hash while the bundle still carries the previous
  run's, so an equi-join on hash returns **zero rows for every concept with a
  review request open** — exactly the ones an audit most wants to see.

The correct form: join on `id` with a **`LEFT JOIN`**, not an inner join, then
**compare** hashes instead of joining on them — an inner join silently drops
every source with no mirror row at all, which is exactly the "never
published" case the query needs to report. A match means current; a mismatch
means an update is sitting in review; a source with no mirror row was never
published. Run for real against the messy fixture (`concepts/ok/overview.md`'s
two sources — an owning `wiki:ok` and a grounding `notes:ok` — plus two other
concepts whose sources have no mirror row at all) with a synthetic
two-document mirror covering only `wiki:ok` and `notes:ok`, one hash left
matching and one changed to simulate an update stuck in review:

```bash
$ okfquery query "
SELECT s.path,
       s.id,
       s.content_hash AS bundle_hash,
       m.anchor.content_hash AS mirror_hash,
       CASE WHEN m.doc_id IS NULL THEN 'never published'
            WHEN s.content_hash = m.anchor.content_hash THEN 'current'
            ELSE 'update in review' END AS status
FROM sources s LEFT JOIN mirror m ON s.id = m.doc_id
ORDER BY s.path, s.id;
" --bundle packages/okfquery/tests/fixtures/messy --mirror /path/to/mirror
┌─────────────────────────────────┬─────────────────┬─────────────┬─────────────┬──────────────────┐
│               path              │       id        │ bundle_hash │ mirror_hash │      status      │
│             varchar             │     varchar     │   varchar   │   varchar   │     varchar      │
├─────────────────────────────────┼─────────────────┼─────────────┼─────────────┼──────────────────┤
│ concepts/claims/index.md        │ wiki:claims     │ h-claims    │ NULL        │ never published  │
│ concepts/incomplete/overview.md │ wiki:incomplete │ NULL        │ NULL        │ never published  │
│ concepts/ok/overview.md         │ notes:ok        │ h-ground    │ h-ground-v2 │ update in review │
│ concepts/ok/overview.md         │ wiki:ok         │ h-own       │ h-own       │ current          │
└─────────────────────────────────┴─────────────────┴─────────────┴─────────────┴──────────────────┘
```

That does not fix the `id` gap above: a connector whose anchor disagrees with
its `doc_id` still misses this join silently. There is no workaround for that
here — only the honest statement of it.

## Not built

- **No full-text search out of the box.** `body` is a plain column; `INSTALL
  fts` and an index over it is a documented recipe, not a feature.
- **No remote bundles.** `--bundle` is a local checkout path; reading one over
  HTTP wants a caching story this package deliberately avoids.
- **No MCP server.** `load()` is already small enough to wrap in one `query`
  tool and a schema resource; nothing here does that yet.
