Skip to content
ContractSpec
Start here

Package manual · integrations

·Intelligence, research and applications

integration.osint-store

Postgres, object, and vector storage adapters for OSINT v0.

integrationsintegrationv2.0.5experimentalservernode
@lssm-tech/integration.osint-store
bun add @lssm-tech/integration.osint-store
Package source ↗Manifest ↗

Manifest declarations describe the current workspace; they do not verify registry publication or runtime execution.

Package guide

Storage adapters and migrations for OSINT v0.

  • PostgreSQL is the transactional source of truth across osint_core, osint_projection, and osint_analytics.
  • Every table carries ecosystem, tenant, dataset, and deployment scope. RLS is enabled and forced, and runtime transactions set all scope and purpose claims locally before access.
  • Raw snapshots are content-addressed in an S3-compatible object store with conditional writes, checksums, and encryption-key metadata.
  • The AWS SDK adapter is exercised against pinned MinIO, including fail-closed conditional writes, metadata round trips, idempotency, and lifecycle deletion.
  • Governed exports encode JSON, CSV, or lossless Parquet, store them content-addressed, and persist scoped checksum/evidence receipts.
  • Assertions, evidence, snapshots, audit entries, and outbox events are append-only to the runtime role.
  • pgvector generations are fingerprinted, dimension checked, shadow-built, and explicitly promoted. Generation-specific HNSW indexes are built with the configured distance through a migration/curation connection. Existing Qdrant and Supabase vector adapters remain valid alternatives behind the runtime ports.
  • Hybrid retrieval uses PostgreSQL FTS plus dense reciprocal-rank fusion and returns evidence references.

The schemas are private and explicitly revoked from public, anon, and authenticated. Deployments may expose a separate API surface, but must not expose these tables directly through the Supabase Data API.

Thirty-one ordered migrations cover the evidence spine, durable operations, FORCE RLS, warehouse/rights, bounded research/curation, knowledge/embedding provenance, and exact deep-research provider/model/prompt/policy/replay provenance. Migration 0009 adds an append-only, FORCE-RLS source transition ledger with revision fences and idempotency keys; each transition atomically updates the canonical source manifest and stores its exact resulting representation. The same migration stores request-digest-bound lifecycle claims with bounded leases, monotonic crash recovery attempts, terminal completion receipts, update guards, and database-enforced overlay_write RLS predicates. Canonical source commits transactionally validate the exact claim owner, attempt, request digest, and live database-clock lease. Knowledge documents and append-only query receipts retain source versions, evidence locators, ACL/purpose bindings, suppression state, and destination policy decisions. A policy-verifier port must independently bind the exact purpose, ACL grants, restricted-field status, and decision reference before SQL; FORCE RLS repeats the purpose and ACL checks from transaction-local claims. Embedding generations require provider, model, redaction, extractor, and chunker fingerprints before registration. To replay the executable RLS and pgvector conformance proof against a disposable PostgreSQL database:

sh
OSINT_TEST_DATABASE_URL=postgresql://... bun run test:postgres
bun run test:minio

When host psql is unavailable, run the hermetic PostgreSQL 17/pgvector proof:

sh
bun run test:postgres:docker

The runner refuses to reuse an existing container, pins the pgvector image by digest, applies every migration, bootstraps distinct migrator/runtime/worker/ curation/read-only login roles, and then executes schema-wide FORCE-RLS, public-privilege, empty-scope, restricted-field, base-overlay, queue-fencing, lifecycle, vector, and rights-deletion conformance. This deterministic proof does not replace the required managed non-owner qualification. Its database lives in a uniquely created host temporary directory rather than Docker's shared writable layer; cleanup stops only the exact named container and removes only that generated directory. Existing Docker images, volumes, and containers are never pruned.

Pinned MinIO conformance stores its small disposable object set on a bounded 512 MiB tmpfs. This avoids inheriting host or Docker free-space thresholds and is removed with the exact named container; it never mounts or prunes shared object data.

