STREAMLIT_HANDOFF.md

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

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):

DATABASE_URL    = <Neon connection string, prefer the DIRECT endpoint over -pooler>
GEMINI_API_KEY  = <Google AI Studio key, same model that produced the rows>

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):

# .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

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.)

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:

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:

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.

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 ("<option> parameter is not supported in Gemini API"): document_ocr (server-side PDF OCR), audio_track_extraction (pull audio from video for the embed), and auto_truncate. They're only available via Vertex AI. If you migrate to Vertex (genai.Client(vertexai=True, project=..., location=...)), all three become usable and would let us simplify the pipeline (no manual ffmpeg audio extraction, no manual OCR pre-step).

• <=> is distance, not similarity. Lower = more similar. Always do 1 - (embedding <=> query) for a similarity score.

• HNSW recall. The HNSW index is approximate. For exact ranking on small result sets, you can SET LOCAL hnsw.ef_search = 100; before the query.

• First Neon query after idle is slow. Neon auto-suspends idle databases; expect ~500ms cold-start latency on the first request.

• Don't ship secrets. secrets.toml should be .gitignored. The keys from the previous session are exposed in that chat transcript and should be rotated.

• Streamlit st.video URL playback. Local file paths work great and support start_time seeking. Remote HTTP URLs are flaky for seeking — prefer local files where possible.

• Audio for the NASA recordings. The source assets on DVIDS are MP4 wrappers (large, ~200 MB each). The previous session extracted the audio track to .m4a (a few MB each) and embedded that. Use the .m4a for playback; ignore the source .mp4 unless you want visual.

• pgvector + psycopg3: don't pass list[float] bare. The pgvector adapter doesn't auto-cast Python lists to the vector type. Either bind a numpy.ndarray, or (what the example does) serialise the vector to the textual form '[a,b,c,…]' and use %s::vector in the SQL. Forgetting this fails with operator does not exist: vector <=> double precision[].

• Text queries are biased toward text-rich modalities. In this corpus, any plain text query crowds the top with audio_clip and pdf_page rows because their embedded_text is long (multi-sentence NASA narratives / multi-line OCR), and because video chunks' multimodal vectors are pulled toward visual neighborhoods that short text queries can't reach. Concrete example: the query "instantaneous acceleration" returns 12 NASA Apollo / Mercury audio rows in the top 12 — and does not surface the DVIDS clip dvids_1007707 whose title literally contains "instant acceleration". To let video chunks compete: default to a source_type filter, present faceted results (top-N per type side by side), or steer users toward image queries (same-modality alignment with video frames).

---

12. Quick test: does the database actually have what this doc claims?

Run once before you start coding the UI:

import os, psycopg
with psycopg.connect(os.environ["DATABASE_URL"]) as c:
    for row in c.execute(
        "SELECT source_type, COUNT(*) AS rows, COUNT(DISTINCT parent_id) AS assets "
        "FROM embeddings GROUP BY source_type ORDER BY source_type"
    ).fetchall():
        print(row)

Expected (as of the handoff): ('audio_clip', 27, 7), ('pdf_page', 126, 5), and ('video_chunk', 154, 49) — total 307 rows across 61 distinct parent_ids, all release='PURSUE_2' / release_date='2026-05-22'.

---

13. Suggested next steps for the Streamlit session

1. Drop embeddings_v2.py and the app.py from §10 into a fresh folder.
2. Create .streamlit/secrets.toml with DATABASE_URL and GEMINI_API_KEY.
3. Run §12 to confirm DB connectivity.
4. streamlit run app.py and test a few queries: "spherical UAP over water", "high-speed maneuver", "Apollo astronaut".
5. Polish UI: result cards, thumbnails (DVIDS pages have poster images in og:image if you want to scrape), pagination, multimodal query (already stubbed in the example), per-result "show all chunks of this video" drilldown.
6. Optional: add an admin tab that ingests new assets (re-uses embeddings_v2 plus the retry_release_2.py patterns).