foundry-core/docs/research-api.md

Shared research API

This work belongs to Thesisly 研迹 and uses Foundry Core rather than a separate application backend. The corpus is public; account sessions and future private collections remain project scoped.

Current implementation boundary

internal/research implements the HTTP contract and a PostgreSQL repository based on the fixed Paper Atlas a3b677b schema (TEXT/BIGINT columns, native English title/abstract search index). cmd/foundry enables it only when FOUNDRY_RESEARCH_DATABASE_URL is configured. The Paper Atlas migration is now published: 21 tables passed its independent count/content-hash acceptance. A dedicated foundry_research_reader role has SELECT on the ten tables consumed here, schema USAGE, and read-only defaults. Its connection is stored only in the protected server file below. The existing SQLite implementation was withdrawn. Real-corpus integration has been checked through a loopback Core and the local frontend. Formal production release still requires the user to merge and choose to deploy; this branch is not deployed by these checks. Very broad queries remain subject to the budget and performance limitation documented below. Do not add FOUNDRY_RESEARCH_DATABASE.

The server-side deployment may load a protected /opt/endoleap-infra/foundry/research.env containing FOUNDRY_RESEARCH_DATABASE_URL and FOUNDRY_RESEARCH_SCHEMA (default research). Keep the connection string out of Git, Actions variables, browser configuration and command arguments. Use a dedicated role with schema USAGE and table SELECT only. Core uses a separate two-connection pool, read-only connection defaults and read-only transactions. Each research transaction disables JIT locally: the measured count plan spent about 2.85 seconds compiling JIT functions, and its end-to-end execution exceeded eight seconds. This does not alter shared PostgreSQL settings or replace exact counts with estimates. Startup checks the published schema/SELECT permissions; an explicitly enabled but invalid connection fails candidate readiness.

Deployment migration defaults now follow the shared foundry-postgres container name. FOUNDRY_POSTGRES_CONTAINER or the existing --container option can select a different container without changing database/table names.

Routes

• GET /projects/{project}/research/papers
• GET /projects/{project}/research/papers/{id}

Only configured Foundry projects are accepted. Reading the public corpus does not require a session; these routes do not expose private data or accept writes.

List parameters are q (200 Unicode characters, 20 words), searchIn (text or author), topic (an exact arXiv category), sort (recent or title), cursor (optional, opaque, at most 32768 characters), and pageSize (1–50, default 8). Unknown, malformed or repeated parameters return 400. recent means descending stable paper ID, not publication date. Search terms must become bound query parameters, never raw SQL or user-supplied full-text syntax.

Title/abstract search uses the migration's English vector with plainto_tsquery; author search uses simple-language terms over the stored author JSON. Both treat input as terms, never full-text operators. Initial full-corpus measurements showed eight-second timeouts for text, author and topic counts; the unfiltered count took 7.25 seconds. Execution plans confirmed sequential scans for authors and category joins. deploy/research/query-indexes.sql supplies operator-run concurrent indexes for those two missing access paths. It is separate from Core startup and auth migrations; do not grant DDL to the runtime reader. Both indexes completed on the confirmed ECS (category 35.4 seconds, author 436.47 seconds); measured end-to-end checks are documented below.

List responses contain items, nextCursor, previousCursor, topics, and pageSize. Empty cursor strings mean that direction is unavailable. There is no total, page number, total page count or deep-page cap. The old page parameter is rejected; coordinate the frontend/backend contract update. Each paper preserves original title/abstract, authors, source dates, topics and source links. Publication decisions need source evidence; absent decisions stay unknown. No invented repositories, metrics or generated research conclusions.

Lists use a read-only repeatable-read snapshot without count(*) or OFFSET. Keyset paging uses ID descending for recent order and (title_key, id) ascending for title order, including a reverse query for previous pages. The title key is kept in its stored codec and C collation. Cursors bind normalized query, search scope, topic, sort and page size; changing filters requires a fresh cursor. They are public row positions, not authorization tokens or guaranteed frozen snapshots across requests. Concurrent corpus edits may alter later pages.

Only pageSize+1 positions are selected; the extra position indicates more data, then full rows and batched metadata are loaded for at most pageSize papers. For recent order with only a topic filter, page directly through the existing (tag_id,paper_id) index and deduplicate evidence before LIMIT. For text/author search in recent order, try an ID interval of 512 near the cursor, expanding once to 4096 only if there are some matches but fewer than pageSize+1. Only a full page plus one proves that interval contains the next page. If neither bounded attempt fills the page, all local candidates are discarded and the complete indexed search is used; rare or older matches are never silently truncated. The fallback materializes matching IDs before sorting, avoiding an unbounded ordered heap scan for sparse terms. Title-order filtered queries still use that complete search and can time out for broad terms. No global database or proxy resource settings are changed. The HTTP layer permits two in-flight queries with an eight-second context budget. Busy requests return 429 with Retry-After: 1; deadlines return 504; storage errors return a generic 503 without database details. Missing papers return 404.

