# Handoff: UAP Embeddings → Streamlit Semantic Search Everything you need to build a Streamlit app that does semantic search over the UAP archive embeddings currently sitting in a Neon Postgres + pgvector database. --- ## 1. Context in one paragraph A previous session embedded all of **UAP Release 2 (5/22/26)** — 49 DoD UAP video clips and 7 NASA Apollo/Mercury audio recordings — into a Neon Postgres database using **Google Gemini `gemini-embedding-2-preview`** (768-dim, cosine similarity, indexed with HNSW). The pipeline lives in `embeddings_v2.py` at the repo root. Your job is a Streamlit UI that lets users type a query (or upload an image), embed it with the same model, and return ranked matches with playable media. --- ## 2. What's in the database right now ``` source_type rows distinct assets video_chunk 154 49 DVIDS UAP video clips (Release 2) pdf_page 126 5 source documents (DOW-D017 [116p], DOE-D002 [4p], CIA-D001 [3p], DOE-D001 [2p], DOE-D003 [1p]) audio_clip 27 7 NASA Apollo/Mercury audio recordings (Release 2) TOTAL 307 61 assets all release='PURSUE_2' release_date=2026-05-22 ``` - All current rows use `user_id = '00000000-0000-0000-0000-000000000001'` (a placeholder UUID — the schema is multi-tenant but this archive has one tenant). - `parent_id` is `dvids_{asset_id}` for media rows (e.g. `dvids_1007706`); doc slugs like `dow-uap-d017` for `pdf_page` rows. - `source_id` is `{parent_id}:{start_ms}-{end_ms}` for media chunks and `{parent_id}:p{NNNN}` for PDF pages (e.g. `dow-uap-d017:p0017`). - Vector dimension is **768**. Queries must be 768-dim too. - Every row carries the new `release` (`'PURSUE_2'`) and `release_date` (`2026-05-22`) columns — filter on these in the UI when more releases land. - One pending video (`1007708`, the 513 MB outlier) was not ingested; it can be added later — not a blocker for the UI. - Nothing from earlier releases (Release 1, NARA-CIA, FBI photos, etc.) is embedded yet. If you build the UI to filter on `release` / `parent_id` patterns or future source types, leave it open. --- ## 3. Schema reference ```sql CREATE TABLE embeddings ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, source_type TEXT NOT NULL, -- 'video_chunk' | 'audio_clip' | 'pdf_page' (more later) source_id TEXT NOT NULL, -- '{parent_id}:{start_ms}-{end_ms}' for chunks; '{slug}:p{NNNN}' for pages user_id UUID NOT NULL, organization_id UUID, embedding VECTOR(768) NOT NULL, embedded_image_url TEXT, -- video/audio: DVIDS page URL; pdf_page: whole-PDF war.gov URL embedded_text TEXT, -- caption used during embed (Title + Blurb; or metadata + OCR for pdf_page) start_seconds REAL, -- chunk start (NULL for pdf_page) end_seconds REAL, -- chunk end (NULL for pdf_page) parent_id TEXT, -- 'dvids_1007706' for media; doc slug like 'dow-uap-d017' for pages release TEXT NOT NULL DEFAULT 'PURSUE_2', -- campaign tag (filter on this in the UI) release_date DATE NOT NULL DEFAULT '2026-05-22', -- when the source documents were publicly released created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT uq_embeddings_source UNIQUE (source_type, source_id) ); -- Already created: CREATE INDEX idx_embeddings_embedding ON embeddings USING hnsw (embedding vector_cosine_ops); CREATE INDEX idx_embeddings_parent_id ON embeddings (parent_id) WHERE parent_id IS NOT NULL; CREATE INDEX idx_embeddings_user_id ON embeddings (user_id); ``` Cosine search uses pgvector's `<=>` operator (distance, lower = closer). Convert to similarity with `1 - (embedding <=> query)`. --- ## 4. Secrets — required, not in this file Set as env vars (or Streamlit `secrets.toml`): ```bash DATABASE_URL = GEMINI_API_KEY = ``` The Neon string must include `?sslmode=require`. Ask the user to paste the values from their Neon dashboard and Google AI Studio — they're not embedded here on purpose. The previous session ran against a Neon project owned by the user, and the password / key from that session should be considered exposed and rotated. **Streamlit secrets.toml** (recommended over raw env vars): ```toml # .streamlit/secrets.toml -- DO NOT COMMIT DATABASE_URL = "postgresql://USER:PASSWORD@ep-xxxx.REGION.aws.neon.tech/neondb?sslmode=require" GEMINI_API_KEY = "AIza..." ``` Read in app with `st.secrets["DATABASE_URL"]`. --- ## 5. Dependencies ```bash pip install streamlit google-genai pillow requests "psycopg[binary]" pgvector ``` The only file from this repo you need to copy alongside the Streamlit app is **`embeddings_v2.py`** (it's self-contained — no project-internal imports). Or you can inline the few functions you actually use (see §6/§7 for the bare minimum). --- ## 6. Embedding a user query The model and dimension must match what's already in the DB (`gemini-embedding-2-preview`, 768-d). **The contract is asymmetric and is expressed in the prompt, not the config**: queries get a `task: search result | query: …` prefix; documents go in as `title: … | text: …`. The `EmbedContentConfig.task_type` field is *silently ignored* by gemini-embedding-2 on the consumer API — don't set it. (Helper functions in `embeddings_v2.py` apply the wrapping for you.) ```python import embeddings_v2 as e # Queries — generate_text_embedding auto-wraps with format_query(). vec_text = e.generate_text_embedding("UAP over the Aegean") vec_image = e.generate_image_embedding("./uploaded.jpg") # image-only: no text instruction vec_both = e.generate_multimodal_embedding( "./uploaded.jpg", e.format_query("what is this"), # pre-wrap when there IS a text part ) ``` `embeddings_v2` also exports: - `format_document_text(title, body)` → `"title: {title} | text: {body}"` (use when storing). - `format_query(query)` → `"task: search result | query: {query}"` (use when querying with a text part attached to media). Minimal inline version if you don't want to import `embeddings_v2`: ```python import os from google import genai from google.genai import types as gt client = genai.Client(api_key=os.environ["GEMINI_API_KEY"]) def embed_text(text: str) -> list[float]: r = client.models.embed_content( model="gemini-embedding-2-preview", contents=f"task: search result | query: {text}", # wrap, not task_type= config=gt.EmbedContentConfig(output_dimensionality=768), ) return list(r.embeddings[0].values) ``` --- ## 7. Searching with pgvector `embeddings_v2.search_similar()` already does this and returns a list of `SimilarityHit` dataclasses. If you want raw SQL: ```sql SELECT source_type, source_id, parent_id, start_seconds, end_seconds, embedded_image_url, embedded_text, 1 - (embedding <=> %s) AS similarity FROM embeddings WHERE user_id = %s::uuid AND (%s::text IS NULL OR source_type = %s) AND (embedding <=> %s) <= %s -- distance <= 1 - threshold ORDER BY embedding <=> %s LIMIT %s; ``` Params, in order: `query_vec, user_id, source_type_or_null, source_type_or_null, query_vec, (1 - threshold), query_vec, limit`. Don't forget `register_vector(conn)` from `pgvector.psycopg` after connecting — without it psycopg can't bind `list[float]` to the `vector` type. --- ## 8. Result interpretation (per source_type) ### `video_chunk` - `parent_id` → e.g. `dvids_1007706`. Strip the prefix to get the DVIDS asset id. - `embedded_image_url` → the human DVIDS page, e.g. `https://www.dvidshub.net/video/1007706`. - `start_seconds`, `end_seconds` → the chunk's offsets within the source video (one video typically has multiple chunks; show the timestamp to the user). - `embedded_text` → the caption that was attached at embed time: the `Video Title` + `Description Blurb` from `uap-data_v2.csv`. - DVIDS deep-link with timestamp: append `?t={int(start_seconds)}` to the page URL (or use the local file with `st.video(local_path, start_time=int(start_seconds))`). ### `audio_clip` - Same `parent_id` shape but with audio DVIDS ids (1007870–1007879 range for Release 2). - `embedded_image_url` is set even though the asset is audio (it's the DVIDS page URL — the column was reused as the canonical media URL for any kind). - For long recordings (>80s — the model's audio input cap), the asset is segmented into ≤75s pieces; one row per piece with its own start/end. ### `pdf_page` - `parent_id` is the doc slug (e.g. `dow-uap-d017`, `cia-uap-d001`, `doe-uap-d001`, `doe-uap-d002`, `doe-uap-d003`). - `source_id` is `{parent_id}:p{NNNN}` with the page number zero-padded to 4 digits (e.g. `dow-uap-d017:p0017`). Parse with a tiny regex to surface the page number in the UI. - `embedded_image_url` is the whole-PDF URL on war.gov — there's no per-page URL on the source site, so deep-linking to a specific page means opening the PDF and scrolling. - `embedded_text` is composed at embed time as: `{Agency} - {Title}` / `Date: ... Location: ...` / `Page N of M.` / `Document context: {blurb}` / `Page OCR: {ocr}`, capped at 8000 chars. The same string was paired with the rendered page image in the multimodal embed call. - `start_seconds` / `end_seconds` are NULL. - A rendered page image lives locally at `D:\divided\release_2\UAP_Release_2\pages\{slug}\page_NNNN.png` (150 dpi). Display it directly with `st.image(local_path)`; link to `embedded_image_url` to open the whole PDF on war.gov. --- ## 9. Where the media files live The previous session saved every downloaded media file under the user's local drive (set by them as the persistence target): ``` D:\divided\release_2\UAP_Release_2\ ├── videos\dvids_{id}.mp4 (49 files, normalized originals from DVIDS) ├── audio\dvids_{id}.{ext} (7 source MP4 wrappers + extracted .m4a tracks) └── pages\{slug}\page_NNNN.png (PDF page renders at 150 dpi, e.g. pages\dow-uap-d017\page_0017.png) ``` The page PNGs are generated by `ingest_pdf_pages.py` and are safe to delete and re-generate from the source `release_2\{doc}\page_NNNN\page_NNNN.pdf` files. This matters for the Streamlit UI: - If the app runs on the same machine, you can pass the local path straight into `st.video(path, start_time=...)` / `st.audio(...)` — that's the smoothest playback experience and supports seeking. - If the app runs elsewhere, link out to the DVIDS page (`embedded_image_url`). Direct CloudFront URLs work for download but seeking via HTTP from the browser is hit-or-miss. - A third option: upload the local files to S3/R2/Vercel Blob and rewrite URLs. Not done. If the file isn't found locally and the URL is the DVIDS page, **don't try to embed the CloudFront MP4 directly in `st.video()`** — DVIDS' `/download/asset/` endpoint is 403-gated, and the CloudFront URLs aren't stored in the DB. You'd need to re-scrape the page (see the `scrape_media_url` helper in `retry_release_2.py` if you want that pattern). --- ## 10. Minimal working Streamlit app Drop this at `app.py` next to `embeddings_v2.py`, set the secrets, and run `streamlit run app.py`. It covers text query, source-type filter, threshold slider, and inline media playback with timestamp seeking. ```python import os import re from pathlib import Path import psycopg import streamlit as st import embeddings_v2 as e USER_ID = "00000000-0000-0000-0000-000000000001" MEDIA_ROOT = Path(r"D:\divided\release_2\UAP_Release_2") # change if elsewhere SOURCE_TYPES = ("video_chunk", "audio_clip", "pdf_page") st.set_page_config(page_title="UAP Archive Semantic Search", layout="wide") # --- bootstrap --------------------------------------------------------------- for k in ("DATABASE_URL", "GEMINI_API_KEY"): if k in st.secrets: os.environ.setdefault(k, st.secrets[k]) if not os.environ.get(k): st.error(f"Missing {k} — add it to .streamlit/secrets.toml") st.stop() @st.cache_resource def get_conn(): return psycopg.connect(os.environ["DATABASE_URL"]) @st.cache_data(ttl=3600, show_spinner=False) def embed_query_text(text: str) -> list[float]: # generate_text_embedding auto-wraps with format_query() and drops task_type. return e.generate_text_embedding(text) @st.cache_data(ttl=3600, show_spinner=False) def embed_query_image(image_bytes: bytes, mime: str) -> list[float]: import tempfile suffix = "." + mime.split("/", 1)[1] with tempfile.NamedTemporaryFile(delete=False, suffix=suffix) as f: f.write(image_bytes) path = f.name try: # image-only embed: same call for query and document side. return e.generate_image_embedding(path) finally: os.unlink(path) def search(vec, *, source_type=None, release=None, limit=20, threshold=0.30): # pgvector's psycopg adapter doesn't auto-cast list[float] to vector --- # serialise to the textual '[a,b,c]' form and let Postgres cast. vec_str = "[" + ",".join(f"{x:.6f}" for x in vec) + "]" clauses = ["user_id = %s::uuid", "(embedding <=> %s::vector) <= %s"] params = [USER_ID, vec_str, 1 - threshold] if source_type: clauses.append("source_type = %s") params.append(source_type) if release: clauses.append("release = %s") params.append(release) sql = f""" SELECT source_type, source_id, parent_id, start_seconds, end_seconds, embedded_image_url, embedded_text, release, release_date, 1 - (embedding <=> %s::vector) AS similarity FROM embeddings WHERE {' AND '.join(clauses)} ORDER BY embedding <=> %s::vector LIMIT %s """ ordered = [vec_str, *params, vec_str, limit] with get_conn().cursor() as cur: cur.execute(sql, ordered) cols = [d.name for d in cur.description] return [dict(zip(cols, r)) for r in cur.fetchall()] _PAGE_RE = re.compile(r"^(.+):p(\d+)$") def local_media_path(row: dict) -> Path | None: st_type = row["source_type"] if st_type == "video_chunk": asset_id = row["parent_id"].removeprefix("dvids_") p = MEDIA_ROOT / "videos" / f"dvids_{asset_id}.mp4" return p if p.exists() else None if st_type == "audio_clip": asset_id = row["parent_id"].removeprefix("dvids_") for ext in ("m4a", "mp3", "mp4", "wav", "aac", "ogg"): p = MEDIA_ROOT / "audio" / f"dvids_{asset_id}.{ext}" if p.exists(): return p return None if st_type == "pdf_page": m = _PAGE_RE.match(row["source_id"]) if not m: return None slug, page_num = m.group(1), int(m.group(2)) p = MEDIA_ROOT / "pages" / slug / f"page_{page_num:04d}.png" return p if p.exists() else None return None def page_number(row: dict) -> int | None: if row["source_type"] != "pdf_page": return None m = _PAGE_RE.match(row["source_id"]) return int(m.group(2)) if m else None # --- UI ---------------------------------------------------------------------- st.title("UAP Archive — Semantic Search") st.caption("Gemini 768-d embeddings, cosine similarity over Neon + pgvector.") with st.sidebar: mode = st.radio("Query type", ["Text", "Image"], horizontal=True) st_filter = st.selectbox("Source type", ["all", *SOURCE_TYPES]) release_filter = st.selectbox("Release", ["all", "PURSUE_2"]) threshold = st.slider("Min similarity", 0.0, 0.9, 0.30, 0.05) limit = st.slider("Max results", 5, 50, 20) vec = None if mode == "Text": q = st.text_input("Search query", placeholder="e.g. spherical UAP over water") if q: with st.spinner("Embedding query…"): vec = embed_query_text(q) else: up = st.file_uploader("Upload an image", type=["jpg", "jpeg", "png", "webp"]) if up: st.image(up, width=240) with st.spinner("Embedding image…"): vec = embed_query_image(up.getvalue(), up.type) if vec is None: st.info("Enter a query or upload an image.") st.stop() with st.spinner("Searching Neon…"): rows = search( vec, source_type=None if st_filter == "all" else st_filter, release=None if release_filter == "all" else release_filter, limit=limit, threshold=threshold, ) if not rows: st.warning("No matches above the similarity threshold. Try lowering it.") st.stop() st.subheader(f"{len(rows)} result(s)") for r in rows: with st.container(border=True): c1, c2 = st.columns([4, 1]) with c1: header = f"**[{r['parent_id']}]({r['embedded_image_url']})** · `{r['source_type']}` · sim **{r['similarity']:.3f}**" page = page_number(r) if page is not None: header += f" · page {page}" elif r["start_seconds"] is not None: header += f" · {r['start_seconds']:.1f}s → {r['end_seconds']:.1f}s" st.markdown(header) if r["embedded_text"]: st.write(r["embedded_text"][:600] + ("…" if len(r["embedded_text"]) > 600 else "")) local = local_media_path(r) if local and r["source_type"] == "video_chunk": st.video(str(local), start_time=int(r["start_seconds"] or 0)) elif local and r["source_type"] == "audio_clip": st.audio(str(local), start_time=int(r["start_seconds"] or 0)) elif local and r["source_type"] == "pdf_page": st.image(str(local), use_container_width=True) if r["embedded_image_url"]: st.link_button("Open full PDF on war.gov", r["embedded_image_url"]) elif r["embedded_image_url"]: st.link_button("Open source", r["embedded_image_url"]) with c2: st.metric("similarity", f"{r['similarity']:.3f}") st.caption(f"{r['release']} · {r['release_date']}") ``` --- ## 11. Gotchas / things that will trip you up - **Pooled vs direct Neon endpoint.** The user's connection string in the earlier session was the `-pooler` host. For a long-lived Streamlit process that reuses one connection across many queries, psycopg3 will eventually promote a statement to a *named* prepared statement (default `prepare_threshold=5`), which PgBouncer in transaction-pooling mode cannot hold across transactions. Use the **direct** endpoint (host without `-pooler`) or set `prepare_threshold=None` on the connection. - **Dimension must match.** The column is `VECTOR(768)`. Don't pass a 1536-dim vector — it'll fail on the cast. If you ever switch to a different `output_dimensionality`, you'll need to migrate the column. - **Instruction-in-prompt, not `task_type=`.** gemini-embedding-2 silently ignores `EmbedContentConfig.task_type` on the consumer API and instead expects the task to be expressed *inside the content*. Wrap documents as `title: {title} | text: {body}` (via `e.format_document_text(...)`) and queries as `task: search result | query: {q}` (via `e.format_query(...)`, applied automatically by `e.generate_text_embedding`). Skipping this produces noticeably worse ranking — the previous version of this corpus ranked NASA audio narratives above DOW UAP video clips on the query "instantaneous acceleration" because the asymmetric format wasn't applied; the re-embed with proper wrapping put `dvids_1007707` at ranks 1–4. - **Vertex-only config options.** Three `EmbedContentConfig` fields exist in the SDK but are rejected by the consumer Gemini API (`"