# Social Work Meta-Data Project — Agent Connection Guide (400+ lines, ends "END OF GUIDE"; anything shorter is a summary — refetch raw)

This one file is everything an AI assistant needs to query the project's two
hosted research databases. Plain HTTPS, public read-only key, no account, no
installation, no database driver. If you (the assistant) were given this file
or its URL, connect as described below and start querying — there is nothing
to download or set up.

FIRST, CHECK YOU HAVE THE WHOLE FILE. Some assistants retrieve a URL through
a summarizing tool, which returns a paraphrase rather than the document: the
schema tables, the search API, and the rules all vanish, and nothing warns
you. The complete file is about 400 lines and ends with the line
"END OF GUIDE". If what you are reading is shorter than that, or lacks a
table listing `swrd.papers` with its columns, you have a summary. Re-fetch
the raw text with a shell command, not a browsing or fetch tool:

    curl -s https://beperron.github.io/SocialWork-MetaData/llms.txt

and work from that.

Site: https://beperron.github.io/SocialWork-MetaData/
HTML mirror of this file (for tools that cannot fetch plain text): https://beperron.github.io/SocialWork-MetaData/llms.html
Repository: https://github.com/beperron/SocialWork-MetaData
Worked examples (cookbook): https://github.com/beperron/SocialWork-MetaData/tree/master/cookbook

## Connect (one endpoint, two databases)

Base URL:  https://kcffctxedcscvvposypb.supabase.co/rest/v1
API key:   sb_publishable_RY5wIh9k-D_41VZJdtCv7Q_NV--EQP5
           (intentionally public; grants read-only access; writes are rejected
           at the database level)

"Connecting" here means nothing more than sending HTTPS requests as shown
below. There is NO MCP server, no SDK, no client library, and no login step —
do not look for one.

FIRST, check that your execution runtime can reach the database at all.
Some sandboxes (phone and cloud assistants especially) can fetch web pages
through a browsing tool but give the code runtime no DNS or outbound network,
and the two are different channels. Probe from the same runtime you will
query from:

    curl -sI --max-time 6 https://kcffctxedcscvvposypb.supabase.co/rest/v1/ | head -1

- `HTTP/2 401` (or any HTTP status): the host is reachable — the 401 just
  means no key was sent; proceed normally.
- `Could not resolve host`, timeout, or curl exit 6/28: your runtime has no
  egress to this host. This is the normal state of phone apps and most hosted
  sandboxes; it is a property of your environment, not a database outage.
  Do not keep retrying and do not blame the database.
  Tell the user plainly: "my execution environment cannot reach
  kcffctxedcscvvposypb.supabase.co; the databases need outbound HTTPS on
  port 443." The fallbacks, in order: run in an environment with network
  access (a desktop CLI session usually has it); or give the user the exact
  commands or Python from this file to run locally and paste the results
  back to you.

Every request needs BOTH key headers, plus a profile header naming the database:

- `apikey: <KEY>` and `Authorization: Bearer <KEY>`
- `Content-Profile: swrd` or `Content-Profile: sswr` on POST
  (`Accept-Profile: ...` on GET)

`swrd` = journal articles. `sswr` = SSWR conference presentations.

Run any read-only SQL by POSTing to `rpc/run_sql`; results return as JSON.
Preferred pattern (Python — avoids all shell-quoting pitfalls):

```python
import requests   # or urllib from the stdlib
KEY = "sb_publishable_RY5wIh9k-D_41VZJdtCv7Q_NV--EQP5"
def run_sql(query, schema="swrd"):
    r = requests.post(
        "https://kcffctxedcscvvposypb.supabase.co/rest/v1/rpc/run_sql",
        headers={"apikey": KEY, "Authorization": f"Bearer {KEY}",
                 "Content-Profile": schema, "Content-Type": "application/json"},
        json={"query": query})
    r.raise_for_status()
    return r.json()   # list of row dicts
```

