code/cl8y-research
2
0
Fork 0
Code Issues 10 Pull requests Projects Releases Packages Wiki Activity

Postgres/pgvector as the product store for ingest, search, and citations #7

New issue
Open
opened 2026-08-31 05:33:23 +00:00 by PlasticDigits · 6 comments
PlasticDigits commented 2026-08-31 05:33:23 +00:00 (Migrated from gitlab.com)
Copy link

Summary

Postgres + pgvector is the product store: week / ingest / search persist and query embeddings when DATABASE_URL is set. PostgresStore implements the same store API as memory (async-adapted). Local tests run against Docker pgvector without folklore --ignored. Agents retrieve ingested sources with citations for blog grounding.

Gap: gaps/GAP_1788152435.md §4.3, §7.1.

Depends on live collect / Telegram / Tavily for what is ingested; this issue is the persistence and search path.

Current codebase

  • docker-compose.yml: pgvector/pgvector:pg16, port 5433, db cl8y_research.
  • migrations/001_init.sql and duplicated DDL in store::postgres::migrate.
  • PostgresStore: upsert_async, search_async (ORDER BY embedding <=> $1). Does not implement DocumentStore. Search scores are 0.0.
  • parse_kind maps unknown kinds to Repo (wrong; can mark garbage onchain-authoritative).
  • CLI: ingest and search always MemoryStore / ephemeral collect. week never upserts. migrate requires --features postgres.
  • Tests: tests/postgres.rs single #[tokio::test] #[ignore = "requires DATABASE_URL and pgvector"]. CI postgres job sets DATABASE_URL and runs --ignored. Makefile has no test-postgres.
  • Embeddings: hashing dim 64 only. Live Replicate embeds documented, not implemented.
  • No HNSW/IVFFlat index. No telegram retention delete.

Why this is needed

Agents cannot search what we ingested this week across processes. Fixture memory search is a toy. Local operators cannot prove pgvector without memorizing --ignored. Blog grounding (editor issue) needs durable cited chunks.

Constraints / guardrails

  1. Default CI without DATABASE_URL still uses MemoryStore tests (no Docker required for jobs that skip postgres).
  2. When DATABASE_URL is set, CLI must use Postgres (fail if feature not compiled).
  3. Telegram hits labeled non-authoritative in search output (source_kind, citation text). Do not treat cosine hits as volume truth.
  4. Parameterized SQL only. No string-concat queries.
  5. Changing EMBEDDING_DIM requires a migration; hashing and live embeds must match dim.
  6. Secrets never stored in body (redact/fail before upsert).
  7. Unique (source_kind, source_id) upserts (already in schema).
  8. Do not log full embeddings next to tokens.

Relevant files

Path Role
src/store.rs Memory + postgres module
src/search.rs Thin wrapper
src/embed.rs HashingEmbedder
src/main.rs ingest/search/week/migrate
src/pipeline.rs search_collection memory-only
migrations/001_init.sql Schema
tests/postgres.rs Ignored roundtrip
docker-compose.yml Local DB
.gitlab-ci.yml postgres job
Makefile No postgres target
skills/cl8y-research-search/SKILL.md Agent instructions

Recommended direction

  1. Single migrator: include_str! or sqlx migrate from migrations/. Add HNSW (or ivfflat) on embedding. Return score from <=> or cosine.
  2. Trait: async store or a Store enum used by CLI (memory vs postgres). Week ingest after collect.
  3. search CLI: print score, source_kind, citation, snippet; warn if telegram / web_search.
  4. Makefile: make db + make test-postgres (docker compose up -d --wait + DATABASE_URL=... cargo test --features postgres).
  5. When URL unset, skip postgres tests cleanly; when set, always run (no --ignored folklore).
  6. Optional: create-once Replicate embedder same dim (follow-up if hashing is enough for v1). Prefer hashing in CI.

Acceptance criteria

  • cargo run --features postgres -- ingest with DATABASE_URL upserts rows visible to search.
  • week (fixture or live) upserts collection when URL set.
  • search hits have non-zero ranking scores and citations.
  • PostgresStore used through the same ingest/search API as memory.
  • Unknown source_kind does not become Repo.
  • make test-postgres documented; CI postgres job still green.
  • Local: compose up → tests run without the developer passing --ignored by folklore.
  • Search skill updated.

