How to load profiles into SQL

The [sql] extra provides an optional relational representation of a profile, for consumers who want profiles in a SQL database (for example Postgres) rather than reading the on-disk directory layout.

The tables

researcher_profiles.db defines three SQLModel tables mirroring the on-disk schema:

Table	Class	One row per
`profiles`	`ProfileRow`	profile (primary key: `slug`)
`papers`	`PaperRow`	`sources/papers.yaml` entry
`expertise_topics`	`ExpertiseTopicRow`	`profile.yaml` `expertise` label

papers and expertise_topics reference the owning profile by profile_slug, a foreign key onto profiles.slug. See the Python API reference for the columns.

Create the tables

create_all creates every table on the engine if it is not already present. It works against any SQLAlchemy-supported backend:

from sqlmodel import create_engine
from researcher_profiles.db import create_all

engine = create_engine("postgresql://user@host/db")
create_all(engine)

Load a profile

rows_for_profile builds all rows (the profile row, one row per paper, one row per expertise topic) for a single profile. Add them in a session and commit:

from sqlmodel import Session
from researcher_profiles import ResearcherProfile
from researcher_profiles.db import rows_for_profile

p = ResearcherProfile.from_files("path/to/jane-doe")

with Session(engine) as s:
    s.add_all(rows_for_profile(p.slug, p.metadata, p.papers))
    s.commit()

To load a whole directory of profiles, iterate a ProfileRegistry and call rows_for_profile for each.

Query the rows

Query with the SQLModel/SQLAlchemy select API:

from sqlmodel import Session, select
from researcher_profiles.db import ProfileRow, PaperRow

with Session(engine) as s:
    for row in s.exec(select(ProfileRow)).all():
        print(row.slug, row.name, row.orcid)

    smith_papers = s.exec(
        select(PaperRow).where(PaperRow.profile_slug == "jane-doe")
    ).all()
    print(len(smith_papers), "papers")

Notes

List-valued metadata (subfields, interests, artifacts, anchor) is stored as JSON columns on ProfileRow, so it round-trips on any backend.
The relational form is a projection, not a full copy: it carries scalar and list metadata plus a subset of paper fields. The on-disk directory remains the authoritative representation.
Nothing else in the package imports researcher_profiles.db, so a non-SQL install never pulls in SQLModel.