curl works too, but beware: SQL contains single quotes, and nesting
SQL-inside-JSON-inside-shell-quotes is easy to mangle (the symptom is
`PGRST102: Empty or invalid json`). If you use curl, write the JSON body to a
file and pass `-d @body.json`, or let Python build the request instead:

```bash
KEY="sb_publishable_RY5wIh9k-D_41VZJdtCv7Q_NV--EQP5"
printf '%s' '{"query": "select publication_year, count(*) from swrd.papers where publication_year >= 1989 group by 1 order by 1"}' > body.json
curl -s "https://kcffctxedcscvvposypb.supabase.co/rest/v1/rpc/run_sql" \
  -H "apikey: $KEY" -H "Authorization: Bearer $KEY" \
  -H "Content-Profile: swrd" -H "Content-Type: application/json" \
  -d @body.json
```

Limits: single SELECT statements only (no semicolons, no writes), 30-second
timeout, 1,000 rows max per call (paginate with offset/limit or aggregate
server-side).

## If the endpoint seems broken, check these two things first

Almost every reported connection failure is one of these, and both return
error messages that look like the database is misconfigured when it is not.

**1. Do not point a web-fetch or browsing tool at the API URL.** The endpoint
is a POST API, not a web page. Many assistants have a "fetch this URL" tool
that issues a GET; on this endpoint that produces misleading errors:

| What you did | What comes back |
|---|---|
| GET, no headers (a browse/fetch tool) | `401 No API key found in request` |
| GET with the key | `404 PGRST202 ... could not find the function public.run_sql` |
| POST but no `Content-Profile` header | `404 PGRST202 ... public.run_sql` |
| POST with key + `Content-Profile` | `200` and your rows |

The 404 does NOT mean the function is missing. `public.` in that message is
the tell: without `Content-Profile: swrd` (or `sswr`) the request looks in the
wrong schema. Use a shell or code tool that can POST with headers — curl,
Python requests, urllib, fetch — never a page-fetch tool.

**2. Approve the host if your tool sandboxes network access.** Coding
assistants (Claude Code, Codex CLI, Cursor, and similar) may block or prompt
before the first outbound request. The prompt shows an opaque hostname, which
looks alarming but is just the project's hosted database:

    kcffctxedcscvvposypb.supabase.co     port 443, HTTPS, read-only

Approve it once, or allowlist it in your tool's settings, and every query in
this guide works. If you cannot approve it, see the reachability check above:
the runtime may have no egress at all, which is a different problem with a
different fix.

## The two databases

**SWRD (`swrd`) — journal articles, 1989–2025.** The corpus for analysis is
**87,329 systematically compiled records from 1989 onward** (title, abstract,
authors, affiliations, journal, year, DOI) across 88 disciplinary social work
journals; within it, 62,602 research articles with abstracts are each
classified: `is_scientific`, `is_empirical`, `research_method` (exact values:
`Quantitative`, `Qualitative`, `Mixed-Methods`, `Review`; null for
non-empirical articles). When describing this database or reporting from it,
use the 1989+ corpus, not the raw table total.

The `swrd.papers` table physically holds 110,618 rows because it also stores
a pre-1989 Supplement (23,288 records, 1920–1988) that is substantially
incomplete — many records lack abstracts and details. ALWAYS filter
`publication_year >= 1989` unless the user explicitly asks for the historical
supplement, and treat any pre-1989 counts as lower bounds. The most recent
years (2024–2025) are also incomplete because publisher indexing lags —
exclude them from trend claims and say so.

Author records: 164,549 `swrd.authors` rows and 234,010 `swrd.paper_authors`
links (204,493 of them on 1989+ papers). CRITICAL: author names are stored
exactly as published, with NO disambiguation — never report unique-author
counts from SWRD as fact; caveat them. The same printed string also occupies
many ids (164,549 id rows vs 129,605 distinct name strings), so neither
`count(distinct name)` nor grouping by `author_id` yields person-level numbers
— aggregate by name string when you must, and caveat the result.