Test plan — functional paths

  1. Roundtrip — upsert DEX overview text; search "dex volume" returns that source_id with score > 0.
  2. Upsert conflict — second ingest same (kind, id) updates body/embedding.
  3. Empty body — skipped (current memory behavior).
  4. Telegram label — hit source_kind=telegram in output.
  5. Week persist — run_week with URL; row count ≥ fixture sources with text.
  6. No URL — default tests still pass (memory).
  7. Migrate idempotent — migrate twice.

Test plan — attack, hack, and abuse

  1. SQL injection — source_id / query string with quotes; parameterized.
  2. Secret in body — upsert of REPLICATE_API_TOKEN=... rejected or redacted before insert.
  3. Kind confusion — garbage kind not authoritative.
  4. Huge body — size cap or truncation documented.
  5. Prompt injection stored — search returns wrapped/untrusted text; week still wraps for the model.

Verification criteria

  1. docker compose up -d && make test-postgres (or README equivalent) green on a clean machine with Docker.
  2. CLI search after ingest prints citations an agent can paste into a post.
  3. CI postgres job green without relying only on a single ignored test.
## Summary Postgres + pgvector is the **product store**: `week` / `ingest` / `search` persist and query embeddings when `DATABASE_URL` is set. `PostgresStore` implements the same store API as memory (async-adapted). Local tests run against Docker pgvector without folklore `--ignored`. Agents retrieve ingested sources **with citations** for blog grounding. Gap: [`gaps/GAP_1788152435.md`](https://gitlab.com/PlasticDigits/cl8y-research/-/blob/main/gaps/GAP_1788152435.md) §4.3, §7.1. Depends on live collect / Telegram / Tavily for *what* is ingested; this issue is the **persistence and search path**. --- ## Current codebase - `docker-compose.yml`: `pgvector/pgvector:pg16`, port **5433**, db `cl8y_research`. - `migrations/001_init.sql` and **duplicated** DDL in `store::postgres::migrate`. - `PostgresStore`: `upsert_async`, `search_async` (`ORDER BY embedding <=> $1`). **Does not implement** `DocumentStore`. Search **scores are `0.0`**. - `parse_kind` maps unknown kinds to `Repo` (wrong; can mark garbage onchain-authoritative). - CLI: `ingest` and `search` always `MemoryStore` / ephemeral collect. `week` never upserts. `migrate` requires `--features postgres`. - Tests: `tests/postgres.rs` single `#[tokio::test] #[ignore = "requires DATABASE_URL and pgvector"]`. CI postgres job sets `DATABASE_URL` and runs `--ignored`. Makefile has no `test-postgres`. - Embeddings: hashing dim 64 only. Live Replicate embeds documented, not implemented. - No HNSW/IVFFlat index. No telegram retention delete. --- ## Why this is needed Agents cannot search what we ingested this week across processes. Fixture memory search is a toy. Local operators cannot prove pgvector without memorizing `--ignored`. Blog grounding (editor issue) needs durable cited chunks. --- ## Constraints / guardrails 1. Default CI **without** `DATABASE_URL` still uses MemoryStore tests (no Docker required for jobs that skip postgres). 2. When `DATABASE_URL` is set, CLI **must** use Postgres (fail if feature not compiled). 3. Telegram hits labeled non-authoritative in search output (`source_kind`, citation text). Do not treat cosine hits as volume truth. 4. Parameterized SQL only. No string-concat queries. 5. Changing `EMBEDDING_DIM` requires a migration; hashing and live embeds must match dim. 6. Secrets never stored in `body` (redact/fail before upsert). 7. Unique `(source_kind, source_id)` upserts (already in schema). 8. Do not log full embeddings next to tokens. --- ## Relevant files | Path | Role | | --- | --- | | `src/store.rs` | Memory + postgres module | | `src/search.rs` | Thin wrapper | | `src/embed.rs` | HashingEmbedder | | `src/main.rs` | ingest/search/week/migrate | | `src/pipeline.rs` | `search_collection` memory-only | | `migrations/001_init.sql` | Schema | | `tests/postgres.rs` | Ignored roundtrip | | `docker-compose.yml` | Local DB | | `.gitlab-ci.yml` | `postgres` job | | `Makefile` | No postgres target | | `skills/cl8y-research-search/SKILL.md` | Agent instructions | --- ## Recommended direction 1. Single migrator: `include_str!` or `sqlx migrate` from `migrations/`. Add HNSW (or ivfflat) on `embedding`. Return `score` from `<=>` or cosine. 2. Trait: async store **or** a `Store` enum used by CLI (`memory` vs `postgres`). Week ingest after collect. 3. `search` CLI: print `score`, `source_kind`, `citation`, snippet; warn if `telegram` / `web_search`. 4. Makefile: `make db` + `make test-postgres` (`docker compose up -d --wait` + `DATABASE_URL=... cargo test --features postgres`). 5. When URL unset, skip postgres tests cleanly; when set, always run (no `--ignored` folklore). 6. Optional: create-once Replicate embedder same dim (follow-up if hashing is enough for v1). Prefer hashing in CI. --- ## Acceptance criteria - [ ] `cargo run --features postgres -- ingest` with `DATABASE_URL` upserts rows visible to `search`. - [ ] `week` (fixture or live) upserts collection when URL set. - [ ] `search` hits have non-zero ranking scores and citations. - [ ] `PostgresStore` used through the same ingest/search API as memory. - [ ] Unknown `source_kind` does **not** become `Repo`. - [ ] `make test-postgres` documented; CI postgres job still green. - [ ] Local: compose up → tests run without the developer passing `--ignored` by folklore. - [ ] Search skill updated. --- ## Test plan — functional paths 1. **Roundtrip** — upsert DEX overview text; search "dex volume" returns that `source_id` with score > 0. 2. **Upsert conflict** — second ingest same `(kind, id)` updates body/embedding. 3. **Empty body** — skipped (current memory behavior). 4. **Telegram label** — hit `source_kind=telegram` in output. 5. **Week persist** — run_week with URL; row count ≥ fixture sources with text. 6. **No URL** — default tests still pass (memory). 7. **Migrate idempotent** — migrate twice. --- ## Test plan — attack, hack, and abuse 1. **SQL injection** — `source_id` / query string with quotes; parameterized. 2. **Secret in body** — upsert of `REPLICATE_API_TOKEN=...` rejected or redacted before insert. 3. **Kind confusion** — garbage kind not authoritative. 4. **Huge body** — size cap or truncation documented. 5. **Prompt injection stored** — search returns wrapped/untrusted text; week still wraps for the model. --- ## Verification criteria 1. `docker compose up -d && make test-postgres` (or README equivalent) green on a clean machine with Docker. 2. CLI search after ingest prints citations an agent can paste into a post. 3. CI postgres job green without relying only on a single ignored test.
PlasticDigits commented 2026-08-31 05:34:58 +00:00 (Migrated from gitlab.com)
Copy link

marked as related to #2

marked as related to #2
PlasticDigits commented 2026-08-31 05:35:00 +00:00 (Migrated from gitlab.com)
Copy link

marked as related to #3

marked as related to #3
PlasticDigits commented 2026-08-31 05:35:01 +00:00 (Migrated from gitlab.com)
Copy link

marked as related to #4

marked as related to #4
PlasticDigits commented 2026-08-31 05:35:06 +00:00 (Migrated from gitlab.com)
Copy link

marked as related to #6

marked as related to #6
PlasticDigits commented 2026-08-31 05:35:08 +00:00 (Migrated from gitlab.com)
Copy link

marked as related to #8

marked as related to #8
PlasticDigits commented 2026-08-31 05:35:10 +00:00 (Migrated from gitlab.com)
Copy link

marked as related to #11

marked as related to #11
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
No results found.
Labels
Clear labels
No items
No labels
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
code/cl8y-research#7
No description provided.