Refreshing data

Kospex stores what it found at the time it looked. Nothing re-derives itself, so after upgrading kospex, upgrading panopticas, or pulling new commits, the database keeps reporting the old answer until you re-run the right command.

Two things catch people out:

There is no kospex sync command. Syncing a repo happens through kgit (kgit clone, kgit sync) or kospex sync-directory. A bare kospex sync exists only as commented-out code; restoring it for on-disk repos without kgit/auth is tracked in issue #123.
Syncing a repo does not refresh its dependencies. dependency_data is populated by a separate pass — krunner osi — and nothing in the sync path writes to it.

What refreshes what

Data	Table	Refreshed by
Commits, authors, developer stats	`commits`, `developer_stats`	`kgit clone`, `kgit sync`, `kospex sync-directory`
File metadata + panopticas tags	`file_metadata`	the above, plus `kospex sync-metadata` and `krunner file-metadata`
Dependencies	`dependency_data`	`krunner osi`, `kospex deps`, `kospex sca`
Branch counts	`observations`	`krunner branches -save`
Repo sizes	`observations`	`krunner repo-size -save`

The three commit-sync commands all call the same internal sync_repo, which ingests commits and then refreshes that repo’s file_metadata — so a repo sync does update panopticas tags, subject to the rebuild guard described below.

krunner osi does not re-scan files. It reads the file list out of file_metadata, so a dependency file kospex has never tagged is invisible to it, however many times you re-run it. That gives the ordering rule: file metadata first, dependencies second.

Syncing repos

kgit clone REPO_URL              # clone into KOSPEX_CODE and sync (-sync is on by default)
kgit sync https://host/org/repo  # sync a single repo from a URL
kgit sync --org https://host/org # sync every repo in an org
kgit pull DIRECTORY              # git pull the clones kospex already knows about
kospex sync-directory DIR        # sync every repo found under DIR

kgit clone syncs an existing clone rather than failing, so it doubles as a refresh for a single repo.

If a repo is already recorded against a different path that still exists, the sync is refused rather than silently repointing it — pass -force to override. A recorded path that no longer exists is treated as a move and repoints with a warning.

Refreshing file metadata

File metadata is where panopticas tags live — the tech_type column, encoded as |tag1|tag2|, which everything else filters on. To refresh it without a full repo sync:

krunner file-metadata                 # every repo in scope
krunner file-metadata github.com~org  # one server, org or repo_id
kospex sync-metadata -repo PATH       # one repo on disk
kospex sync-metadata -directory DIR   # a directory of repos

You usually don’t need `-force`

Kospex.file_metadata() consults needs_metadata_rebuild(), which rebuilds a repo when any of these is true:

no prior sync was recorded for it
HEAD has moved since the last sync
the installed panopticas version differs from the recorded one
the installed scc version differs from the recorded one

Tool versions are compared as opaque strings — kospex only cares whether the tag changed, never how the versions order — so pre-release suffixes and unusual version schemes need no special handling.

The practical consequence: after upgrading panopticas, a plain krunner file-metadata re-tags everything. The recorded last_panopticas_version no longer matches the installed one, so every repo is marked for rebuild. No -force, no re-clone, no need to touch HEAD.

Reach for -force only when you suspect the stored metadata is wrong for a reason kospex cannot detect — a partial run, a hand-edited database, or a panopticas change that shipped under an unchanged version number.

Note: krunner file-metadata -force deletes the existing rows for the current hash before rescanning. It does not pass force down to Kospex.file_metadata(), so it is a row-clearing hammer rather than a way to override the rebuild guard — which has usually already decided to rebuild.

Refreshing dependencies

krunner osi -all              # every repo — regenerates OSI-all.csv
krunner osi <repo_id>         # one repo, org or server
kospex deps -repo PATH        # find/assess dependency files in one repo
kospex deps -file FILE        # assess a single manifest
kospex sca FILE               # one manifest, with advisory enrichment
kospex sca -malware FILE      # ...and maliciouspackages.com lookup (needs API_MAT)

krunner osi walks the dependency files recorded in file_metadata, parses each with the matching parser, enriches via deps.dev, and writes dependency_data.

Because it reads that file list from the database rather than the disk, a manifest that panopticas doesn’t recognise — or that was added since the last metadata refresh — will not appear. If a manifest you expect is missing from /dependencies/, refresh file metadata first and re-run.

kospex sca takes a file path argument, not a repo. Its -repo option is declared but unimplemented — passing it prints NOT implemented and exits 1. -save defaults to on, so sca writes to the database unless told otherwise.

Order of operations