The PostgreSQL proof also exercises the dedicated osint_rights role against real projection, embedding, and export rows, plus the lifecycle runtime role against grant denial, live-claim reclaim denial, terminal immutability, and receipt isolation. The MinIO command creates and removes only its explicitly named disposable container.

Migration 0010 adds the generic operation work queue used by continuous OSINT workers. Claims use FOR UPDATE SKIP LOCKED, database-clock leases, monotonic attempts, and exact owner/attempt fencing. Completion, retry, dead-letter, and explicit replay are guarded in PostgreSQL and remain scoped by FORCE RLS. Workers must still reauthorize persisted requests before effects; stored grants are replay evidence and never replace a fresh authority decision.

Migration 0024 removes global dataset and grant identifiers. Primary keys and business uniqueness now include ecosystem, tenant, dataset, and deployment so partner ecosystems can safely reuse equivalent local identifiers and grants. Migration 0025 applies the same complete identity to the canonical snapshot, evidence, entity, assertion, relation, identity, curation, embedding, and current-projection graph, including composite foreign keys between every edge. Migration 0026 removes the remaining global operational, warehouse, knowledge, source-lifecycle, work-queue, and embedding-ledger identifiers and replaces their dependent references with complete deployment-scope foreign keys. Migrations 0027–0029 add evidence-bound job availability reconciliation, leased signed-webhook deliveries with append-only attempts and replay receipts, and revision/attempt-fenced dead-letter recovery. Failed scans cannot advance absence counters, and canonical job closure assertions require authorized evidence rows in the same transaction. Migration 0030 adds the immutable acquisition execution receipt to each source snapshot. Evidence reads can therefore resolve the exact plan, selected route, failed attempts, egress binding, normalized request/cost usage, and timestamps without relying on worker memory or logs. Migration 0031 adds shared job-source rate slots for multi-replica workers. Security-definer claim/release functions serialize each complete scope and rate key with a transaction advisory lock, enforce concurrency and reserved start spacing, reclaim expired crash leases, and fence release by claim and owner. The worker role has function execution only and no direct slot-table access.

Migration 0011 upgrades rights deletion work with database-clock leases, monotonic attempts, exact owner/attempt settlement, crash reclaim, explicit replay, overdue-SLO indexing, and a dedicated osint_rights FORCE-RLS policy. PostgresOsintRightsRepositoryV0 persists verified requests, monotonic suppressions, and idempotent fan-out tasks. PostgresOsintDeletionTaskQueueV0 claims deadline-prioritized work and rejects stale or cross-worker settlement.

Migration 0012 adds the embedding effect ledger and append-only transition history. Exact batch and request digests bind provider, model, dimensions, generation, content hashes, and the fresh policy decision, evidence, and expiry. A database trigger keeps those bindings immutable and permits only running to completed or ambiguous transitions. Vectors and their provider-reported usage/cost receipt commit in the same scoped transaction; incomplete or uncertain effects cannot be reclaimed as fresh provider calls. PostgresOsintProjectionSourceV0 reads only current, non-retracted, non-restricted assertions and authorized evidence before current or knowledge projection.

Migrations 0013 and 0014 add the governed subject-rights lifecycle and its grant-aware FORCE-RLS policies. Admission is effect-free. Verification, approval or denial, manual completion, and deletion-worker reconciliation use optimistic revisions and append-only receipts that bind the actor, operation request digest, policy decision, contract fingerprint, reason, and evidence. Only an approved deletion-class request may atomically create suppression and the five destination task families; the final fenced task reconciles the request to completed.

PostgresOsintReadStoreV0 implements every generated OSINT GET operation from canonical, scoped tables. Entity fields are selected deterministically from current assertions only when authorized evidence remains visible; restricted assertions and evidence are filtered by PostgreSQL policy before projection. Graph reads rebuild evidence references through the authorized evidence join, and replay reads omit raw source representations and credential references.

