nova-codex-curator schema

Reverse-engineered by reading the nova-codex-curator source directly (SQLAlchemy model classes, dataclasses, OpenSearch index-creation code, and S3/YAML/JSON structures) rather than authored by the team that owns this service. Review, if needed, happens via PR against this file like any other schema change.

Derived from:

• curator/pipelines/servicenow_kb/db.py — the only real relational schema (SQLAlchemy DeclarativeBase models against a PostgreSQL "global tenant catalog", read via psycopg2/asyncpg). No Alembic/migrations folder exists in the repo — the tables are declared in code only and presumably provisioned/migrated by whatever owns the shared global DB (not this repo).
• curator/clients/opensearch.py — the AWS OpenSearch k-NN vector index definition (ensure_index/create_index), which is this service's actual "document" schema (chunk + embedding + metadata).
• curator/models.py and curator/pipelines/servicenow_kb/models.py — plain @dataclass types (not ORM/DB-backed) used to move data between pipeline stages in memory; some of their fields end up as S3 object metadata and/or OpenSearch document fields, so they're documented here as the shape that feeds the persisted stores.
• curator/pipelines/crawler/gov_regulations/{config_loader,base,manifest}.py — YAML-configured crawl sources and a JSON sync-state manifest, both persisted as objects in S3 rather than in a database. Included here as "schema" because they are structured, versioned records this service reads and writes on every run.
• curator/api.py — an in-memory (non-persisted, process-local) job-status dict exposed over GET /sync/{job_id}.
• curator/config.py — Pydantic settings (CODEX_ env prefix); not schema itself but shows which of the above stores are wired up (opensearch_index, bedrock_embedding_dimension, global_database_url, s3_bucket).

There is no single unified database: this service spans a relational tenant catalog (Postgres), a search/vector index (OpenSearch), and object-store-as-record-store (S3 JSON/YAML/metadata). Each is documented as its own cluster below.

Service dependencies (per pyproject.toml / docs/architecture.md): AWS S3 (raw store), AWS OpenSearch (k-NN vector index), AWS Bedrock (Claude vision for OCR, Cohere embed-v4 for embeddings), PostgreSQL via SQLAlchemy + psycopg2/asyncpg (multi-tenant ServiceNow OAuth catalog), FastAPI/uvicorn (HTTP API), semantic-text-splitter (chunking), ServiceNow Table API (external, JWT-bearer OAuth, tenant knowledge-base source).

Diagrams

Cluster 1 — Tenant catalog (PostgreSQL, SQLAlchemy ORM)

