PostgreSQL is the control plane of ISP. It stores all metadata about sources, connector configurations, extension code, ingest status, and deletion history. It does NOT store document content (that lives in Typesense).
Host: localhost (external) / isp-db (Docker internal)
Port: 5432
Database: isp
User: isp
Password: isp-dev-password
DSN: postgresql://isp:isp-dev-password@isp-db:5432/isp
Access via DBeaver: connect to localhost:5432 with above credentials.
indexesStores one row per ISP source. This is the primary metadata table.
CREATE TABLE indexes (
id VARCHAR(12) PRIMARY KEY, -- uuid4.hex[:12]
name VARCHAR(80) NOT NULL, -- display name
description TEXT DEFAULT '',
created_at VARCHAR(30) NOT NULL, -- ISO8601 UTC
doc_count INTEGER DEFAULT 0, -- synced from Typesense
last_ingested VARCHAR(30), -- ISO8601 UTC
status VARCHAR(20) DEFAULT 'ready',
connectors JSONB DEFAULT '[]', -- REST/sitemap config array
collection_name VARCHAR(200) DEFAULT '', -- isp_{id} for managed, '' for external
is_external BOOLEAN DEFAULT FALSE, -- pre-existing Typesense collection
external_collection VARCHAR(200) DEFAULT '', -- e.g. "catalog"
external_filter VARCHAR(500) DEFAULT '', -- e.g. "type:=device"
external_query_by VARCHAR(100) DEFAULT 'display',
external_title_field VARCHAR(100) DEFAULT 'display',
external_url_field TEXT DEFAULT '', -- deprecated; URL now owned by extension
-- Added via ALTER TABLE on existing deployments:
embed_model VARCHAR(50) DEFAULT '', -- per-index embedding model override ('' = inherit global)
version_boost BOOLEAN DEFAULT FALSE, -- recency score bump for new-badge items
intent_label TEXT DEFAULT '', -- PLP tab this index maps to (e.g. 'device', 'tablet')
slug VARCHAR(120) DEFAULT '' -- human-readable URL alias (e.g. 'tablets')
);
-- Partial unique index: only non-empty slugs are constrained, allowing '' during the
-- init_db → seed_slugs startup window before back-fill completes.
CREATE UNIQUE INDEX indexes_slug_unique ON indexes (slug) WHERE slug <> '';
connectors JSONB examples:
REST connector (saved after first ingest/rebuild):
[{
"type": "rest",
"url": "https://api.example.com/products",
"method": "POST",
"headers": {"Authorization": "Bearer TOKEN"},
"body": "{\"category\": \"phones\"}",
"result_path": "",
"id_field": "id",
"title_field": "title",
"content_field": "description",
"url_field": "url",
"field_map": {"productName": "title", "msrp": "price"}
}]
External connector (auto-created by discover):
[{
"type": "external",
"url": "https://www.example.com/cell-phone/",
"collection": "catalog"
}]
ingest_statusTracks the current state of an ingest or rebuild job. One row per index, upserted in place.
CREATE TABLE ingest_status (
index_id VARCHAR(12) PRIMARY KEY,
state VARCHAR(20) DEFAULT 'idle', -- idle|queued|running|done|error
total INTEGER DEFAULT 0, -- total docs fetched
indexed INTEGER DEFAULT 0, -- docs successfully upserted
error TEXT DEFAULT '' -- error message if state=error
);
State transitions:
idle → queued → running → done
→ error
deleted_splitsTracks external sources the user explicitly deleted. Prevents discover from re-adding them on page load.
CREATE TABLE deleted_splits (
split_key TEXT PRIMARY KEY -- format: "{collection}:{filter}"
);
Examples:
catalog:type:=device ← Phones deleted
catalog:type:=accessory ← Accessories deleted
catalog: ← Entire catalog deleted (no filter)
extensionsStores the global JS extension library. Each row is either a per-item transform(doc) function or a full-response pre_process(response) function, distinguished by the type column.
CREATE TABLE extensions (
id VARCHAR(12) PRIMARY KEY, -- uuid4.hex[:12]
name VARCHAR(120) NOT NULL, -- human-readable, used to match file-based seeds
description TEXT DEFAULT '',
code TEXT NOT NULL DEFAULT '', -- full JS source
type VARCHAR(20) NOT NULL DEFAULT 'transform', -- 'transform' | 'pre_processor'
created_at VARCHAR(30) NOT NULL,
updated_at VARCHAR(30) NOT NULL
);
type values:
| Value | Function signature | When it runs |
|---|---|---|
transform |
function transform(doc) { … return doc; } |
Per item, after result_path extraction |
pre_processor |
function pre_process(response) { … return response; } |
Once on the full raw API response, before result_path extraction |
Extensions are seeded from backend/extensions/*.js on every startup. The // @name, // @description, and // @type comment headers are parsed to populate the corresponding columns. If an extension with the same name already exists it is updated (code sync), so the file is always the source of truth.
Pre-processor type extensions are never auto-assigned at startup — they must be assigned manually via the Extensions tab in the UI.
index_extensionsMany-to-many join between indexes and extensions. Controls which extensions run on which source, in what order, and at which stage.
CREATE TABLE index_extensions (
index_id VARCHAR(12) NOT NULL REFERENCES indexes(id) ON DELETE CASCADE,
extension_id VARCHAR(12) NOT NULL REFERENCES extensions(id) ON DELETE CASCADE,
run_order INTEGER NOT NULL DEFAULT 0, -- lower = runs first within the same stage
enabled BOOLEAN NOT NULL DEFAULT TRUE,
PRIMARY KEY (index_id, extension_id)
);
assign_extension_if_new uses ON CONFLICT DO NOTHING so startup seeding never overrides user-configured run_order or enabled state.assign_extension (full upsert) is used only when the user explicitly re-assigns via the UI.type at runtime: pre_processor extensions run on the raw response first, then transform extensions run per item.search_eventsStores one row per search impression or click. Powers the analytics-driven popularity feedback loop.
CREATE TABLE search_events (
id BIGSERIAL PRIMARY KEY,
index_id VARCHAR(12) REFERENCES indexes(id) ON DELETE CASCADE,
session_id VARCHAR(64), -- anonymous ephemeral token, no PII
query TEXT, -- search query text
doc_id VARCHAR(128), -- Typesense document id
rank INTEGER, -- 0-based position in result list
clicked BOOLEAN DEFAULT FALSE,
source VARCHAR(32), -- "plp" | "mgmt_ui"
created_at BIGINT -- Unix timestamp ms
);
CREATE INDEX search_events_index_doc_created ON search_events (index_id, doc_id, created_at);
CREATE INDEX search_events_created ON search_events (created_at);
Retention: raw events are kept for 90 days. Call DELETE FROM search_events WHERE created_at < (EXTRACT(EPOCH FROM NOW()) - 90*86400)*1000 to prune, or use POST /v1/analytics/aggregate to aggregate then let old rows age out.
Popularity write-back: the aggregation job reads click events, computes position-discounted CTR per document, normalises to 0–1,000,000, and updates the popularity field in Typesense directly — no re-embedding, no rebuild required. The updated score takes effect on the next search.
slug is a URL-safe alias for an index. It is generated from the display name at creation time using these rules:
[a-z0-9-]Examples: "TABLETS" → "tablets", "My Products v2" → "my-products-v2".
If the generated slug is already taken by another index, the first 4 hex chars of the index ID are appended as a disambiguator (e.g. tablets-9ed5).
Startup back-fill: on every startup seed_slugs() runs after init_db() and assigns slugs to any indexes that have an empty slug. This means existing deployments receive slugs automatically on the first restart after upgrading.
API resolution: all routes that accept {id} in the path call _resolve_index(id_or_slug), which tries slug lookup first and falls back to hex ID. Both URL forms always work:
/v1/indexes/tablets/search/public ← slug form
/v1/indexes/9ed56100f220/search/public ← hex ID form (still valid)
You can pass slug when creating an index via POST /v1/indexes. If omitted, it is auto-generated.
intent_label maps an index to a PLP tab identifier (e.g. device, accessory, smartwatch, tablet). The /v1/intent/tab endpoint reads this column at request time — there are no hardcoded collection IDs or keywords in the code. Adding a new tab is as simple as setting intent_label on an index and the intent detection self-configures. See Tab Intent Detection for the scoring algorithm.
PostgreSQL
┌─────────────────────────────────┐
Create Source │ INSERT INTO indexes │
─────────────► │ (name, description, created_at) │
└─────────────────────────────────┘
┌─────────────────────────────────┐
Start Ingest │ UPDATE indexes │
─────────────► │ SET connectors = [cfg] │
│ INSERT INTO ingest_status │
│ SET state = 'queued' │
└─────────────────────────────────┘
┌─────────────────────────────────┐
Ingest Done │ UPDATE indexes │
─────────────► │ SET doc_count = N │
│ UPDATE ingest_status │
│ SET state = 'done', indexed = N │
└─────────────────────────────────┘
┌─────────────────────────────────┐
Delete Source │ DELETE FROM indexes │
─────────────► │ CASCADE: index_extensions │
│ DELETE FROM ingest_status │
│ INSERT INTO deleted_splits │
└─────────────────────────────────┘
┌─────────────────────────────────┐
Startup Seed │ UPSERT INTO extensions │
─────────────► │ (from backend/extensions/*.js) │
│ INSERT INTO index_extensions │
│ ON CONFLICT DO NOTHING │
└─────────────────────────────────┘
The backend uses asyncpg with a connection pool (min 2, max 10 connections):
_pool = await asyncpg.create_pool(
settings.database_url,
min_size=2,
max_size=10
)
Tables are created on startup via init_db() called from FastAPI’s lifespan handler — no separate migration tool is needed for this schema. All CREATE TABLE statements use IF NOT EXISTS.
-- List all sources with slugs, intent labels, and doc counts
SELECT id, name, slug, intent_label, doc_count, is_external
FROM indexes ORDER BY created_at DESC;
-- Look up an index by slug (same as API resolution)
SELECT * FROM indexes WHERE slug = 'tablets';
-- Find indexes that still have no slug (should be empty after first startup)
SELECT id, name FROM indexes WHERE slug = '' OR slug IS NULL;
-- Check ingest status for all sources
SELECT i.name, s.state, s.indexed, s.total, s.error
FROM ingest_status s JOIN indexes i ON i.id = s.index_id;
-- See what has been deleted
SELECT * FROM deleted_splits;
-- View connector config for a source
SELECT name, connectors FROM indexes WHERE name = 'Phones';
-- List all extensions
SELECT id, name, updated_at FROM extensions ORDER BY created_at DESC;
-- See which extensions are assigned to which indexes
SELECT i.name AS index_name, e.name AS extension_name, ie.run_order, ie.enabled
FROM index_extensions ie
JOIN indexes i ON i.id = ie.index_id
JOIN extensions e ON e.id = ie.extension_id
ORDER BY i.name, ie.run_order;
-- Count total events and clicks per index (last 30 days)
SELECT i.name, i.slug,
COUNT(*) AS impressions,
COUNT(*) FILTER (WHERE e.clicked) AS clicks,
ROUND(COUNT(*) FILTER (WHERE e.clicked)::numeric / NULLIF(COUNT(*),0) * 100, 1) AS ctr_pct
FROM search_events e JOIN indexes i ON i.id = e.index_id
WHERE e.created_at >= (EXTRACT(EPOCH FROM NOW()) - 30*86400) * 1000
GROUP BY i.name, i.slug ORDER BY impressions DESC;
-- Top clicked documents for a specific index
SELECT doc_id, COUNT(*) AS clicks
FROM search_events
WHERE index_id = '9ed56100f220' AND clicked = TRUE
GROUP BY doc_id ORDER BY clicks DESC LIMIT 10;
-- Low-CTR queries (impressions but zero clicks) for an index
SELECT query, COUNT(*) AS impressions
FROM search_events
WHERE index_id = '9ed56100f220' AND clicked = FALSE
AND query NOT IN (
SELECT DISTINCT query FROM search_events
WHERE index_id = '9ed56100f220' AND clicked = TRUE
)
GROUP BY query ORDER BY impressions DESC LIMIT 10;
Citations below are sourced from training knowledge (not live browsing). DOI and arXiv links are provided where known.
Stonebraker, M., & Rowe, L. A. (1986). “The Design of Postgres.” Proceedings of the ACM SIGMOD International Conference on Management of Data, pp. 340–355. DOI: 10.1145/16894.16914 — Original Postgres design paper; PostgreSQL 16 is the direct descendant. ISP uses it as the control-plane metadata store (indexes, connectors, extensions, analytics events).
Momjian, B. (2001). PostgreSQL: Introduction and Concepts. Addison-Wesley. ISBN: 0-201-70331-9 — Reference for PostgreSQL JSONB semantics; ISP stores connector configs and metadata blobs as JSONB (binary JSON with indexing support).
Ousterhout, J. K. (1998). “Why Threads Are a Bad Idea (for Most Purposes).” Proceedings of USENIX 1996 Technical Conference. — Motivates asyncpg’s event-loop model: async I/O avoids thread overhead; connection pool (min=2, max=10) matches database concurrency limits.
Hintjens, P. (2013). ZeroMQ: Messaging for Many Applications. O’Reilly. ISBN: 978-1-449-33406-2 — Background on async message-passing patterns; relevant to ISP’s asyncio.BackgroundTasks model for ingest jobs decoupled from HTTP request lifecycle.
Joachims, T. (2002). “Optimizing Search Engines Using Clickthrough Data.” Proceedings of KDD 2002, pp. 133–142. DOI: 10.1145/775047.775067 — Motivates the search_events table schema: impression + click events are the minimal signals needed for CTR-based popularity learning.
Craswell, N., Zoeter, O., Taylor, M., & Ramsey, B. (2008). “An Experimental Comparison of Click Position-Bias Models.” Proceedings of WSDM 2008, pp. 87–94. DOI: 10.1145/1341531.1341545 — Position-bias models; informs the 90-day event retention window and the position-weighted aggregation in the analytics service.