integration.osint-store

@lssm-tech/integration.osint-store

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:

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:

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.

Proposé 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.