PostgresOsintMetadataMutationV0 executes dataset, grant, program, source registration, subscription, correction, curation, and research-cancellation commands inside one scoped transaction. Each committed effect writes stable audit and outbox identifiers derived from the durable work lease. Provider, research, and export operations fail with an explicit executor requirement when the store is used alone; the managed worker composes their specialized provider and object-store executors. PostgresOsintEvidenceWriterV0 atomically binds deep-research evidence rows to their immutable content-addressed snapshot.

Proposed reviewed development resource authority

PROPOSED_NOT_ADMITTED private development resource authority: optional canonical AuthOS resource JWT/workload profiles; exact <=300-second TTL and registered resource/claim validation; durable scoped OSINT receipts preserve SAME active decisionRef and terminal expiry/revocation. Existing defaults and production RP catalog remain unchanged. Real adoption, security review, TLS/registration, canonical lifecycle/obligation/evidence adapters, migration admission and runtime qualification are required before enablement. Product entitlement is not an OSINT effect grant. No issuer, key, token, grant or database has been created or verified by this source preparation.

Proposed OsintAuthorityLifecycleInstallerV1 writes immutable, scoped authority/delegation/fulfillment records using separately admitted nonowner roles. Readers lock real canonical rows in the receipt transaction. Terminal tombstones, immutable evidence and RLS prevent resurrection or cross-scope reuse. The proposed SQL files are excluded from automatic migration discovery and install no rows, roles or grants. PostgreSQL row locking needs an UPDATE column privilege; dedicated mutation-guard triggers prohibit actual canonical-owner edits through that privilege. Runtime qualification of those roles, guards and the exact development database remains required.

Narrow authority imports

The additive fresh-authority-store, authority-lifecycle-values, authority-lifecycle-reader, and authority-lifecycle-installer subpaths expose the same canonical SQL authority implementations as the root barrel. Use them for custody and lifecycle composition to avoid loading unrelated object/export backends. Existing root exports and authority enforcement remain unchanged.

Reference taxonomy (migration 0035)

Migration 0035_osint_reference_taxonomy.sql (D117-TAXO-01a, D-133b) creates the osint_reference schema with taxonomy_schemes, taxonomy_concepts and taxonomy_batch_receipts, generated from defineDatabaseTable specs (./reference-taxonomy-tables, ./reference-taxonomy-ddl); a drift test keeps the migration file byte-identical to the generator. Rows carry the single owner column owner_id = 'osint' and no brand, product or tenant field; schemes and concepts are publicly readable through osint_reference_reader, batch receipts are not. PostgresReferenceTaxonomyStoreV1 (./reference-taxonomy-store) implements the runtime taxonomy store port. The SQL relies on the osint_runtime, osint_curation and osint_analytics roles from migration 0001.

Public job inventory reads (./public-job-inventory-store)

The standalone public_job_inventory schema (series osint-public-job-inventory, files in public-job-inventory/) is read through the frozen contract public-job-inventory-read.v1 (public-job-inventory-read-contract.ts). Reads are select-only and run in the scoped transaction, so the read-only inventory role can serve them:

  • openReferencesMatching(query) / openRecordsMatching(query): the complete open inventory, most recent first (recency_at = the publish date, else the first sighting, a later publish date clamped to the sighting; then id), with an opaque continuation bound to the query and to the posted-within window of its first page. Role fragments (ordered words with gaps) and keywords are accent- and case-folded and served by the recency index walk (pg_trgm indexes only outside row-level security, see "Reads at scale" below); country, workplace and contract filters keep unknown values unless the caller opts out; group: 'opening' returns one item per opening (same employer and title, gender markers ignored) with its size, using a keyset over the recency index and one indexed probe per candidate row (never a pass over every match).
  • countOpenRecordsMatching(query): exact posting and opening counts (linear in the matches: once per query, not per page).
  • getRecord(id) / getRecords(ids): any state (closed, duplicates), by primary key; openingMembers(keys): the postings of each opening.
  • PostgresPublicJobEnrichmentReaderV1.getEnrichedRecord: by id with derived assertions and their provenance.
  • PostgresPublicJobInventoryOperatorReaderV1.inventoryHealth({ now }): operator-only coverage, board and freshness health. It is a separate class, not a method of the product store, so a host that binds the product store cannot reach it.