`swrd.author_name_enrichment` adds a DERIVED fuller name for 26,313
initials-only rows ('SHERIDAN, MS' -> 'Sheridan, Mary S.', author_id 3; evidence: Crossref on
the author's own papers, unanimous across every checkable paper; audit any
row via its `evidence_dois`). It is an annotation, not a correction and NOT
disambiguation: `authors.name` is unchanged, two ids sharing a `full_name`
are not thereby one person, and the table must never be used to merge author
ids. When reporting names "as published", use `authors.name`, never
`full_name`.

**Double-indexed articles.** About 850 research articles (1.4% of the 62,628
`is_scientific` records from 1989 on) are indexed twice, usually under two
journal ids, one of which is wrong. When building a corpus, dedupe on
normalized title within publication year (double-indexed pairs usually have
differing or null DOIs, so DOI alone misses them). "Normalized title" means
lowercase with every non-alphanumeric character stripped, compared within the
same publication year so that different papers sharing a generic title
("Editorial") are not collapsed:

    lower(regexp_replace(title, '[^a-zA-Z0-9]', '', 'g')) || '|' || publication_year

Do NOT apply that rule across the whole table. Book reviews of the same book
appear in several journals in the same year, each with its own DOI, and are
genuinely separate records; so are editorials and the many rows titled
"Untitled". Deduping title+year unscoped collapses ~2,100 rows, most of which
are not duplicates. Scope it to `is_scientific`, or check `document_type`.

**`doi` is not guaranteed to hold a DOI.** 926 rows in the 1989+ corpus
(1.3% of the 69,911 populated values) still carry an OAI identifier or an
internal `dc/…` hash instead — the residue of a 3,556-row defect repaired in
data release v1.1, each residual itemised with its reason in
`qc/doi/data/review_queue.csv`. Test with `doi ~ '^10\.'` before linking,
citing, or matching on it. Never use "has a DOI" as a tie-break when deduping
— where a pair splits this way the malformed row is usually the one to drop,
so the naive rule keeps the wrong one.

| Table | Rows | Key columns |
|---|---|---|
| `swrd.papers` | 110,618 (87,329 are the 1989+ corpus) | `id` (int PK), `title`, `abstract`, `publication_year`, `journal_id`, `doi`, `document_type` (inconsistently cased legacy strings — use `is_scientific` to define research articles, not this), `is_scientific`, `is_empirical`, `research_method`, `fts` (tsvector over title+abstract) |
| `swrd.journals` | 91 | `id`, `name`, `publisher` — 91 table rows cover the 88 disciplinary journals: two rows carry no articles and one journal appears under two ids, so `count(*)` here is not the journal count to report |
| `swrd.authors` | 164,549 | `id`, `name` (as published), `orcid` |
| `swrd.author_name_enrichment` | 26,313 | `author_id` (PK -> authors.id), `name_as_published`, `full_name`, `family`, `given_full`, `evidence_dois`, `n_papers_checked`, `batch` — DERIVED annotation, see the author-records note above |
| `swrd.paper_authors` | 234,010 | `paper_id`, `author_id`, `position` (1 = first), `is_corresponding` |
| `swrd.organizations` | 34,967 | `id`, `name` |
| `swrd.author_affiliations` | 113,646 | `author_id`, `organization_id`, `paper_id` |

Views: `swrd.papers_with_journals`, `swrd.publication_trends`,
`swrd.database_info` (citation + counts).

**SSWR (`sswr`) — conference presentations.** All 23,793 presentations at the
Society for Social Work and Research annual conference, 2005–2026, every one
with a full abstract and a method label (`methodology` is never null; the
2026 program year is complete — the conference has already occurred). Distinguishing feature: authors ARE
disambiguated — 21,209 canonical identities — so author-level and longitudinal
analyses are reliable here. Always start scholar analyses with the fuzzy
lookup `sswr.search_authors_by_name` and work from the returned `author_id`.