After upgrading panopticas, or when a new manifest type becomes supported:

kospex upgrade-db -apply    # only if a release added a migration
krunner file-metadata       # re-tag files; new manifest types become visible
krunner osi -all            # re-parse dependencies with the current parser

After pulling new commits into your clones:

kgit pull [SEE OPTIONS]     # update the clones, syncs and updates file metadata
krunner osi -all            # dependencies, if manifests may have changed

Why the numbers can get worse after a refresh

A refresh reports what is actually there now, which is not always an improvement on what was reported before. Two mechanisms cause a legitimate-looking drop:

Parser fixes surface things that were previously dropped. If a parser was silently discarding declarations it couldn’t handle, fixing it makes those dependencies appear — typically as rows with no resolvable version, which then fail freshness checks. Dependency counts rise and health figures fall. Nothing regressed; the earlier figure was flattering because part of the input was missing.

Tag changes invalidate saved queries rather than erroring. tech_type is matched with LIKE '%|tag|%'. If panopticas renames a tag, a query filtering on the old name returns zero rows instead of failing, so anything built on the old vocabulary quietly reports nothing. Check release notes for tag renames, and re-check saved queries and dashboards after a panopticas upgrade.

Both are reasons to refresh deliberately rather than on a schedule nobody watches — and to refresh before presenting numbers to anyone.

Normalising repo_id case

repo_id is now lowercased. Git providers treat an owner and repository name as case-insensitive for uniqueness but case-preserving for display, so github.com/Kospex/Kospex and github.com/kospex/kospex are one repository — but kospex used to mint two ids for them, splitting the commit history and double-counting the org.

There is no migration for this. It is a one-off data change with no schema component, and whether two ids that collapse to one should be merged depends on which row you want to keep — a judgement a migration cannot make. The SQL below is deliberately run by hand.

Nothing self-heals: a repo keeps its old mixed-case id until you either normalise it or re-sync it.

Do you have any?

SELECT _repo_id FROM repos WHERE _repo_id <> lower(_repo_id);

Empty result means there is nothing to do — likely if every repo came from an all-lowercase URL.

Check for collisions first

This is the part that needs a decision. Two ids that differ only by case collapse into one when lowercased, and the rows have to be merged rather than updated:

SELECT lower(_repo_id) AS target, group_concat(_repo_id, ' + ') AS sources
FROM (SELECT DISTINCT _repo_id FROM repos)
GROUP BY lower(_repo_id) HAVING COUNT(*) > 1;

If this returns rows, stop. Each pair is the same repository recorded twice. Decide which to keep — usually the one whose repos.file_path points at a clone that still exists — then clear the other with kreaper delete-repo -repo_id <the-other-one> -yes before running the update below. Re-sync afterwards.

If it returns nothing, the update is safe — every id maps to a distinct lowercase id, so no rows merge.

Normalise

Back up first; there is no undo.

cp ~/kospex/kospex.db ~/kospex/kospex.db.bak

Thirteen tables carry _repo_id, and repos, commits, commit_files and file_metadata also carry _git_owner / _git_repo, which must be lowercased with it. They are not derived from the id — they are written from the parsed clone URL, and the org-scoped queries bind _git_owner from a split org_key. If the two disagree on case, every org lookup that starts from a repo_id silently returns no rows.

BEGIN;

UPDATE branch_history    SET _repo_id = lower(_repo_id);
UPDATE branches          SET _repo_id = lower(_repo_id);
UPDATE commit_files      SET _repo_id = lower(_repo_id);
UPDATE commit_metadata   SET _repo_id = lower(_repo_id);
UPDATE commits           SET _repo_id = lower(_repo_id);
UPDATE dependency_data   SET _repo_id = lower(_repo_id);
UPDATE developer_stats   SET _repo_id = lower(_repo_id);
UPDATE file_metadata     SET _repo_id = lower(_repo_id);
UPDATE kospex_groups     SET _repo_id = lower(_repo_id);
UPDATE krunner           SET _repo_id = lower(_repo_id);
UPDATE observations      SET _repo_id = lower(_repo_id);
UPDATE repo_hotspots     SET _repo_id = lower(_repo_id);
UPDATE repos             SET _repo_id = lower(_repo_id);

UPDATE repos         SET _git_owner = lower(_git_owner), _git_repo = lower(_git_repo);
UPDATE commits       SET _git_owner = lower(_git_owner), _git_repo = lower(_git_repo);
UPDATE commit_files  SET _git_owner = lower(_git_owner), _git_repo = lower(_git_repo);
UPDATE file_metadata SET _git_owner = lower(_git_owner), _git_repo = lower(_git_repo);