erDiagram
    INSTANCES ||--o{ TENANTS : "has many (instances.sn_instance = tenants.instance)"
    INSTANCES ||--o{ INSTANCE_OAUTH : "has many (instances.sn_instance = instance_oauth.sn_instance)"

    INSTANCES {
        string sn_instance PK
        text private_key
        string verifier_kid
    }
    TENANTS {
        string tenant_id PK
        string tenant_name
        string instance FK
        boolean is_active
    }
    INSTANCE_OAUTH {
        string sn_instance PK_FK
        string app PK
        string client_id
        text client_secret
    }

Cluster 2 — Vector index (AWS OpenSearch)

erDiagram
    CODEX_DOCUMENTS {
        string _id PK "= chunk_id"
        text content "chunk text"
        float_vector embedding "knn_vector, dim=1024"
        object metadata "flat bag of provenance fields, see detail below"
    }

One index per "channel": the default codex-documents index (gov regulations / generic crawler / union docs), plus one index per country for gov regulations (gov_regulations_{country}, e.g. gov_regulations_us) and one index per tenant for ServiceNow KB sync (index name = tenant_id). All indices share the identical mapping shown above — OpenSearchClient is parameterized by index_name and create_index() is called with whichever name the caller passes in.

Cluster 3 — S3-persisted structured records (JSON / YAML, not a DB)

erDiagram
    COUNTRY_CONFIG ||--o{ SOURCE_CONFIG : "sources[]"
    SOURCE_CONFIG ||--o{ PART_SPEC : "parts[]"
    SOURCE_CONFIG ||--o{ SYNC_MANIFEST_ENTRY : "tracked by (source_id)"
    PART_SPEC ||--|| SYNC_MANIFEST_ENTRY : "part_id (key)"

    COUNTRY_CONFIG {
        string country PK
        string country_name
        string index_name "OpenSearch index for this country"
    }
    SOURCE_CONFIG {
        string source_id PK
        string state
        string source_name
        string handler "us_ecfr | web_scraper | ..."
        string base_url
        string crawl_method "api | http_get | sparql"
        string content_type
        string version_detection "api | http_headers | content_hash"
        json rate_limit "{max_calls, window_seconds}"
        float fetch_timeout
        string language
        json metadata_defaults
    }
    PART_SPEC {
        string part_id PK
        json spec "handler-specific locator"
    }
    SYNC_MANIFEST_ENTRY {
        string part_id PK
        string s3_key
        string content_hash
        string last_amendment_date
        string crawled_at
        string document_id
    }

Tables at a glance

Table / Model	Store	Purpose
instances	PostgreSQL (SQLAlchemy)	One row per ServiceNow instance URL: holds the RSA private key + JWT kid used to sign JWT-bearer assertions for that instance.
tenants	PostgreSQL (SQLAlchemy)	One row per tenant; links a tenant to its ServiceNow instance and whether it's active.
instance_oauth	PostgreSQL (SQLAlchemy)	Per-instance, per-app OAuth client credentials (composite key sn_instance + app); this service only reads rows where app = 'superagent_jwt'.
codex-documents (and per-country / per-tenant index variants)	AWS OpenSearch	The k-NN vector index of embedded document chunks — the actual "documents" table downstream RAG consumers query.
RawDocument (dataclass → S3 object + metadata)	S3 object metadata	In-memory representation of a freshly crawled/fetched raw document, before OCR/chunking; becomes S3 object body + x-amz-meta-* provenance headers.
DocumentChunk (dataclass)	in-memory only	A semantically split piece of a document's markdown, pre-embedding.
EmbeddedChunk (dataclass)	in-memory → OpenSearch doc	A DocumentChunk plus its embedding vector; this is what bulk_index writes into OpenSearch.
TenantSNConfig (dataclass)	in-memory, assembled from tenants+instances+instance_oauth	Resolved per-tenant ServiceNow OAuth config used by ServiceNowClient.
KBBase (dataclass)	in-memory (from ServiceNow API)	A ServiceNow knowledge base (kb_knowledge_base) — sys_id, title, description.
KBArticle (dataclass)	in-memory (from ServiceNow API) → S3 + OpenSearch	A single ServiceNow KB article, with attachments/related links/knowledge-type metadata; source record for the ServiceNow KB sync pipeline.
CountryConfig (dataclass)	S3 YAML (configs/gov_regulations/{country}.yml)	Top-level per-country crawl config: which OpenSearch index and which sources to crawl.
SourceConfig (dataclass)	S3 YAML (nested in CountryConfig)	One crawl source within a country (e.g. us-ecfr): handler, crawl method, rate limit, metadata defaults, list of parts.
PartSpec (dataclass)	S3 YAML (nested in SourceConfig)	One regulation document/part to crawl, keyed by part_id, with a handler-specific spec dict.
PartState / SyncManifest entries	S3 JSON (manifests/{country}/{source_id}-sync.json)	Incremental-sync state per part: last-seen S3 key, content hash, amendment date, crawl time, document id.
VersionInfo (dataclass)	in-memory	Per-part version/amendment info returned by a SourceHandler.get_version_info.
FetchedContent (dataclass)	in-memory	Raw bytes + metadata returned by SourceHandler.fetch_part, before it's written to S3 as a RawDocument.
SyncStats / DeletionStats (dataclass)	in-memory only	Run counters (uploaded/skipped/failed/errors, or opensearch/S3 deleted counts); not persisted beyond the process/job.
job state (_jobs dict)	in-memory (process-local, curator/api.py)	FastAPI /sync job tracker: job_id, tenant_id, status, log_lines, counts, timestamps. Lost on process restart — no persistence layer.

Table detail

instances — PostgreSQL, SQLAlchemy Instance

Field	Type	Notes
sn_instance	String (PK)	ServiceNow instance base URL, e.g. https://mycompany.service-now.com.
private_key	Text	PEM-encoded RSA private key used to sign the RS256 JWT-bearer assertion for this instance.
verifier_kid	String	kid header value ServiceNow expects on the assertion JWT.

Relationships: one Instance → many Tenant (tenants back_populates), one Instance → many InstanceOAuth (oauth_records back_populates).

tenants — PostgreSQL, SQLAlchemy Tenant

Field	Type	Notes
tenant_id	String (PK)	Tenant identifier; also used as the S3 bucket name and OpenSearch index name for that tenant's KB sync.
tenant_name	String, not null	Human-readable tenant name.
instance	String (FK → instances.sn_instance)	Which ServiceNow instance this tenant syncs from.
is_active	Boolean, default True	Only active tenants are loaded by load_tenants().

instance_oauth — PostgreSQL, SQLAlchemy InstanceOAuth

Field	Type	Notes
sn_instance	String (PK part 1, FK → instances.sn_instance)	Which instance this OAuth client belongs to.
app	String (PK part 2)	App/client name; this service filters to app = "superagent_jwt" only — other rows (other apps' OAuth clients) are presumed to coexist in the same table but are out of scope for this service.
client_id	String	OAuth client id used as JWT iss/aud.
client_secret	Text	OAuth client secret. Stored possibly Node.js AES-256-CBC-encrypted (iv:ciphertext format, detected by presence of a :); decrypted at read time via decrypt_node_format() using Settings.encryption_key.

Composite primary key: (sn_instance, app). This table is a shared global catalog — the comment in db.py says tenant config is loaded "from the global PostgreSQL DB", implying this Postgres instance/schema is owned and populated by another service (likely the Superagent platform side), not by nova-codex-curator itself; this repo only reads it.

codex-documents (OpenSearch index; default name codex-documents, overridable per country/tenant)

Field	Type	Notes
_id	keyword (doc id)	Set to chunk.chunk_id ({document_id}#chunk_{seq}) on bulk index.
content	text	The chunk's raw markdown/text content.
embedding	knn_vector, dimension 1024 (from Settings.bedrock_embedding_dimension)	HNSW method, space_type: innerproduct, engine: faiss. Produced by AWS Bedrock Cohere embed-v4 (Settings.bedrock_embedding_model_id).
metadata.chunk_id	keyword	Same value as _id.
metadata.document_id	keyword	SHA-256-derived id of the source document; used for "does this document already exist" checks and for delete-by-document.
metadata.article_number	keyword	ServiceNow KB article number (KB sync path only).
metadata.article_sys_id	keyword	ServiceNow KB article sys_id (KB sync path only).
metadata.source_url	keyword	Original URL the content was fetched from.
metadata.source_domain	keyword	Domain used for the raw/{domain}/… S3 prefix and for delete-by-domain.
metadata.filename	keyword	Original filename, where applicable.
metadata.page_count	integer	PDF page count, where applicable.
metadata.agency	keyword	Issuing agency (gov-regulations path).
metadata.jurisdiction	keyword	e.g. "US Federal".
metadata.country	keyword	Country code (gov-regulations path).
metadata.state	keyword	Optional state/province filter.
metadata.language	keyword	ISO language code, default en.
metadata.publication_date	keyword	Source publication/amendment date.
metadata.crawled_at	keyword	ISO timestamp of crawl/sync.
metadata.kb_type	keyword	ServiceNow knowledge_types "type" field, remapped from type.
metadata.kb_type_sys_id	keyword	ServiceNow knowledge_types "type_sys_id" field, remapped from type_sys_id.
metadata.description	text	Free-text description, where present.
metadata.agent_policy	boolean	Flag surfaced from ServiceNow KB metadata (meaning not confirmed — see Open Questions).
metadata.cfr_title	keyword	eCFR title number (gov-regulations / us_ecfr handler).
metadata.cfr_part	keyword	eCFR part number (gov-regulations / us_ecfr handler).

Note: the mapping declares a fixed metadata property list, but bulk_index()/_sync_article() actually write metadata as {**base, **chunk.metadata} / an arbitrary dict — i.e. additional ad-hoc keys (e.g. tenant_id, kb_sys_id, kb_title, article_title, category, chunk_index, total_chunks, synced_at) are written into metadata at index time even though they aren't declared in the explicit mapping above. OpenSearch will dynamically map those extra fields (unless dynamic mapping is otherwise restricted), so the effective document shape is broader than the declared mapping — see Open Questions.

Index settings: knn: true, number_of_shards: 1, number_of_replicas: 0 (same for every index/tenant/country variant — no sharding-for-scale strategy per index).

RawDocument (dataclass — curator/models.py)

Field	Type	Notes
s3_key	str	Where the raw bytes live in S3.
source_url	str	Original fetch URL.
source_domain	str	Controls raw/{domain}/… S3 prefix.
content_type	str	MIME type.
content_hash	str	Used for change detection.
crawled_at	str	ISO timestamp, defaults to "now" at construction.
country	str	Default "".
state	str	Default "".
filename	str	Default "".
page_count	int	Default 0.
agency	str	Default "".
jurisdiction	str	Default "".
publication_date	str	Default "".
language	str	Default "en".

Not a DB row — this shape is what ends up as S3 object metadata (x-amz-meta-*) on the raw object, per the s3_meta dict built in _sync_article() and the crawler's upload path.

DocumentChunk (dataclass — curator/models.py)

Field	Type	Notes
chunk_id	str	Format {document_id}#chunk_{seq}.
document_id	str	SHA-256 of full document content (or ServiceNow article sys_id for KB sync).
content	str	Chunk text.
metadata	dict[str, str \| int]	Arbitrary provenance bag, merged into the OpenSearch metadata object at index time.

EmbeddedChunk (dataclass — curator/models.py)

Field	Type	Notes
chunk_id	str	Same as DocumentChunk.chunk_id; becomes the OpenSearch _id.
document_id	str	Same as DocumentChunk.document_id.
content	str	Chunk text — becomes OpenSearch content.
embedding	list[float]	Length 1024 (Cohere embed-v4) — becomes OpenSearch embedding.
metadata	dict[str, str \| int]	Merged with {chunk_id, document_id} to form the OpenSearch metadata object.

TenantSNConfig (dataclass — curator/pipelines/servicenow_kb/models.py)

Field	Type	Notes
tenant_id	str	From tenants.tenant_id.
tenant_name	str	From tenants.tenant_name.
sn_instance	str	From instances.sn_instance, e.g. https://mycompany.service-now.com.
client_id	str	From instance_oauth.client_id (app=superagent_jwt).
client_secret	str	Decrypted from instance_oauth.client_secret.
private_key	str	PEM RSA key from instances.private_key.
verifier_kid	str	From instances.verifier_kid.
user_id	str	Default ""; ServiceNow sys_id used as JWT sub, optionally overridden per sync run.

This is an in-memory join of tenants ⋈ instances ⋈ instance_oauth — not a table itself, but documented because it's the shape the rest of the pipeline (ServiceNow client, syncer) actually consumes.

KBBase (dataclass — ServiceNow KB, external source)

Field	Type	Notes
sys_id	str	ServiceNow sys_id of the knowledge base (kb_knowledge_base table, external).
title	str	KB title.
description	str	Default "".

KBArticle (dataclass — ServiceNow KB, external source)

Field	Type	Notes
sys_id	str	ServiceNow article sys_id.
number	str	ServiceNow KB article number (e.g. KB0012345); used as filename stem and OpenSearch article_number.
title	str	Article title.
text_html	str	Raw HTML body — source for OCR/markdown conversion.
sys_updated_on	str	Last-updated timestamp; used as publication_date downstream and as part of the content hash.
kb_sys_id	str	Parent KB's sys_id.
kb_title	str	Parent KB's title.
category	str	Default "".
workflow_state	str	Default "" — ServiceNow publish/workflow state (e.g. draft/published), not further enumerated in code.
source_url	str	Default "".
attachments	list[dict[str, Any]]	Each dict has (at least) file_name, content_type, sys_id, per usage in syncer.py.
related_links	list[str]	Default empty list; not otherwise consumed in the code read.
knowledge_types	dict[str, Any]	Keys observed: type (remapped to kb_type), type_sys_id (remapped to kb_type_sys_id); other keys pass through as-is into S3 metadata / chunk metadata.

CountryConfig / SourceConfig / PartSpec (dataclasses — S3 YAML, configs/gov_regulations/{country}.yml)

CountryConfig

Field	Type	Notes
country	str	Country code, e.g. us. Also the S3 config key stem.
country_name	str	Default "".
index_name	str	OpenSearch index for this country; defaults to gov_regulations_{country} if omitted.
sources	list[SourceConfig]	One or more crawl sources.

SourceConfig

Field	Type	Notes
source_id	str	Unique within country; also the manifest namespace.
state	str	Optional state/province filter, default "".
source_name	str	Default "".
handler	str (enum-like)	Maps to a handler registry key — observed values: us_ecfr, web_scraper.
base_url	str	Handler endpoint base.
crawl_method	str (enum-like)	"api" \| "http_get" \| "sparql".
content_type	str	MIME type, default "text/html".
version_detection	str (enum-like)	"api" \| "http_headers" \| "content_hash", default "content_hash".
rate_limit	dict[str, Any]	{max_calls: int, window_seconds: int}, default {30, 60}.
fetch_timeout	float	Default 120.0.
language	str	Default "en".
metadata_defaults	dict[str, str]	Observed keys: source_domain, agency, jurisdiction; merged into RawDocument/S3 metadata.
parts	list[PartSpec]	The documents/parts to crawl for this source.

PartSpec (frozen dataclass, curator/pipelines/crawler/gov_regulations/base.py)

Field	Type	Notes
part_id	str (PK-like)	Unique key within the source, e.g. title-29/part-541; also the manifest entry key and raw filename stem.
spec	dict[str, Any]	Handler-specific locator. eCFR: {title: int, part: str}. Generic web_scraper: {url_path: str}.

VersionInfo / FetchedContent / SyncStats (dataclasses — base.py, crawler-internal, in-memory only)

Model	Field	Type	Notes
VersionInfo	part_id	str	Matches PartSpec.part_id.
VersionInfo	amendment_date	str	Default ""; empty forces a re-fetch.
VersionInfo	extra	dict[str,str]	Handler-specific extra version data.
FetchedContent	part_id	str
FetchedContent	body	bytes	Raw downloaded content.
FetchedContent	content_type	str
FetchedContent	source_url	str
FetchedContent	extra_metadata	dict[str,str]
SyncStats (crawler + servicenow_kb, two separate identical-shaped classes)	uploaded, skipped, failed	int	Run counters.
SyncStats	errors	list[str]	Error messages.

PartState / SyncManifest entries (S3 JSON — manifests/{country}/{source_id}-sync.json)

Field	Type	Notes
s3_key (map key: part_id)	str	Where this part's raw content lives in S3.
content_hash	str	Content hash at last sync.
last_amendment_date	str	Used to decide whether a re-fetch is needed (needs_update()).
crawled_at	str	ISO timestamp of last crawl.
document_id	str	Document id (matches RawDocument/chunk document_id).

Stored as a flat JSON object keyed by part_id → PartState fields (see example in docs/architecture.md). Not a table; one manifest object per (country, source_id) pair in S3.

_jobs — in-memory job-status dict (curator/api.py, process-local only)

Field	Type	Notes
job_id	str (PK)	UUID4, generated per POST /sync call.
tenant_id	str	Request's tenant.
user_sys_id	str	Request's user_sys_id.
status	str (enum-like)	"running" \| "completed" \| "failed".
log_lines	list[str]	Capped at 2000 entries (oldest evicted).
uploaded / skipped / failed	int	Copied from SyncStats on completion.
errors	list[str]	Copied from SyncStats.errors, or the top-level exception message on crash.
started_at / finished_at	str | None	ISO timestamps.

No persistence: this dict lives in a single global dict guarded by a threading.Lock, lost on process restart, and not shared across replicas — worth flagging if this service is expected to run with >1 replica or survive restarts mid-job (see Open Questions).

Relationships (informal)

instances (sn_instance) ──< tenants (instance)
instances (sn_instance) ──< instance_oauth (sn_instance, app='superagent_jwt')

tenants ⋈ instances ⋈ instance_oauth   →  TenantSNConfig (in-memory join, per active tenant)
                                            │
                                            ▼
                                   ServiceNowClient (external ServiceNow API)
                                            │
                                       KBBase / KBArticle (external, fetched)
                                            │
                              ┌─────────────┼───────────────────────┐
                              ▼             ▼                       ▼
                        S3 raw object   chunk_document()      (per-tenant OpenSearch
                     (raw/servicenow/…)   → DocumentChunk        index = tenant_id)
                                            → embed()
                                            → EmbeddedChunk
                                            → bulk_index() ─────────┘

CountryConfig (S3 YAML) ──< SourceConfig ──< PartSpec
                                  │              │
                                  ▼              ▼
                          SourceHandler.get_version_info / fetch_part
                                  │              │
                                  ▼              ▼
                          VersionInfo      FetchedContent
                                  │              │
                                  └──────┬───────┘
                                         ▼
                                 SyncManifest / PartState  (S3 JSON, per country+source_id)
                                         │
                                         ▼
                             RawDocument → S3 (raw/{domain}/{part_id}.{ext})
                                         │
                                         ▼
                     ingestion: OCR → chunk_document() → DocumentChunk
                                         → embed() → EmbeddedChunk
                                         → bulk_index() → OpenSearch (index_name from CountryConfig, e.g. gov_regulations_us)

Deletion pipeline: document_id | source_url | source_domain  →  OpenSearch delete_by_query
                                                              →  (optional) S3 delete_many by prefix

FastAPI /sync  →  spawns thread  →  run_sync()  →  writes counters into _jobs[job_id] (process-local, not persisted)

Open questions / things to confirm before treating this as the reviewed contract

1. No migrations for the Postgres tables. instances / tenants / instance_oauth are declared as SQLAlchemy models in this repo, but there is no Alembic/migrations/ directory here — meaning either (a) this repo only reads a schema owned/migrated by another service, or (b) migrations live somewhere not found in this checkout. Confirm which service owns provisioning this Postgres schema (likely a Superagent platform-side repo), since nova-codex-curator's model classes could silently drift from the real DDL.
2. instance_oauth.app values beyond superagent_jwt. The table clearly supports multiple OAuth "apps" per instance (composite PK includes app), but only superagent_jwt is read here — unclear what other app values exist or who else writes/reads this table.
3. OpenSearch mapping vs. actual indexed fields mismatch. create_index() declares a fixed set of metadata.* sub-fields, but bulk_index()/ _sync_article() write additional keys (e.g. tenant_id, kb_sys_id, kb_title, article_title, category, chunk_index, total_chunks, synced_at) that aren't in the declared mapping. Confirm whether dynamic mapping is intentionally relied on here, or whether the declared mapping is stale/incomplete.
4. Meaning of metadata.agent_policy (boolean). It's declared in the OpenSearch mapping but no code path in this repo sets or reads it — likely populated by a ServiceNow knowledge_types key not seen in the sample data, or set by a consumer outside this repo. Needs confirmation from whoever owns the knowledge_types contract in ServiceNow.
5. KBArticle.workflow_state and KBBase fields aren't enumerated. ServiceNow workflow states (e.g. draft/published/retired) aren't constrained to a known set anywhere in this codebase — treated as a free string. Confirm the actual choice list against the ServiceNow KB app (likely nova-superagent-sn-app or similar) if filtering by state matters downstream.
6. In-memory-only job state (_jobs in curator/api.py). Not persisted to any database; lost on restart and not shared across replicas/processes. Confirm whether this is acceptable for the deployed topology (single long-lived process vs. multiple replicas/autoscaling), since POST /sync dedup-by-tenant and GET /sync/{job_id} status lookups both rely on this process-local dict.
7. Vector-DB choice differs from the PRD assumption in the task framing. This service uses AWS OpenSearch k-NN, not pgvector/Chroma/ Pinecone/Weaviate — confirmed directly from curator/clients/opensearch.py and pyproject.toml (opensearch-py). No pgvector or other vector-DB client library is present in dependencies.
8. S3 "schema" (manifests, configs, raw prefixes) has no schema versioning. SyncManifest/PartState and the YAML SourceConfig are parsed with dataclass(**dict)/yaml.safe_load with no version field, no validation beyond required-key KeyErrors, and no migration story if the shape changes — a stale S3 manifest from an older code version could load incorrectly (e.g. via PartState(**val) if fields are added/removed). Worth flagging as a forward-compat risk, not confirmed as a design intent.
9. Legacy single-tenant ServiceNow settings (servicenow_instance, servicenow_username, servicenow_password, servicenow_kb_id in curator/config.py) are marked "kept for backward compat" but no code path reading them was found in the files inspected — confirm whether they're dead config or used by a code path not covered in this pass (e.g. a legacy CLI entry point).