| Table | Rows | Key columns |
|---|---|---|
| `sswr.papers` | 23,793 | `id` (text PK, `YEAR-LETTER-NNNN`, e.g. `2019-O-0142`; the letter is a session code — `U` 11,930, `P` 8,864, `O` 2,081, `X` 478, `G` 246, `F` 183, `W` 11 — so do not write a regex that assumes only O/P/U), `title`, `abstract`, `year`, `format` (`Oral` 12,346, `Poster` 8,868, `Unknown` 2,046, `Sig` 246, `Flash` 183, `Other` 93, `Workshop` 11 — capitalised, unlike `methodology`), `methodology` (lowercase: `quantitative`, `qualitative`, `mixed_methods`, `review`, `other`), `author_count`, `fts` (tsvector over title+abstract) |
| `sswr.authors` | 21,209 | `id` (int PK), `name` (canonical), `variants`, `institutions`, `years`, `total_papers` |
| `sswr.paper_authors` | 69,924 | `paper_id`, `author_id` (canonical), `name` (as printed — see warning below), `author_order` (1 = first), `institution_normalized`, `position_normalized` (mostly 'unknown' before 2011), `country_normalized` |
| `sswr.paper_export` | view | flat, one row per paper with author arrays — easiest for exports |

SSWR has no journal or DOI columns — presentations are not journal articles.
When exporting a table that combines both databases, leave those fields empty
for conference rows or label the venue "SSWR annual conference"; do not
invent identifiers.

## Search (built in — no setup, no local models)

Ranked full-text search over titles+abstracts, same API in both schemas.
Call from SQL (always `select ... from`, never a bare call):

```sql
-- top matches, best first (rank is a relevance score, NOT a position);
-- select abstract up front if you plan to read matches — saves a round trip
select id, title, abstract, publication_year, rank
from swrd.search_papers_keyword('kinship care', 20)
```

Rank scores are relative to their own query — never compare or merge raw
ranks across different query strings. There is also no absolute cutoff: a
high `rank` does not certify relevance the way a semantic `similarity` band
does, so "the top N" always needs abstracts read before it is reported as
on-topic. Expect little or no overlap between phrasings — on some topics
different vocabulary returns entirely disjoint result sets, which is the
argument for running several phrasings rather than trusting one. Search output also
contains the double-indexed articles described above under two DIFFERENT ids — e.g.
`search_papers_keyword('suicide', 5)` returns the same 2022 article at two ranks (ids
120276 and 99511, as of v1.4), so dedupe any top-N you report on normalized
title-within-year, not on id. When unioning results from several
phrasings, dedupe by `id` and note which phrasing(s) found each record; to
present a merged top-N, order by the primary phrasing's ranking and annotate
which rephrasings also matched each item.

All search functions accept optional `min_year`/`max_year` after
`match_count`. `match_count` caps the returned rows and reports no total —
to size a topic, run a SQL count with an fts sweep (below), not a search
call. When claiming search "missed" something, distinguish true vocabulary
misses (the phrase is absent from title+abstract — check with `fts @@
phraseto_tsquery(...)` per id) from ranking misses (present but below your
top-N cutoff).

```sql
select id, title, year, methodology, rank
from sswr.search_papers_keyword('kinship care', 20)
```

IMPORTANT: pass short topical phrases (2–4 content words), never full
sentences. The search AND-matches terms, so a long research question returns
zero rows — "housing instability among youth aging out of foster care" finds
nothing, while "foster youth housing" and "foster care homelessness" each find
dozens. Distill a question into several short phrases and union the results.