The public record's contractType comes from one mapping (publicJobContractKindV1, a TS port of the stored contract_kind function, held equal by a parity test), so openRecords and openRecordsMatching agree.

Read migration (0004_public_job_inventory_read_indexes.sql)

The integrator allocates its number (0004 is free in the series). It needs pg_trgm (a trusted extension, created with schema public), adds five stored generated columns in one ALTER TABLE (one table rewrite under ACCESS EXCLUSIVE), then builds five partial indexes (SHARE locks: reads continue, collector writes wait). It also replaces 0001's postings_open_idx (on last_seen_at) and sets fillfactor = 70, toast_tuple_target = 1024, so the collector's per-run re-sighting update (last_seen_at, fetched_at, last_scan_id, updated_at) touches no indexed column and stays HOT: it writes no trigram entry. PostgreSQL 16+ recomputes a stored generated column only when its base columns are assigned (checked on PostgreSQL 18), so re-sightings never refold descriptions. Every new read selects the generated columns: roll the reads out only after the migration is applied.

Measured on 2026-11-11 with a throwaway native PostgreSQL 17.11 cluster (Apple M-series, shared_buffers=512MB, autovacuum off), 200,000 synthetic postings over 1,000 boards, 4.4 KB average description:

MeasureResult
Migration (rewrite, folding, five indexes)200 s
Size before / after migration453 MB / 1,192 MB (toast 558 MB, document trigram index 280 MB)
Re-sighting of all postings, one transaction per board, rounds 1 / 2 / 381 s / 45 s / 29 s; HOT 48 % / 73 % / 86 %
Same rounds with the first design (no storage options, 0001 index kept), at 20,000 rows17 s / 20 s / 24 s per 20,000; HOT 0 %; document index 61 MB to 189 MB
Browse page of 50, postings or openings (pages 1 and 2)2 to 9 ms
Title role (3 alternatives), opening page4 to 5 ms
Country and contract filter, opening page4 ms
Document role matching 10 % of postings, page of 50373 to 420 ms
Keyword over title and employer name (employer name not indexed)179 to 252 ms
Record page of 50 with descriptions31 ms
Exact counts, broad / document role245 ms / 345 ms

Decision: keep the full folded description in the trigram index (the store returns a superset for the matching domain, so a capped excerpt would drop candidates) and keep it out of the re-sighting write path through HOT updates. Until autovacuum and pruning settle after the rewrite, early re-sightings are partly non-HOT (the trigram index grew from 280 MB to 578 MB over three rounds with autovacuum off). Document-scope role pages are the slowest read (about 0.4 s at 200,000 postings); P2a should prefer title scope where its matching allows it.

Reads at scale under row-level security (parity round 2)

