job-search/internal/store

Store

A SQLite store for opportunities and their OCDS releases, built on sqlx + goose and mattn/go-sqlite3.

Build and test

The title and general searches use SQLite FTS5, which go-sqlite3 only compiles in with a build tag, so every build and test needs -tags sqlite_fts5. Without it, opening the store fails with no such module: fts5.

go test -tags sqlite_fts5 ./...
go run  -tags sqlite_fts5 ./cmd/jobsearch migrate status

The tag can be made the default for every build with go env -w GOFLAGS=-tags=sqlite_fts5.

Layout

internal/types/                    Go struct definitions for tables (opportunities.go, classifications.go)
internal/store/migrations/         NNNNN_name.sql (Up and Down in one file)
internal/store/migrate.go          OpenDB, NewMigrator (goose), schema version checks
internal/store/migrate_test.go     version checks, stepping up and down, failed-migration rollback
internal/store/queries.go          all SQL, as named constants grouped by table
internal/store/queries_test.go     prepares every query against the migrated schema
internal/store/store.go            Store: logic, using the queries via sqlx
internal/store/search.go           SearchOpportunities: the general search's query
internal/store/store_test.go       behavioural tests
internal/store/search_test.go      behavioural tests of the general search
internal/store/common.go           common for migrate, store and direct access

Schema

Table	Key	Notes
opportunities	uuid (unique)	Updatable. id INTEGER PRIMARY KEY exists only to give the search index a stable rowid (fts5).
opportunity_marks	uuid	uuid references opportunities. The user's own marks (hidden, skipped, interested, bid_submitted, won, lost), each BOOLEAN NOT NULL DEFAULT 0. A CHECK constraint stops won and lost both being set.
opportunity_releases	(uuid, release_id)	Write-once. uuid references opportunities. data is TEXT, so json_extract works on it.
opportunity_release_tags	(uuid, release_id, tag)	Write-once. (uuid, release_id) references opportunity_releases.
classification_codes	id	Shared between opportunities. A CPV code has one row (unique on code where is_cpv), keeping the longest description it has been given: EU Supply cuts descriptions to 64 characters. A code of any other scheme gets a row per distinct content. The table grows over time to include codes not already seen in fetched opportunities.
opportunity_classifications	(opportunity_uuid, classification_id)	Mapping table; replaced as a set when the opportunity is updated.
opportunities_fts	rowid = opportunities.id	FTS5 index on opportunities.title, description and buyer_name, kept in sync by triggers.
classification_codes_fts	rowid = classification_codes.id	FTS5 index on classification_codes.classification (the description), kept in sync by triggers.
opportunity_change_batches	id; hash (unique)	Write-once. One row per save that adds opportunities or gives them new releases, and per change of marks. hash names the batch to clients (random, so unguessable); created_at orders the batches.
opportunity_change_batch_opportunities	(batch_id, opportunity_uuid)	The opportunities in each batch. References both tables, cascading on delete.

Foreign keys are enforced (_foreign_keys=on in the connection string) and cascade on delete from opportunities. The connection also enables WAL mode, a 5s busy timeout, and _txlock=immediate, so a save takes the write lock up front rather than failing halfway through.

When adding a classification scheme flag (e.g. is_unspsc), the migration adding the column must drop and recreate the classification_codes_content unique index with the new column included. Otherwise rows differing only by that flag would be merged. A scheme whose codes have one official description, like CPV, can instead have a partial unique index on code (as classification_codes_cpv does), with its own ON CONFLICT clause in upsertClassificationCodeReturningID.

internal/types: The structs contain db tags to allow sqlx to map table columns to struct members. Any struct members not mapped to a column (including child slices) are referenced by db:"-".

Migrations and Goose

Migrations are embedded in the store package and applied explicitly, by the project's migrate command. store.Open doesn't migrate: it checks the schema version and fails with

• ErrNeedsMigration if migrations are pending (resolved by running migrate);
• ErrSchemaTooNew if a newer build has migrated the database.

store.WithAutoMigrate() makes Open apply pending migrations instead, for tests and throwaway databases. For tooling, store.OpenDB(path) opens the database with the store's connection settings, and store.NewMigrator(db) returns a goose Provider over the embedded migrations.

Each migration runs in its own transaction, so a failed migration is rolled back and leaves the database at the previous version. goose records applied migrations in goose_db_version. Each file has -- +goose Up and -- +goose Down sections. Each section is one StatementBegin/StatementEnd block, which sends it to SQLite in a single call; that's what lets trigger bodies (which contain ;) work. A new migration is created with:

goose -dir internal/store/migrations create add_something sql

Queries and sqlx

All SQL is in queries.go as constants, grouped by table in migration order; store.go holds only the logic. Inserts use sqlx named parameters (:column, matched to the db struct tags in internal/types). sqlx treats any colon followed by a name inside named SQL as a parameter, including inside SQL comments, so comments go on the Go constant, never inside the string.

queries_test.go checks the SQL without needing a test for every code path:

• TestQueriesPrepare prepares every query constant against a freshly migrated database, so a syntax error, an unknown table or unknown column fails the test and names the failing statement.
• TestQueriesAllListed parses queries.go and fails if a constant isn't listed in that test's checks map. Adding a query therefore means adding one line there, and it can't be skipped by accident.
• TestSearchOpportunitiesQueryAllTerms does the same for SearchOpportunities, with a term of every kind, plain and negated.
• TestListOpportunitiesQueryAllFilters builds the ListOpportunities query with every filter set and runs it, checking that the fragments combine and that the arguments line up with the placeholders.
• TestMarksCheckConstraint checks the database itself rejects won and lost set together, for writes that bypass the Go validation.

Adding a table involves adding its migration, putting its queries in a new section of queries.go, listing them in checks, and adding the store methods.

Reads select whole rows (SELECT *, t.*, c.*) which map to a Go struct. sqlx refuses to read a result column that has no matching struct field, so when a migration adds a column, reads fail with missing destination name <column> until it is mapped: the field is added to the type in internal/types if callers should see it, or to the store's row wrapper if it is internal (like opportunities.id in opportunityRow). This means that queries should only return data for a single struct type at a time (nested structs are not supported - subsequent queries must be run to populate the referenced structs).

ListOpportunities is assembled by listOpportunitiesQuery from fragments in queries.go: a base SELECT, one filterOpportunity* clause per filter that is set, and the ordering and paging tail. Each filter fragment takes exactly one argument, except the filterOpportunityMark* fragments, which take none (MarkUnset puts NOT in front of them). Adding a filter means one fragment and one line in listOpportunitiesQuery. The classifications and the marks for a page of opportunities are then loaded with one IN (...) query each.

SQL style

A soft convention for the SQL in queries.go, followed so the queries look alike and read easily. A query that reads better another way can depart from it.

• A statement starts on the line after the opening backtick. A fragment combined into another query (filterOpportunity*) starts straight after it.
• Keywords are uppercase (SELECT, INSERT INTO, ON CONFLICT); table and column names are the lowercase snake_case of the schema.
• A short statement may stay on one line (SELECT t.* FROM opportunities t WHERE t.uuid = ?). A longer one has one clause per line, each at the start of the line: SELECT, FROM, JOIN, WHERE, ORDER BY, LIMIT.
• A column list that doesn't fit on one line goes inside its brackets, indented one tab and filled to about 80 characters per line. The VALUES list wraps at the same columns, so each line of values matches the line of columns above it.
• ON CONFLICT … DO UPDATE SET puts one column = excluded.column per line, indented one tab.
• A subquery (EXISTS (…), IN (…)) is indented one tab inside its brackets, with the closing bracket at the start of a line.
• Tables are aliased consistently: t for opportunities, c for classification_codes, tc for opportunity_classifications, m for opportunity_marks, b for opportunity_change_batches, bo for opportunity_change_batch_opportunities.

Store API

s, err := store.Open(ctx, "jobsearch.db") // fails with ErrNeedsMigration if not migrated
s, err := store.Open(ctx, path, store.WithAutoMigrate()) // tests: migrate on open
s, err := store.Open(ctx, path, store.WithChangeBatchRetention(30*24*time.Hour))
res, err := s.SaveOpportunity(ctx, &opportunity) // SaveResult{OpportunityUpdated, NewReleases}
res, err := s.SaveOpportunities(ctx, opportunities) // all in one transaction; SaveMultipleResult
err = s.SetMarks(ctx, types.OpportunityMarks{UUID: uuid, Interested: true}) // ErrNotFound
t, err := s.GetOpportunity(ctx, uuid) // classifications + marks + releases + tags; ErrNotFound
ts, err := s.ListOpportunities(ctx, store.OpportunityFilter{
	Title:      "cloud migr",
	Hidden:     store.MarkUnset, // MarkAny (default), MarkSet or MarkUnset per mark
	Interested: store.MarkSet,
	Limit:      20,
})
rs, err := s.ListReleases(ctx, uuid) // oldest first, with tags
batch, err := s.LatestChangeBatch(ctx) // hash of the newest change batch, or ""
ts, batch, err = s.ListChangedOpportunities(ctx, batch, time.Time{}) // changed since; ErrUnknownBatch
uuids, err := s.SearchOpportunities(ctx, search.Parse("cloud buyer:leeds"), releasedFrom) // never nil
ok, err := s.ReleaseExists(ctx, uuid, releaseID)