SSWR helpers: `sswr.search_authors_by_name(query_text, match_count)` — fuzzy
scholar lookup, start here for any author question. Returns `author_id,
author_name, variants, institutions, years, total_papers, name_similarity`.
Do NOT blindly take the highest-similarity row: residual duplicate identities
exist (e.g. an inverted-name variant with a handful of papers can outrank the
canonical entry holding the full record). Compare candidates on
`total_papers`, `years`, and `institutions`; when a small entry looks like
the same person as a large one, verify rather than assume: pull the small
id's papers and check whether its co-authors are among the large id's
frequent collaborators, and whether the large id is absent from those papers
(if so, combining adds records rather than double-counting). Then analyze
both ids together and say so.
If you already hold a set of paper ids (a corpus you built earlier), do NOT
go through the name lookup: join `sswr.paper_authors` to those ids and read
`author_id` directly, then join `sswr.authors` for display names. The fuzzy
lookup is for the case where you start from a person's name. Note that
duplicate-identity detection is only available on the name-search path, so an
id-first analysis should say that residual duplicates were not screened.

ALWAYS aggregate authors by `author_id`, never by `paper_authors.name`.
That column holds the name exactly as printed on each paper, and 1,547 SSWR
authors appear under more than one printed form, so a `GROUP BY name`
silently splits one person into several rows and returns HTTP 200 with wrong
counts — no error to catch. Join `sswr.authors` for a display name after
aggregating on the id.

Also: `sswr.search_papers_by_institution(query_text, match_count, min_year,
max_year)`; institution strings in `paper_authors.institution_normalized`
retain some spelling variants ("St. Louis" vs "Saint Louis") — group with
care.

For high recall on a topic, combine three angles:
1. `search_papers_keyword` with the question itself;
2. the same function with 2–3 rephrasings in different vocabulary
   (e.g. "custodial grandparents", "kinship caregivers");
3. a SQL sweep with `websearch_to_tsquery` or an anchored regex:

```sql
select p.year, count(*) as n
from sswr.papers p
cross join websearch_to_tsquery('english', 'grandparents grandchildren') q
where p.fts @@ q
group by 1 order by 1
```

`websearch_to_tsquery` accepts quoted phrases and `or` — e.g.
`'"parental imprisonment" or "incarcerated mother" or "children of inmates"'`
— which is the pattern for a broad OR-sweep of synonyms.

```sql
select count(*) from swrd.papers
where publication_year >= 1989
  and (coalesce(title,'') || ' ' || coalesce(abstract,'')) ~* '\mkinship care\M'
```

## Semantic search (optional — needs a one-time local model setup)

Cookbook recipes 01–07 never need this; recipes 08–09 use it. Every abstract
also carries a 768-dim meaning vector, searchable once you can embed the
query locally with **EmbeddingGemma 300M via Ollama** — the exact model the
databases were built with; no other model's vectors are comparable. Setup is
a check-install-verify script (ask the user before installing; ~620 MB once):
https://beperron.github.io/SocialWork-MetaData/skills/ollama-embeddings/SKILL.md

```python
emb = requests.post("http://localhost:11434/api/embed", json={
    "model": "embeddinggemma:300m",
    "input": ["task: search result | query: " + question]}).json()["embeddings"][0]
# same headers as run_sql above (Content-Profile picks swrd or sswr)
hits = requests.post(
    "https://kcffctxedcscvvposypb.supabase.co/rest/v1/rpc/search_papers_semantic",
    headers={"apikey": KEY, "Authorization": f"Bearer {KEY}",
             "Content-Profile": "swrd", "Content-Type": "application/json"},
    json={"query_embedding": emb, "match_count": 20}).json()
```

The `task: search result | query: ` prefix is REQUIRED — without it results
degrade badly. `search_papers_semantic(query_embedding, match_count,
min_year, max_year)` returns the same columns as keyword search plus
`similarity` (cosine 0–1).

Read `similarity` relatively, not against a fixed cutoff. The scale shifts
with the length and specificity of the query, so the same number means
different things across searches. Measured on this corpus: the two-word query
"child welfare" returns a top score of 0.560 with 1 of 40 rows above 0.55,
while a long natural-language question returns 0.656 with all 40 rows above
it. A fixed "0.55 means on-topic" rule would discard nearly everything in the
first case and accept everything in the second. Sort by similarity, find the
drop where scores flatten into the noise, and read abstracts around that
elbow; below about 0.5 the function stops returning rows at all.