The product roles (public_job_inventory_owner, public_job_inventory_reader) are NOSUPERUSER, NOBYPASSRLS, and the schema forces row-level security. Under row-level security PostgreSQL never turns a non-leakproof qual such as LIKE into an index condition, so the trigram indexes of 0004 serve no product read: they are only used by a role that bypasses row-level security (checked on native PostgreSQL 17: a superuser gets a bitmap scan on postings_open_title_trgm_idx, a NOBYPASSRLS reader a sequential scan for the same statement). The earlier 200,000-row measurements above ran as a superuser. The reads are therefore built so that no feed, detail, search, SEO or sitemap page needs those indexes:

  • Pages without a hard filter, with a posted-within window only, or with a single country without unknowns (equality on the country, served by the per-country recency index) walk a recency index in which every row matches, so they stop at the page size.
  • Pages with any other hard filter (workplace, contract, excludeExpired, employer, provider, several countries or unknown countries kept) walk at most PUBLIC_JOB_INVENTORY_SCAN_ROWS_V1 (20,000) rows of the recency order in one request, whatever the reader's row-level security: a dense filter fills its page early, a rare or absent one returns a short (possibly empty) page with a continuation after the last examined row (round 3; the round-2 code walked the whole recency index for a rare filter).
  • Pages with a text predicate (role, keyword, place) first walk at most PUBLIC_JOB_INVENTORY_PROBE_ROWS_V1 (3,000) rows of the recency index, filtered in order. When the page fills there, it is exact. Bounded page transactions disable sequential scans locally: otherwise a large LIMIT can make PostgreSQL scan and sort the whole inventory instead of walking the recency index. The setting ends with the transaction and changes no result.
  • A rarer predicate then uses the trigram indexes only when row-level security does not apply (row_security_active); under it the page walks at most PUBLIC_JOB_INVENTORY_SCAN_ROWS_V1 (20,000) more rows and may come back short with a continuation positioned after the last examined row. Each request is bounded; following next until it is null is complete and exact (consumers must not treat a short page as the end).
  • By-id reads use the primary key; opening members the opening index; sitemap shards (openShardReferences) a primary-key range per id-prefix shard (no offset).
  • Live counts stay linear in the matches: an exact count, or a capped count of a rare predicate, examines every open row (under row-level security a sequential scan). cap stops a common predicate early. Product requests therefore read maintained counts instead (round 3, ./public-job-inventory-store): refreshOpenCountSnapshotsV1(entries, { now, cap? }) recomputes registered counts off-request as the owner (the collector job runs it after every reconcile for the open total inventory:open plus the entries the composition registers through runPublicJobInventoryV1({ countSnapshots })), and readOpenCountSnapshotsV1(entries, { now, maxAgeSeconds }) returns them by primary key (one lookup per key, at most 256) with computedAt, ageSeconds and stale; a key whose stored query digest differs from the caller's query reads as null, so a changed landing definition never shows another query's count. An older refresh never replaces a newer count.

New in the contract (additive v1): locationTerms (whole-word place terms, served by postings_open_location_trgm_idx outside row-level security and by the bounded walk under it), excludeExpired (stated validThrough before the first page's now), reference validThrough and language (deterministic, publicJobPostingLanguageV1: fr, en, es, de, it, pt, nl or null), the record field validThrough, countOpenRecordsMatching(query, { cap }) with capped, countOpenRecordsMatchingEach, openShardReferences, publicJobVacancyFreshnessV1 (the focused feed's four states, 7-day stale rule) and health totals notSeenWithinWeek, expired and coverage withoutPublishDate. Employer keywords use a semi-join on companies.