COMMIT;

Run it with sqlite3 ~/kospex/kospex.db < normalise.sql, or paste it into an interactive sqlite3 session. The first query above should then return nothing.

What is not affected

Existing clone directories keep their original casing, and nothing needs moving. repos.file_path records the real path, and kgit pull, kgit clone and sync all read it, so an existing clone keeps working wherever it is.

New clones are lowercased, because kgit clone derives the directory from the same parsed URL as the repo_id. So a disk that predates this change ends up mixed — ~/code/github.com/Textualize/rich beside ~/code/github.com/textualize/rich for a repo cloned later. That is cosmetic: each is recorded in repos.file_path and found through it. If you would rather have one convention, move the directory and update the row:

UPDATE repos SET file_path = ? WHERE _repo_id = ?;

On a case-insensitive filesystem (macOS by default) the two names are the same directory and there is nothing to do.

Display casing is lost. _git_owner held the provider’s canonical casing (Textualize, NousResearch), and after this it does not. That is a deliberate trade: the columns have to agree with the id, and the id has to be case-insensitive for the identity to be correct. If canonical display names are wanted later they should come from the provider API, which is authoritative, rather than from whatever casing a clone URL happened to carry.

Upgrading to 0.1.0: re-syncing after the ingest fixes

Two separate things changed in 0.1.0 and both need attention on an existing database. This section covers re-syncing for the commit-ingest fixes. Normalising repo_id case above covers the one-off SQL for repo ids — do that first, since re-syncing a repo whose id is still mixed-case records it twice.

0.1.0 changed how commits are read from git. The fixes apply to newly-synced commits only, and commit sync is incremental — it walks from the last recorded commit — so a routine sync never revisits existing rows and nothing self-heals.

Three things differ between data ingested before and after the upgrade:

merge commits are now identifiable (commits.parents is populated)
content a merge introduced that exists in no parent is now recorded in commit_files
non-ASCII file paths are stored unquoted

Until a repo is re-synced it keeps the old values, so an estate part-way through will hold a mix. Reported numbers stay self-consistent either way — the queries read whichever evidence is present — but merge-aware figures are only complete for re-synced repos.

Which repos are stale

SELECT _repo_id, COUNT(*) AS commits
FROM commits WHERE parents IS NULL
GROUP BY _repo_id ORDER BY commits DESC;

Every repo synced before 0.1.0 appears here, so on first upgrade this is your whole estate. It stays useful afterwards as a general staleness check: a repo listed here predates the current ingest.

Re-syncing a repo

A re-sync has to start from an empty slate, because sync_repo derives its window from the newest commit already recorded. Clearing the repo removes its repos row along with everything else, which resets the sync provenance so the next sync walks the full history.

# See what would be removed - read-only, deletes nothing
kreaper delete-repo -repo_id github.com~owner~repo -dry-run

# Clear every table carrying this _repo_id, including the repos row
kreaper delete-repo -repo_id github.com~owner~repo -yes

# Re-sync from the local clone (offline; finds the repo at or below the path)
kospex sync-directory ~/code/github.com/owner/repo

kospex sync-directory walks a directory and syncs every git repo at or below it, so pointing it at one repo syncs just that one, and pointing it at ~/code re-syncs everything.

To pull and re-sync in one step for clones kospex already knows about, use kgit pull instead.

One thing a re-sync will not fix

Non-ASCII paths stored in their old quoted form — "caf\303\251.py" — do not get replaced. file_path is part of the primary key of commit_files, so unquoting produces a different key: a re-sync adds the correct row and leaves the quoted one behind as a duplicate.

Check for them first:

SELECT _repo_id, COUNT(*) FROM commit_files
WHERE file_path LIKE '"%' GROUP BY _repo_id;

If any exist, delete them — before or after the re-sync, but do not skip it:

DELETE FROM commit_files WHERE file_path LIKE '"%';

These rows are orphans that nothing joins to; the correct rows are recreated by the re-sync.

Back up first

kreaper deletes. Take a copy of ~/kospex/kospex.db before starting, and run kospex upgrade-db -apply if the database is behind — writes against a behind-schema database can record incomplete data.

Checking what a repo was last built from

Provenance is recorded per repo, so you can tell whether a refresh is needed without guessing:

SELECT _repo_id, last_sync_hash, last_panopticas_version, last_scc_version
FROM repos;

A last_panopticas_version behind the installed one means that repo’s file_metadata will rebuild on its next metadata run. These columns are point-in-time and keep no history — they record only the most recent successful sync.

Requires migration 0003. If the columns are absent, run kospex upgrade-db -apply.