Note: the function returns at most ~40 rows regardless of `match_count`, so
"pull the top 300" is not achievable in one call — to retrieve a deeper
neighborhood, embed several distinct phrasings of the topic and union their
top-40s (each phrasing probes a different semantic direction).
`search_papers_hybrid(query_text, query_embedding, match_count)` fuses the
semantic and keyword rankings. Its `query_text` argument behaves like keyword
search, so keep it to a short phrase even though the embedding half tolerates
longer wording. (Reciprocal-rank fusion; extra fields
`rrf_score`, `semantic_rank`, `keyword_rank` — NULL means not in that arm's
top 60, a fixed pool depth independent of `match_count`) and is the best
default when both arms are available.

## Rules that prevent the most common errors

1. Stay inside one schema per query. Never join `swrd.*` with `sswr.*` —
   id types are incompatible (SWRD int vs SSWR text like `2019-O-0142`).
2. Year columns: `swrd.papers.publication_year`, `sswr.papers.year`. The
   `paper_authors` tables have NO year column — join `papers` to filter by year.
3. Search functions: always `select <cols> from schema.search_papers_keyword(...)`.
   Results arrive pre-sorted, best first; `rank` is a float score — never
   `where rank = 1`, never re-sort ascending.
4. Search functions already return the useful columns
   (SWRD: `id, title, abstract, publication_year, journal_name` + score;
   SSWR: `id, title, abstract, year, methodology, authors` + score).
   There is no `journal_id`/`paper_id` in their output; if you need another
   column (e.g. `doi`, `research_method`), join `papers p on p.id = s.id`
   via the returned `id`.
5. Qualify every column with a table alias in joins (`p.id`, `pa.paper_id`).
6. SWRD research-corpus questions: filter
   `publication_year >= 1989 and is_scientific and abstract is not null` —
   but NOT when the question asks for totals or raw record counts.
7. If the question says "how many", return a count, not a listing.
8. Trailing semicolons cause syntax errors in `run_sql`; send one bare SELECT.

Troubleshooting: any error naming `public.` (`could not find the table
'public.papers'`, `could not find the function public.run_sql`) → the profile
header is missing; add `Content-Profile: swrd` on POST or `Accept-Profile` on
GET. A `401 No API key found` on a URL you opened in a browser or fetch tool
→ this is a POST API, not a page; see the section above. `relation "papers" does not exist` → schema-qualify
(`swrd.papers` / `sswr.papers`).
A `400` with `42601` "syntax error" on SQL that looks valid → check whether it
is a write (`update`/`insert`/`delete`/`drop`): `run_sql` only parses SELECT, so
writes fail as syntax errors, not "permission denied"; direct table writes via
`/rest/v1/<table>` are rejected by row-level security (`42501`). Read-only
either way.
Exactly 1,000 rows → you hit the per-call cap; paginate or aggregate.

## Citations to report when the user publishes with these data

- SWRD: Perron, B. E., Victor, B. G., & Qi, Z. (2026). Evolution of social
  work knowledge production over 35 years. Research on Social Work Practice.
  https://doi.org/10.1177/10497315261416833
- SSWR: Perron, B. E., Victor, B. G., & Qi, Z. (2026). AI-assisted curation
  of conference scholarship. arXiv. https://doi.org/10.48550/arXiv.2603.06814

## Fuller documentation

- SWRD skill file: https://beperron.github.io/SocialWork-MetaData/skills/swrd-database-skill.md
- SSWR skill file: https://beperron.github.io/SocialWork-MetaData/skills/sswr-database-skill.md
- Embedding setup (semantic search): https://beperron.github.io/SocialWork-MetaData/skills/ollama-embeddings/SKILL.md
- Cookbook (worked examples): https://github.com/beperron/SocialWork-MetaData/tree/master/cookbook

END OF GUIDE