Store side of the marketing CareerPublicJobsReadPortV1 (wiring is the marketing lane's): landing().items = openRecordsMatching({ roleFragments: role terms, roleScope: 'title', locationTerms | workplaces: ['remote'], countryCodes: ['FR'], includeUnknownCountry: false, excludeExpired: true, now }) (follow next while a page is short); landing().total and landingCounts = readOpenCountSnapshots of the landing entries (keys such as landing:fr:<role>:<place>, registered with the collector run, see above); posting = getRecord; indexable({ page, pageSize }) = openIndexablePage({ page, pageSize, total }) with total the maintained inventory:open count (PUBLIC_JOB_INVENTORY_OPEN_TOTAL_ENTRY_V1): page n is id range n of a power-of-two plan sized so a range holds on average at most 80% of pageSize, read by a primary-key range scan of at most pageSize + 1 light rows (id, title, lastSeenAt; no description, no join); complete: false says the total is out of date. openShardReferences keeps the full reference projection (it reads a description prefix for the language) for consumers that page shards by continuation.

Migration 0006_public_job_inventory_seo_reads.sql (allocated by the g8 train from the 9xxx_store_inventory_seo_reads.sql placeholder; body unchanged) adds the location_key function, two expression trigram indexes and the open_count_snapshots table (RLS-scoped like every inventory table, written by the owner, read by the product reader through default privileges); no column, no rewrite; SHARE lock during the index builds. Its header carries the CREATE INDEX CONCURRENTLY variant (two single-statement steps outside a transaction). The deploy pre-deploy step now applies the whole series (deploy/job-inventory/README.md).

Long locks at 200,000 rows

  • 0004 rewrites postings once under ACCESS EXCLUSIVE (about 200 s at 200,000 rows, round 1) and builds five indexes under SHARE in the same transaction: collector writes and every reader wait for minutes. At the production size of a few thousand rows it takes seconds, which is why the pre-deploy step applies it now. Low-lock variant if it must ever run at scale (proposal, not shipped): (1) add the five columns as plain nullable columns (instant ADD COLUMN, no rewrite); (2) backfill them in batches of about 5,000 rows with fold_text, title_key, contract_kind, least(published_at, first_seen_at) and the opening key, one transaction per batch; (3) keep them current with a BEFORE INSERT OR UPDATE OF title, description, contract_type, published_at, first_seen_at trigger (or have the collector write them), so re-sightings stay HOT; (4) build each index with CREATE INDEX CONCURRENTLY IF NOT EXISTS, one statement per step outside a transaction (the apply step would need a non-transactional mode for such a file).
  • 0006: about 0.5 s of SHARE lock at 200,000 rows for the two trigram builds (CONCURRENTLY variant in its header); the snapshot table is new and empty.

Native rehearsal: scripts/public-job-inventory-rehearsal.ts (throwaway database on a given server; see its header). It bootstraps the production roles, applies the series as public_job_inventory_owner (NOSUPERUSER, NOBYPASSRLS, no CREATE on schema public: pg_trgm comes from the superuser bootstrap), reads as public_job_inventory_reader under forced row-level security, and fails on any sequential scan of postings in an asserted read or on any request that examines more than probe + 2 x scan + 5,000 = 48,000 postings rows (one statement of a continuation journey: 25,000).

Receipt (round 3): code 9326304379112aa32f07a78e3d36f189dd2883cb (tree 17bf82ba950e3fc191e873cb7481a517ea3c044f, clean), bun.lock sha256 543ff1b9…3024f9, bun 1.4.2, PostgreSQL 17.11 (Homebrew, arm64), throwaway local cluster under the heavy gate, bun scripts/public-job-inventory-rehearsal.ts 200000, 2026-11-12T03:04:07Z to 03:08:18Z, exit 0: 32 asserted reads, no failure, no request over budget. Inventory 195,833 open, 8,333 closed, 4,167 duplicates, 1,106 MB. Full receipt kept with the coordination task report (work/G8-PARITY/store-r3/receipt-200000.json, sha256 d10b4ebd416050dc69fc1fceaeff8a0dee1675a0ada32395699554251f0488f5). Round 2 ran on PostgreSQL 17 only; the round 3 receipt below is PostgreSQL 18.6.

Read (one request unless stated)StatementsPostings rows examinedWall ms
Browse page 50 / page 215119 / 9
Browse FR (single country, per-country index)15110
Browse openings page 5012,72716
Developer roles, document scope, page 50210815
Role title scope, openings, page 50421,04989
Rare word (bounded slice) / absent word4 / 56,214 / 43,000109 / 427
Employer keyword, common / rare2 / 51,538 / 43,00035 / 120
FR permanent remote (hard filters)1519
Contract + workplace with no match, page 50 (bounded slice)240,00065
Same, every continuation to the end (10 requests)20391,666 (at most 20,000 per statement)689 in all
Rare country (one posting, per-country index)112
Provider filter (dense)113210
getRecord / getRecords 20 / openingMembers 2011 / 20 / 9883 / 4 / 12
SEO landing developer in Paris / nurse in Saint-Étienne / accountant remote5 / 4 / 543,000 / 6,160 / 43,000214 / 24 / 317
Capped count, whole inventory, cap 10,000110,001223
Sitemap shard 5/16, 5,000 ids (full references)15,296544
Count snapshots, 21 keys (maintained)105
Indexable page 7, pageSize 5,000 (light rows)13,21419
Not asserted: snapshot refresh, open total + 20 landings (owner, off-request)414,374,9793,005
Not asserted: 20 live landing counts, cap 1,000404,166,6465,879
Not asserted: exact count developer / whole inventory2 / 1208,332 / 208,3334,050 / 1,266
Not asserted: operator inventoryHealth6820,4991,842

Migration timings in the same run: 0001-0005 as the owner on empty tables under 15 ms each; 0006 (then the 9xxx placeholder) at 200,000 rows 485 ms (SHARE), re-apply 1 ms.

Text search under row-level security (parity round 3)

Migration 0011_store_inventory_text_search.sql (allocated by the g8 round-3 train from its 9xxx_ placeholder) makes rare text reads sub-linear for the product roles without BYPASSRLS anywhere (decision request: record it as a D-entry; the design is the lane's proposal):

  • public_job_inventory_search: NOLOGIN, NOINHERIT, NOBYPASSRLS, created by the bootstrap superuser (deploy/job-inventory/sql/enable-public-jobs-search.sql, also in bootstrap-public-jobs-roles.sql); the owner login gets SET, not INHERIT, on it, only so the migration can create the function as its owner. It holds SELECT on postings and companies and nothing else (CREATE on the schema is granted and revoked inside the migration).
  • A SELECT-only policy using (true) for that role alone: a constant policy adds no row-level security qual, so the planner may turn LIKE into a trigram index condition for that role (tested: the reader gets no index condition for the same statement, the definer role gets one).
  • open_text_candidates(title_any, document_any, title_or_company_all, document_all, place_any, since, after_at, after_id, max_rows): SECURITY DEFINER, owned by that role, search_path = pg_catalog, pg_temp, STABLE, EXECUTE for public_job_inventory_reader and public_job_inventory_owner only. It refuses to run unless all four app.osint_* scope settings are set (the same settings the policies of 0001 compare with) and compares them itself, reads open, de-duplicated rows only, accepts only LIKE patterns holding a trigram (three letters or digits in a row; anything else would read every index entry), at most 256/256/16/16/32 patterns, and returns at most max_rows (1 to 100,001) (id, recency key) pairs, most recent first after an optional keyset position. Its statement is planned with the bound values (EXECUTE ... USING).
  • The reads (matchingPage): after the 3,000-row probe, a rare text page under row-level security reads the most recent PUBLIC_JOB_INVENTORY_TEXT_CANDIDATES_V1 (2,000) text matches after its position and selects its rows among them by primary key (an id array), re-applying every predicate under the reader's own row-level security. A full page is exact; a short page ends after the last candidate only when 2,000 candidates came back. Without the function (migration not applied, no EXECUTE: ROW_SECURITY_ACTIVE also reads has_function_privilege) or when no text predicate holds a trigram, the bounded recency walk below serves the page as before.

Migration 0019_country_aware_text_candidates.sql adds a 12-argument open_text_candidates overload that applies country, unknown-country and remote-any-country predicates before the candidate limit. A null country list is exactly the 0011 behavior; an ungranted reader or a rollback that drops only the new overload continues through the original nine-argument function.

  • Counts with a text predicate under row-level security (live landing counts, the owner's snapshot refresh) count the candidates (at most 100,001, or cap + 1) by primary key: exact when fewer came back, capped when more than cap matched among them, else the linear count.
  • postings_open_id_idx (open rows by scope and id): sitemap pages and shards scan id ranges of open rows only, so closed and duplicate rows no longer cost range reads as tombstones accumulate.

Also round 3:

  • roleWholeWords: each role alternative must occur as consecutive whole words of the folded text (the SEO landing match); the substring pattern still feeds the indexes.
  • Overseas departments (D-243): GP, MQ, GF, RE and YT count as France. countryCodes: ['FR'] matches them (unless excludeOverseasDepartments), overseasDepartments keeps only the listed ones, references and records report countryCode: 'FR' with overseasDepartment. The store keeps the ISO code the collector stored (PUBLIC_JOB_FRANCE_OVERSEAS_DEPARTMENTS_V1 maps it to the INSEE number and name).
  • A few known countries without unknowns (France and its departments: six codes, at most 8) walk one per-country recency index each, every arm stopped at the page size, then merged in order: the French feed stays a page-size read, not a 20,000-row slice. Openings over several countries probe 3,000 rows, then walk bounded slices.
  • openIndexablePage rows carry the provider (a host lists only redisplayable boards).

Receipt (round 3, PostgreSQL 18.6): code d783fa226b66cfc22f45ebee309ddae66297d3b2 (tree 25e59434e8d89285ba70364ba8eff7be7aabc474, clean), PostgreSQL 18.6 (Homebrew, arm64, D-243), throwaway local cluster (shared_buffers=512MB, autovacuum off) under the heavy gate, bun 1.4.2, bun scripts/public-job-inventory-rehearsal.ts 200000 with REHEARSAL_PG_LOG (auto_explain nested plans of the definer function), 2026-11-12T10:08:19Z to 10:13:38Z, exit 0: 38 asserted reads, no sequential scan of postings, no request over 48,000 postings rows. 340 nested definer statements: none scans postings; every one reads a trigram index (postings_open_title_trgm_idx, postings_open_document_trgm_idx, postings_open_location_trgm_idx, companies_name_trgm_idx). Inventory 195,833 open, 8,333 closed, 4,167 duplicates, 1,133 MB. The text-search migration applied in 2.7 s at 200,000 rows (SHARE lock for the id index), re-applied in 3 ms. Receipt: work/G8-PARITY/store-r3-round3/receipt-200000-pg18.json in the coordination repository (sha256 227983b25bb8242530ff80899e125fb200fad82e37437340c9d2cae86f4fb3ff).

Read (one request unless stated)StatementsPostings rows examinedWall ms
Browse FR, France with its overseas departments (6 merged walks)15612
Overseas department GP15113
Rare title role + contract, FR (definer candidates)729,485267
Rare description keyword, one match (definer candidates)73,026189
Absent word (probe, then an empty candidate set)63,000371
Rare employer keyword (company-name trigram, definer)73,26079
Rare word journey, 6,250 matches, every continuation380502,307 (at most 25,000 per statement)10,775 in all
SEO port landing developer in Paris: 20 items + exact total93,00017
SEO port landing developer in France: 20 items + exact total (not asserted)524,597150
Not asserted: 225 port landing counts, live (cold jobs index)6751,142,1483,886
Exact count, developer roles (definer candidates, was linear)324,478710
Indexable page 7 (open id index)13,0228

The cold jobs index (225 live landing counts) costs about 4 s once per 10 minutes per web process; registering careerSeoLandingCountEntriesV1() with the collector's count snapshots turns it into one 225-key lookup.

Relationships

THIS PACKAGEintegration.osint-store80 import paths

Arrows point from a package to its direct dependency. Counts are derived from captured workspace manifests.

10 declarations

Declared capabilities

Integration Osint Store

Postgres, object, and vector storage adapters for OSINT v0.

Scripts

  • buildcontractspec-bun-build build
  • build:bundlecontractspec-bun-build transpile
  • build:typescontractspec-bun-build types
  • cleanrimraf dist .turbo
  • lint:checknode ../../../scripts/biome.cjs check .
  • testbun test
  • test:minio./scripts/test-minio-docker.sh
  • test:postgres./scripts/test-postgres.sh
  • test:postgres:docker./scripts/test-postgres-docker.sh
  • prebuildcontractspec-bun-build prebuild
  • typechecktsgo --noEmit