Validation

go test -race -count=1 ./internal/research covers routing, query limits, Unicode, anonymous reads, rejected mutations, project lookup, errors, saturation and timeout slot release. Run compilation/tests only on the user-confirmed ECS runner. These transport tests do not prove a database connection, full-corpus query performance or production availability.

The PostgreSQL repository batches source/tag/identifier/submission queries in the same read-only repeatable-read transaction. Author tuples are displayed as given name, family name and suffix; an optional fourth affiliation field is excluded from the display name (observed in real arXiv records); null abstracts/dates remain null. Publication status comes from the latest observed decision, using decision date and ID as tie-breakers, and stays unknown when no decision exists. Unit checks cover author shapes, bound user values and schema qualification. The real Core detail and homepage HTTP routes have returned 200. The frontend has also rendered the actual 3,526,999-paper corpus through a loopback SSH tunnel: eight rows, matching detail title/source, desktop and mobile layout, no page exceptions or horizontal overflow in that check. The earlier count-based API returned 146 author records in 0.336 seconds. Before the cursor optimization, after paging/author fixes, category cs.AR returned 9,457 records in 4.906 seconds, CartoonGAN returned three in 0.332 seconds, and the multi-term search attention is all you need returned 8,653 in 6.325 seconds. These were sequential manual HTTP checks, not a load test. Very broad text searches can still exceed the eight-second budget; clients retain search controls so users can narrow the query or retry. This known limit is not an estimated count or sample fallback. This is not a production deployment or an authentication acceptance result.

The existing deploy/check-schema.sh also loads the synthetic fixture in internal/research/testdata/corpus.sql inside its disposable PostgreSQL container (--network none, no host port). The research test process receives a separate SELECT-only fixture role via RESEARCH_TEST_DATABASE_URL. These integration checks execute real SQL for filtering, stable paging, nullable content, source/tag/identifier joins, latest decision tie-breakers, permission denial and SQL-backed HTTP routes. They never connect to the shared migration instance and do not establish full-corpus performance or live-data correctness.

Text storage compatibility (repair 4e62710)

The Paper Atlas repair uses nul-soh-v1: NUL is stored as SOH/SOH, and a literal SOH as SOH/STX, preserving binary text ordering. Core reads the schema comment paper-atlas:text-codec=nul-soh-v1 through pg_catalog at startup, restores SQL text before JSON parsing/API responses, and encodes bound search terms. An absent/unrelated schema comment keeps legacy text unchanged; an unsupported marked codec or malformed encoded text fails explicitly.

Schema comments are public metadata, so consumers need no privileges on the private migration controller. The research role still needs only schema USAGE and table SELECT. The codec adaptation is on the review branch, pushed with [skip ci] under the user's no-CI instruction; the published schema marker has been verified with the runtime reader. All research tests passed manually against disposable PostgreSQL on the confirmed ECS, including the encoded SQL fixture for schema detection, raw TEXT restoration, JSON author/metadata decoding, search and the HTTP round trip. Passing isolated fixtures does not establish full-corpus API performance.

Identity handoff

The existing production Core received a configuration-only reload using its unchanged release binary: the original projects were retained and thesisly was registered. Both Google and Slack identity records were observed in this project. The user independently confirmed each provider's real authorization, reload/new-tab persistence and logout behavior. Browser control was unavailable, so these browser results are attributed to user acceptance rather than an automated real-account test. This does not deploy the research work branch; mail delivery was not exercised.

Cursor regression coverage

The disposable PostgreSQL fixture covers forward/backward traversal, identical title keys with different IDs, published control-character encoding, sparse matches separated by a large ID gap, and fallback from the bounded interval. Invalid, oversized, cross-filter and wrong-version cursors are rejected before repository execution. The runtime reader retains SELECT-only privileges; no DDL or writes are added. Run tests manually on the confirmed ECS; skip CI for this iteration and retain [skip ci] in commit and PR titles.

Manual cursor measurements (2026-11-07)

On the confirmed ECS, after this task's compilation and disposable fixture had finished, sequential direct requests to the temporary Core returned: homepage 65 ms, next/previous 39/21 ms, graph 345 ms, CartoonGAN 731 ms (3 papers), cs.AR 145 ms, title order 180 ms, author Buhrman 769 ms, and attention is all you need 1312 ms. Recent, topic and title cursor round trips returned the same first page without overlap. These are individual manual samples, not production latency guarantees; concurrent compilation earlier produced multi-second cold measurements. No proxy, database memory, CPU or production configuration was changed.

The final source compiled manually on the ECS; package unit checks and all ten selected cursor/PostgreSQL integration tests passed, including interval expansion and complete fallback. No CI or local build ran. The local authorized Next dev uses the temporary API through the existing SSH tunnel. Independent Chrome also verified real homepage/search navigation, a preserved document, one mobile header/main, no page exceptions and no horizontal overflow. The production Core was not replaced by these temporary preview updates.