Saving. SaveOpportunity writes everything in one transaction. SaveOpportunities does the same for several opportunities in a single transaction, reporting totals (NewOpportunities, UpdatedOpportunities, NewReleases) and each opportunity's SaveResult:

• Opportunity normalisation. The opportunity is normalised in place. Releases, Marks and tags get the opportunity's UUID and their ReleaseID, times become UTC, LatestReleaseDate becomes the newest release date, and classifications get their database IDs.
• Minimal writing. Releases and tags are write-once. A release already stored (same uuid and release_id) is skipped, and NewReleases counts only the new ones. ReleaseExists lets a fetcher skip converting a release it already has.
• Opportunity row updates. The opportunity row and its classifications are replaced only if the save's newest release date is at least the stored latest_release_date. An older release arriving late is still stored, but never overwrites newer opportunity data; OpportunityUpdated reports which happened.
• Marks are kept. An opportunity's marks are stored when it is first saved; saving it again never changes them, so fetched data (which carries no marks) can't reset what the user has set.
• Change batches. The opportunities a save adds or gives new releases are recorded, in the same transaction, in a change batch named by a random hash; SaveOpportunities reports it as ChangeBatch ("" if nothing changed). A batch's created_at is kept later than every earlier batch's, even if the clock goes back. Recording one deletes the batches older than the store's retention (WithChangeBatchRetention; without it, or with 0 or less, they are kept for ever).

Marks. SetMarks replaces an opportunity's marks, returning ErrNotFound if the opportunity isn't stored. won and lost exclude each other: SetMarks, SaveOpportunity and SaveOpportunities reject marks with both set with types.ErrWonAndLost (from OpportunityMarks.Validate), and the database's CHECK constraint backs this up. A change of marks is recorded in a change batch of its own, so a client catching up sees it.

Listing. ListOpportunities filters by status, classification code, title text, a range on LatestReleaseDate and each mark (MarkSet or MarkUnset; an opportunity with no marks stored has every mark unset), newest first, 50 per page by default. It loads classifications and marks but not releases (which can be large); those are loaded by ListReleases or GetOpportunity.

LatestChangeBatch returns the hash of the newest change batch ("" if there is none). Read before listing, it is the batch to give ListChangedOpportunities, which returns the opportunities in every later batch, once each, unpaged and ordered as ListOpportunities orders them, with the hash of the newest of those batches to give it next time. A non-zero releasedFrom leaves out opportunities whose latest release is before it, as OpportunityFilter.ReleasedFrom does; the hash is the same either way. Batches recorded while it runs are left for next time rather than missed. A hash it doesn't know, such as a deleted batch's, is ErrUnknownBatch.

Title search. The words are matched against the title using FTS5: every word must appear, each also matches as a prefix, case and accents are ignored, and "migrating" finds "migration", etc. Input is sanitised, so FTS5 operators and quotes typed by a user are searched as plain words, never interpreted.

General search. SearchOpportunities runs a search.Query (parsed by internal/search, which documents the syntax) and returns only the matching UUIDs, for the frontend to filter its own list by. It is assembled by searchOpportunitiesQuery from the searchOpportunity* and searchCPV* fragments:

• Words without a prompt search {title description} in opportunities_fts, and must all match. title: and buyer: search the title and buyer_name columns; repeats of one prompt are joined by OR into one clause. Every negated full-text term goes into one NOT IN clause, joined by OR. Each term is one FTS5 phrase built from Term.Words, its last word a prefix, so typed input is never FTS5 syntax.
• buyer_id: matches the whole ID, ignoring case (IN, expanded by sqlx.In).
• CPV terms are checked against each code, inside one EXISTS on the opportunity's CPV codes: a code must match a cpv: term (a code prefix by LIKE, or words in classification_codes_fts) and no !cpv: term. With only !cpv: terms the clause is NOT EXISTS a code matching any of them, so an opportunity with no CPV codes passes it.
• OpportunityFilter.Title searches only the title column of the same index.