dbctx

package module
v0.1.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Aug 11, 2026 License: MIT Imports: 11 Imported by: 0

README

dbctx

Go Reference Go Report Card

Compile a PostgreSQL database into compact, queryable context.

dbctx is a Go library and CLI tool that compiles a PostgreSQL database into a portable, queryable context index (.dtx file). It extracts schema, relationships, field semantics, representative values, JSONB structure, and builds a full-text search index — all without requiring an LLM, embeddings, or external services.

Use it to give text-to-SQL systems, AI agents, and database-aware applications a compact, relevant slice of your database schema at query time, instead of dumping the entire information_schema into every prompt.

Key features:

  • Natural-language query — find relevant tables, columns, and relationships from a text question
  • JSONB intelligence — discover paths, types, and representative values inside JSONB columns
  • State/categorical detection — identify implicit enums (status, plan, role) with their values
  • FK expansion — automatically include related tables through foreign-key graph traversal
  • Compact text output — LLM-ready schema notation with notation legend, optimized for token budget
  • Portable .dtx format — SQLite-based, ship/cache/version/inspect it like any file
  • Zero LLM dependency — deterministic introspection, statistics, and heuristics only
  • Go library API — embed directly in your Go application, no subprocess needed
  • In-memory mode — ephemeral indexes for testing or ephemeral workloads
  • Web UI — built-in browser-based database explorer

It is designed to be the missing layer between a real database and systems that need to understand it:

PostgreSQL
    │
    ▼
  dbctx
    │
    ▼
 database.dtx
    │
    ├── schema
    ├── relationships
    ├── field intelligence
    ├── representative values
    ├── JSONB structure
    └── retrieval index
    │
    ▼
Text query
    │
    ▼
Relevant tables + compact schema + field context
    │
    ▼
Text-to-SQL / visualization / analytics system

Jump to:

I want to... Go to
Understand what dbctx does and why it exists Why does this need to exist?
See a quick demo Quick look
See how fast it is Performance
Use it from the command line CLI quick start
Browse the database in a web UI Web UI
Use it as a Go library in my app Library Usage
Query with natural language and get compact schema Querying the context
Understand the .dtx file format The .dtx format
See what dbctx understands about a database What dbctx understands
Understand how it works under the hood Architecture / The retrieval model
See intended use cases (text-to-SQL, agents, etc.) Intended use cases
Understand the design decisions Design principles
See the project roadmap Project status / Roadmap
Contribute or extend it Contributing

Quick look

1. Build an index
dbctx build postgres://user:pass@localhost/mydb --output mydb.dtx

(screenshot coming soon)

2. Query from the CLI
dbctx query mydb.dtx "How many failed GitHub reviews last month?"

The query finds relevant tables, surfaces JSONB structure, and highlights state-like fields — all in compact text output.

dbctx CLI query output

3. Explore in the UI
dbctx ui mydb.dtx

A local web interface for browsing everything dbctx extracted from your database.

Overview
Table details
JSONB expansion
State & categorical values
Query interface

Performance

Real-world numbers against a production PostgreSQL database with 60 tables, 758 columns, 97 foreign keys, and 677 JSONB paths.

Full build
Phase              Duration     Share
──────────────────────────────────────────
Connect              0.1ms      0.0%
Schema               2.5s      20.2%
Store                11ms      0.1%
Fields               3.2s      26.2%
JSONB                6.5s      53.1%  (4 workers, connection pool)
FTS                  49ms      0.4%
──────────────────────────────────────────
Total               ~12s          100%

JSONB analysis uses a connection pool (pgxpool, 4 connections) and a worker pool (4 goroutines) for parallel PostgreSQL queries. SQLite writes are batched in transactions.

The .dtx file is 448 KB for this database.

Query performance
Query                          Duration    Matched    Text render
──────────────────────────────────────────────────────────────────
"id"                              138ms     11 tables      270µs
"reviews"                          76ms      7 tables       47µs
"failed reviews last month"       105ms     11 tables      615µs
"revews" (fuzzy)                   81ms      6 tables       90µs
"nonexistent_xyz" (no match)        2ms      0 tables        2µs
Library benchmarks (in-memory, 4-table fixture, 3-run average)
BenchmarkQuery_Short         ~816 µs/op     38 KB/op
BenchmarkQuery_Medium        ~854 µs/op     41 KB/op
BenchmarkQuery_Fuzzy         ~660 µs/op     38 KB/op
BenchmarkMatchedText         ~4.4 µs/op    3.4 KB/op
BenchmarkMatchedTextRaw      ~3.9 µs/op    2.4 KB/op
BenchmarkAllText             ~7.8 µs/op    5.9 KB/op
BenchmarkReport              ~378 µs/op     14 KB/op
BenchmarkTables               ~28 µs/op    1.7 KB/op
BenchmarkTableDetail         ~147 µs/op     11 KB/op
BenchmarkStats                ~33 µs/op    3.2 KB/op

Key takeaways:

  • Full build completes in ~12 seconds for a real 60-table database
  • Query + text rendering completes in ~100ms — fast enough for interactive use
  • Text rendering itself is sub-millisecond — the FTS query dominates latency
  • Fuzzy search adds negligible overhead over exact match
  • The resulting .dtx is 448 KB — small enough to ship, cache, or embed

Library Usage

dbctx is a Go library that can be imported directly into your application. This is the intended integration path for text-to-SQL systems, AI agents, analytics tools, and database-aware applications.

Install

go get github.com/shrsv/dbctx

Basic usage

package main

import (
    "context"
    "fmt"
    "log"

    "github.com/shrsv/dbctx"
)

func main() {
    ctx := context.Background()

    // Build an in-memory index (no file created)
    idx, err := dbctx.Build(ctx, "postgres://localhost/mydb", nil)
    if err != nil {
        log.Fatal(err)
    }
    defer idx.Close()

    // Query with natural language
    result, err := idx.Query("failed reviews last month")
    if err != nil {
        log.Fatal(err)
    }

    // Get compact schema for matched tables only — ready for an LLM prompt
    fmt.Println(result.Matched().Text())         // includes notation legend

    // Or refine the selection
    fmt.Println(result.All().Text())                             // all tables
    fmt.Println(result.Include("reviews", "orgs").Text())        // specific tables
    fmt.Println(result.Matched().Exclude("migrations").Text())   // matched minus one

    // Use TextRaw() to omit the legend (tighter token budget)
    fmt.Println(result.Matched().TextRaw())
}

The Text() output is a compact, LLM-ready representation with a notation legend:

--- notation ---
PK: primary key           col → table  foreign key
^  is primary key         ?  nullable   >target  FK target
[state] state-like categorical (< 100 distinct values)
[cat]   categorical field
{a, b, c}  representative values (from pg_stats)
$.path  type  {samples}  JSONB path with inferred type
(score: X.XX)  relevance score from query matching

reviews  (score: 15.24)
  PK: id
  org_id → orgs
  pull_request_id → pull_requests
  status character varying(50) [state]
    {completed, failed, created, in_progress}
  metadata jsonb
    $.provider  string  {github, gitlab}
  created_at timestamp with time zone

orgs  (score: 3.12)
  PK: id
  name text
  plan text [state]
    {free, pro, enterprise}

Persist to a .dtx file

// Build and save to disk
idx, err := dbctx.Build(ctx, dsn, &dbctx.Options{
    Path:    "mydb.dtx",
    Schemas: "public,app",
})

// Later: open the existing file (read-only, no PostgreSQL needed)
idx, err := dbctx.Open("mydb.dtx")

In-memory mode

When Options.Path is empty (or opts is nil), the index lives in memory only. No files are created. This is useful for:

  • ephemeral indexes rebuilt on each startup
  • testing
  • environments where file I/O is undesirable
idx, _ := dbctx.Build(ctx, dsn, nil) // in-memory, no .dtx file

In-memory indexes are faster to build (no disk I/O) but must be rebuilt each time the process starts.

Non-blocking startup

For applications that need database context available at startup without blocking the main thread, use BuildAsync. It starts the build in a background goroutine and returns immediately. Queries made before the build completes will block until the index is ready.

This pattern is useful for binary startup where you want to begin serving requests immediately while the index builds in the background:

func main() {
    ctx := context.Background()

    // Start building in background — returns immediately
    idx, ready, err := dbctx.BuildAsync(ctx, dsn, nil)
    if err != nil {
        log.Fatal(err)
    }
    defer idx.Close()

    // Register idx with your application server, handlers, etc.
    // The index is safe to pass around even before the build completes.

    // Start serving HTTP immediately
    http.HandleFunc("/query", func(w http.ResponseWriter, r *http.Request) {
        // This call blocks automatically if the index isn't ready yet
        result, err := idx.Query(r.URL.Query().Get("q"))
        if err != nil {
            http.Error(w, err.Error(), 500)
            return
        }
        // Return compact text of matched tables
        w.Header().Set("Content-Type", "text/plain")
        w.Write([]byte(result.Matched().Text()))
    })

    // Log when the index is ready
    go func() {
        <-ready
        log.Println("dbctx index is ready")
    }()

    log.Println("server starting on :8080 (index building in background)")
    http.ListenAndServe(":8080", nil)
}

For a non-blocking readiness check instead of waiting:

select {
case <-idx.Ready():
    // index is ready, serve with full context
    result, _ := idx.Query(query)
default:
    // index still building, return a fallback response
    w.Write([]byte("database context is loading, please retry"))
}

If the background build fails, idx.Err() returns the error and all query methods will return it.

Available methods

// Query — returns a ResultSet for selection and text rendering
result, _ := idx.Query("failed reviews last month")

// ResultSet — select tables and render (includes notation legend)
result.Matched().Text()                          // matched tables only (score > 0)
result.All().Text()                              // all tables including FK-expanded
result.Include("reviews", "orgs").Text()         // specific tables by name
result.Matched().Exclude("migrations").Text()    // matched minus exclusions
result.Matched().Include("extra").Text()         // matched plus extras
result.Matched().TextRaw()                       // same as Text() but without the legend
result.Matched().Tables()                        // get []TableContext for custom logic
result.Matched().Len()                           // count of selected tables
result.TableMap()                                // map[string]TableContext for lookup

// Tables — list all tables with summary info
tables, _ := idx.Tables()

// TableDetail — full column/relationship/value detail for one table
detail, _ := idx.TableDetail("reviews")

// Stats — summary counts (tables, columns, FKs, state fields, etc.)
stats, _ := idx.Stats()

// Report — dump human-readable report to a writer
idx.Report(os.Stdout)

// Ready — channel that closes when the index is ready
<-idx.Ready()

// Err — returns build error (for async builds)
if err := idx.Err(); err != nil { ... }

// Close — release resources
idx.Close()

Full API reference

See the pkg.go.dev documentation or run go doc github.com/shrsv/dbctx locally.

Why does this need to exist?

When building a system that lets users ask questions about a database in natural language, the first problem is usually presented as:

"How do I generate SQL from a user's question?"

That is often the wrong first problem.

The harder problem is:

How do I efficiently tell the model what this database actually contains?

A real PostgreSQL database isn't just:

users(id, name, email)
orders(id, user_id, status, created_at)

It contains information that is critical for understanding queries but is absent from a conventional schema dump.

You quickly run into questions like:

How do I get a highly compressed schema?

Given a database with hundreds of tables and thousands of fields, how do I give a downstream system only the relevant 10–20 tables without dumping the entire information_schema into a prompt?

How do I discover the possible states of a field?

If I have:

reviews.status TEXT

how do I discover that the meaningful values are:

pending
running
completed
failed

without manually documenting every field?

How do I understand JSONB?

If I have:

reviews.metadata JSONB

how do I discover that it actually contains:

provider
repository.name
repository.owner
severity
automated

and that provider is usually one of:

github
gitlab
bitbucket
How do I find the right tables for a question?

Given:

"How many failed GitHub reviews did we have last month?"

how do I identify that the relevant tables are probably:

reviews
repositories

rather than sending the entire database schema to an LLM?

How do I expand the result intelligently?

If a query matches reviews, how do I automatically include related tables through foreign keys?

And how do I do all of this without an LLM?

That is the purpose of dbctx.

What is dbctx?

dbctx is a database context compiler and index.

It connects to PostgreSQL and builds a persistent .dtx file containing a compact representation of the database that is useful for downstream systems.

It captures both structural facts and derived observations.

Structural
────────────────────────────
tables
columns
types
primary keys
foreign keys
indexes
relationships

Derived
────────────────────────────
categorical fields
state-like fields
representative values
value frequencies
JSONB paths
JSONB types
JSONB representative values
field characteristics

Retrieval
────────────────────────────
table matching
field matching
value matching
foreign-key expansion
relevant-context extraction

The result is not SQL.

It is context from which SQL can be generated reliably and cheaply.

The .dtx format

The most important artifact produced by dbctx is the .dtx file.

.dtx stands for DB Context.

Instead of treating the database context as an ephemeral prompt assembled every time a query arrives, dbctx makes it a persistent artifact:

production.dtx

Conceptually:

PostgreSQL
     │
     │ introspection + observation
     ▼
production.dtx

Then:

production.dtx + user query
              │
              ▼
       relevant context
              │
              ▼
        SQL generator

This separation is intentional.

The database can be scanned and analyzed once. Query-time systems can then retrieve only the information they need.

Example

Suppose PostgreSQL contains:

reviews (
    id,
    repository_id,
    status,
    created_at,
    metadata JSONB
)

repositories (
    id,
    organization_id,
    provider
)

organizations (
    id,
    name,
    plan
)

A conventional schema extractor might give you:

reviews.metadata JSONB

That's technically correct but not particularly useful.

dbctx can derive a richer representation:

reviews
  id                uuid       PK
  repository_id     uuid       → repositories.id
  status            text       state
  created_at        timestamptz
  metadata          jsonb

reviews.status
  values:
    pending
    running
    completed
    failed

reviews.metadata
  provider          string
    values: github, gitlab, bitbucket

  severity          string
    values: low, medium, high, critical

  repository.name   string
  repository.owner  string
  automated         boolean

repositories
  id                uuid       PK
  organization_id   uuid       → organizations.id
  provider          text

organizations
  id                uuid       PK
  name              text
  plan              text
    values: free, pro, enterprise

This is much closer to what an AI system actually needs to understand the database.

Querying the context

Now give dbctx a textual query:

How many failed GitHub reviews did we have last month?

dbctx can identify candidate tables through fuzzy matching and database structure:

reviews          score: high
repositories     score: high
organizations    score: low

Then foreign-key expansion gives:

reviews
  └── repositories
        └── organizations

The resulting context might contain only:

reviews(
  id,
  repository_id → repositories.id,
  status,
  created_at,
  metadata
)

reviews.status
  {pending, running, completed, failed}

reviews.metadata.provider
  {github, gitlab, bitbucket}

repositories(
  id,
  organization_id → organizations.id,
  provider
)

repositories.provider
  {github, gitlab, bitbucket}

That compact context can then be passed to whatever generates SQL.

dbctx does not use an LLM

This is a deliberate design decision.

dbctx uses deterministic database introspection, statistics, heuristics, indexing, and relationship analysis.

It does not require:

  • an OpenAI API key
  • an embedding model
  • an inference server
  • an LLM
  • a vector database

The database context should be something you can build locally, inspect, diff, cache, ship, and reproduce.

For example:

same database state
        +
same dbctx version
        =
same context index

This makes the system substantially easier to reason about than an LLM-generated database description.

What dbctx understands

Tables and columns

dbctx extracts the PostgreSQL structure:

table
column
PostgreSQL type
nullable
default
primary key
foreign key
indexes

It preserves the relationships between objects rather than flattening everything into text.

Relationships

Foreign keys form a database graph:

users
  │
  ├── organizations
  │      │
  │      └── subscriptions
  │
  └── reviews
         │
         └── repositories

This graph is useful both for retrieval and for generating useful context.

A textual match does not have to discover every relevant table independently.

If:

reviews → repositories

and reviews is strongly matched, the related repository table can be expanded automatically.

State and categorical fields

Many real-world databases contain implicit enums:

status
state
stage
phase
type
kind
role
category
mode

even when the PostgreSQL type is merely:

TEXT

dbctx can use heuristics involving field names, cardinality, data types, value distributions, and observed values to identify likely categorical or state-like fields.

For example:

deployment.status

state-like: true

values:
  pending
  building
  deployed
  failed
  cancelled

This information is particularly valuable for questions involving:

failed
active
pending
cancelled
enterprise
premium
github
mobile
production

because those concepts often exist only as data values rather than schema declarations.

JSONB intelligence

JSONB is one of the biggest reasons dbctx exists.

A conventional schema sees:

metadata JSONB

dbctx attempts to understand what is actually inside it.

For example:

metadata JSONB

$.provider
    string
    values: github, gitlab, bitbucket

$.repository
    object

$.repository.name
    string

$.repository.owner
    string

$.labels
    array

$.labels[].name
    string

$.automated
    boolean

The representation can include observations such as:

path: $.provider
type: string
cardinality: 3
representative_values:
    github
    gitlab
    bitbucket

This gives downstream systems useful knowledge without requiring raw JSON documents to be inserted into every prompt.

Representative values

dbctx is not intended to store a copy of your database.

Instead, it maintains compact observations about fields.

For a categorical field:

status

distinct: 5

representative:
    pending
    running
    completed
    failed
    cancelled

For a high-cardinality field:

email

type: text
distinct: ~1.2M
representative:
    alice@example.com
    bob@example.com
    ...

The exact observation strategy can vary by field type.

The goal is always:

retain enough information to understand the field without turning the context index into a copy of the database.

Incremental updates

The .dtx file is designed to be incrementally updated.

A database context should not need to be rebuilt from scratch every time the database changes.

Conceptually:

database
   │
   ├── schema changed?
   │
   ├── values changed?
   │
   ├── JSONB structure changed?
   │
   └── statistics changed?
   │
   ▼
incremental update
   │
   ▼
database.dtx

This is particularly important for large production databases where:

  • schemas evolve
  • new enum-like values appear
  • JSONB structures evolve
  • tables grow continuously
  • new relationships are added

The .dtx artifact retains the accumulated context and updates the pieces that need refreshing.

The retrieval model

dbctx treats database understanding as a retrieval problem.

A query follows roughly this path:

text query
    │
    ▼
table / field / value matching
    │
    ▼
candidate tables
    │
    ▼
foreign-key expansion
    │
    ▼
relevant fields
    │
    ▼
state + categorical information
    │
    ▼
JSONB structure
    │
    ▼
compressed database context

This is intentionally separate from SQL generation.

dbctx answers:

"What part of this database does this question appear to be about?"

A downstream system answers:

"What SQL should I write against it?"

That separation is one of the central design principles of the project.

Why not just send the whole schema to an LLM?

You can.

For small databases, it often works.

It becomes increasingly unattractive as databases grow.

Imagine:

500 tables
6,000 columns
1,500 foreign keys
hundreds of JSONB fields
thousands of categorical values

Dumping all of that into every request is expensive and noisy.

More importantly, the model has to perform database retrieval and SQL generation simultaneously.

dbctx moves the first problem into a deterministic index:

Database understanding
        ↓
     dbctx
        ↓
Relevant context
        ↓
    LLM / SQL

The downstream model gets a much smaller and more relevant representation.

Why a file format?

Because database context is useful outside a single running process.

A .dtx file can potentially be:

  • generated in CI
  • cached locally
  • checked into a repository
  • versioned
  • diffed
  • inspected
  • generated during deployment
  • shared between services
  • used by multiple AI applications
  • regenerated incrementally

For example:

schema.sql
database.dtx

can become part of an application's development and deployment artifacts.

The database itself remains the source of truth.

The .dtx file is its compiled context representation.

Architecture

dbctx is deliberately small.

┌───────────────────────────────────────────┐
│                  dbctx                    │
│                                           │
│  PostgreSQL introspection                 │
│          │                                │
│          ▼                                │
│  Schema graph                             │
│          │                                │
│          ├── Field analysis               │
│          ├── Value analysis               │
│          ├── JSONB analysis               │
│          └── Relationship analysis        │
│                    │                      │
│                    ▼                      │
│              .dtx database context        │
│                    │                      │
│          ┌─────────┴─────────┐            │
│          ▼                   ▼            │
│      retrieval             export        │
│          │                   │            │
│          ▼                   ▼            │
│   candidate context     compact format   │
└───────────────────────────────────────────┘

The core implementation is intended to be a single binary.

No database server.

No separate indexing service.

No model runtime.

No external vector database.

Intended use cases

dbctx is intended to simplify building systems such as:

Text → SQL
"What was our revenue from enterprise customers last quarter?"

→ relevant tables + relationships + field context

→ SQL generation

Text → Visualization
"Show weekly failed deployments for the last six months."

→ relevant tables + state fields + time fields

→ SQL

→ chart

Natural-language analytics
"Which customers haven't used the product in 30 days?"

→ database context

→ SQL

→ answer

AI agents

Agents frequently need to discover the structure of an application's database before performing an operation.

Instead of repeatedly introspecting PostgreSQL:

agent
  ↓
dbctx
  ↓
relevant database context
Database-aware developer tools

The same context can power:

  • database explorers
  • query assistants
  • analytics interfaces
  • admin panels
  • debugging tools
  • reporting systems
  • BI applications

Design principles

1. No LLM required

The database context should be derived from observable facts and deterministic heuristics.

2. Compact over exhaustive

The objective isn't to reproduce the database.

It is to preserve the information necessary to understand it.

3. Incremental by design

A growing database should not require a complete rebuild of its context.

4. PostgreSQL first

PostgreSQL has an exceptionally rich system catalog and strong type/relationship information.

dbctx starts there.

5. The format is a first-class artifact

The .dtx format should be useful independently of the binary that produces it.

6. Retrieval before generation

Finding the relevant database context is a separate problem from generating SQL.

7. Inspectable and reproducible

Engineers should be able to understand why a particular table or field appeared in a context result.

Example workflow

Build an index:

dbctx build postgres://user:password@localhost/myapp \
    --output myapp.dtx

Update it:

dbctx update postgres://user:password@localhost/myapp \
    --index myapp.dtx

Query it:

dbctx query myapp.dtx \
    "How many failed GitHub reviews did we have last month?"

Potential output:

TABLES

reviews              0.97
repositories         0.91

RELATIONSHIPS

reviews.repository_id
    → repositories.id

FIELDS

reviews.status
    state
    {pending, running, completed, failed}

reviews.created_at
    timestamptz

reviews.metadata
    jsonb

JSONB PATHS

reviews.metadata.provider
    string
    {github, gitlab, bitbucket}

A downstream application can then construct whatever prompt or query representation it wants.

Web UI

dbctx includes a built-in web explorer for browsing the database context interactively.

dbctx ui myapp.dtx

This starts a local web server and opens the explorer in your browser.

The UI provides:

  • Overview — summary statistics at a glance (tables, columns, relationships, state fields, JSONB paths)
  • Tables — full table list with column counts, FK counts, and row estimates; click any table to explore
  • Table detail — columns with types, PK/FK tags, nullable flags, distinct counts; expandable value lists for state-like and categorical fields; JSONB path trees; clickable FK relationships for navigation
  • Query — natural language search against the context index; results ranked by relevance with collapsible detail sections for columns, values, relationships, and JSONB paths

The UI is styled after VS Code and is embedded in the binary itself — no external dependencies or build steps required.

┌──────────────────────────────────────────────────┐
│  dbctx — Database Context Explorer               │
├──────────────────────────────────────────────────┤
│  Overview  │  Tables  │  Table  │  Query         │
├─────────┬────────────────────────────────────────┤
│ sidebar │  content area                          │
│         │                                        │
│ tables  │  stats / table detail / query results  │
│ list    │                                        │
│         │  • collapsible sections                │
│         │  • expandable value lists              │
│         │  • clickable FK navigation             │
│         │  • JSONB path trees                    │
└─────────┴────────────────────────────────────────┘

What dbctx is not

dbctx is not:

  • a text-to-SQL model
  • a SQL execution engine
  • a BI platform
  • an LLM wrapper
  • a vector database
  • a replacement for PostgreSQL's system catalog
  • an attempt to infer arbitrary business logic

It is the layer underneath those systems.

             ┌──────────────────┐
             │  Visualization   │
             ├──────────────────┤
             │   Text → SQL     │
             ├──────────────────┤
             │      Agents      │
             └────────┬─────────┘
                      │
                 compact context
                      │
                ┌─────▼─────┐
                │   dbctx    │
                └─────┬─────┘
                      │
                 PostgreSQL

Project status

dbctx is currently being developed.

The initial focus is:

  • PostgreSQL schema extraction
  • table and column graph
  • primary/foreign-key relationships
  • field statistics
  • categorical/state detection
  • representative values
  • JSONB structural inference
  • .dtx file format
  • incremental updates
  • fuzzy table/field/value retrieval
  • foreign-key expansion
  • compact context export
  • stable .dtx specification
  • web UI explorer

The ambition is to keep the core small enough that the entire system can remain understandable.

Roadmap

Phase 1 — Database understanding

Build the PostgreSQL introspection layer.

tables
columns
types
PKs
FKs
indexes
Phase 2 — Data understanding

Add deterministic field analysis:

cardinality
distributions
representative values
categorical detection
state detection
Phase 3 — JSONB

Build structural inference for JSONB:

paths
types
arrays
objects
cardinality
representative values
Phase 4 — .dtx

Define a stable, versioned database context format.

Phase 5 — Retrieval

Implement:

fuzzy matching
field matching
value matching
FK expansion
context ranking
Phase 6 — Integration

Make it easy for applications to consume dbctx output for:

text → SQL
text → charts
text → analytics
AI agents
database assistants

The bigger idea

SQL generation is only one part of making databases accessible to natural language.

The system first needs to know:

What tables exist?
What do they represent?
How are they related?
Which fields matter?
What values can those fields take?
What is hidden inside JSONB?
Which tables are relevant to this question?

Only then does SQL generation become interesting.

dbctx is an attempt to make that database understanding:

deterministic, compact, incremental, portable, and reusable.

              PostgreSQL
                   │
                   ▼
             ┌──────────┐
             │  dbctx   │
             └────┬─────┘
                  │
               .dtx
                  │
        ┌─────────┼─────────┐
        ▼         ▼         ▼
      SQL       Charts    Agents
      │          │          │
      └──────────┴──────────┘
                 │
          Database-aware apps

Build the context once. Use it everywhere.

Contributing

The most interesting parts of dbctx are likely to be the heuristics and the .dtx format itself.

Contributions around:

  • PostgreSQL introspection
  • efficient incremental indexing
  • JSONB structural inference
  • categorical/state detection
  • compact representations
  • retrieval algorithms
  • .dtx format design

are especially welcome.

License

MIT License. See LICENSE for details.

Documentation

Overview

Package dbctx compiles a PostgreSQL database into a compact, queryable context index for text-to-SQL systems, AI agents, and database-aware applications.

dbctx connects to PostgreSQL, extracts schema metadata, analyzes field statistics from pg_stats, discovers JSONB structure via sampling, and builds a full-text search index — all without requiring an LLM or external services. The result is a Index that answers natural-language queries about which tables, columns, values, and relationships are relevant to a given question.

The index can be stored on disk as a portable .dtx file (SQLite) or kept entirely in memory for ephemeral use. It is safe for concurrent access from multiple goroutines.

Quick start

Build an in-memory index and query it: 

idx, err := dbctx.Build(ctx, "postgres://localhost/mydb", nil)
if err != nil {
    log.Fatal(err)
}
defer idx.Close()

result, err := idx.Query("failed reviews last month")
if err != nil {
    log.Fatal(err)
}
fmt.Println(result.Matched().Text())

The Selection.Text output is a compact, notation-annotated schema ready to pass to an LLM or text-to-SQL system: 

--- notation ---
PK: primary key           col → table  foreign key
...

reviews  (score: 15.24)
  PK: id
  org_id → orgs
  status character varying(50) [state]
    {completed, failed, created, in_progress}
  metadata jsonb
    $.provider  string  {github, gitlab}

Persisting the index

Save the index to a .dtx file for later reuse — no PostgreSQL needed: 

idx, err := dbctx.Build(ctx, dsn, &dbctx.Options{Path: "mydb.dtx"})
// ...later...
idx, err = dbctx.Open("mydb.dtx")

Non-blocking startup

For applications that need the index available without blocking startup, use BuildAsync. Queries made before the build completes will block automatically: 

idx, ready, err := dbctx.BuildAsync(ctx, dsn, nil)
if err != nil {
    log.Fatal(err)
}
defer idx.Close()
// Register idx with your application immediately...
<-ready // or: <-idx.Ready()

Selection API

Query results can be filtered and rendered in several ways: 

result, _ := idx.Query("failed reviews")
result.Matched().Text()                          // matched tables with legend
result.Matched().TextRaw()                       // matched tables, no legend
result.All().Text()                              // all tables including FK-expanded
result.Include("reviews", "orgs").Text()         // specific tables
result.Matched().Exclude("migrations").Text()    // matched minus exclusions

Use cases

dbctx is designed for any system that needs to understand a PostgreSQL database at query time: text-to-SQL generation, natural-language analytics, AI agents, database explorers, BI tools, and developer assistants. It replaces repeated full-schema dumps with a deterministic, queryable index.

Example

This example demonstrates building an in-memory index from PostgreSQL and querying it with natural language. The output is a compact, notation-annotated schema ready for an LLM or text-to-SQL system. 

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/shrsv/dbctx"
)

func main() {
	ctx := context.Background()

	idx, err := dbctx.Build(ctx, "postgres://localhost/mydb", nil)
	if err != nil {
		log.Fatal(err)
	}
	defer idx.Close()

	result, err := idx.Query("failed reviews last month")
	if err != nil {
		log.Fatal(err)
	}

	// Matched() returns only tables with score > 0.
	// Text() prepends a notation legend explaining every symbol.
	fmt.Println(result.Matched().Text())
}
Example (BuildAsync)

This example demonstrates non-blocking startup with BuildAsync. The index builds in a background goroutine while the application continues setup. Queries block automatically until the index is ready. 

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/shrsv/dbctx"
)

func main() {
	ctx := context.Background()
	dsn := "postgres://localhost/mydb"

	idx, ready, err := dbctx.BuildAsync(ctx, dsn, nil)
	if err != nil {
		log.Fatal(err)
	}
	defer idx.Close()

	// Register idx with your application immediately.
	go func() {
		<-ready
		log.Println("dbctx index is ready")
	}()

	// This call blocks automatically if the index isn't ready yet.
	result, err := idx.Query("active users")
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(result.Matched().Text())
}
Example (Persist)

This example demonstrates saving an index to a .dtx file and opening it later without a PostgreSQL connection. 

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/shrsv/dbctx"
)

func main() {
	ctx := context.Background()
	dsn := "postgres://localhost/mydb"

	// Build and save to disk.
	idx, err := dbctx.Build(ctx, dsn, &dbctx.Options{Path: "mydb.dtx"})
	if err != nil {
		log.Fatal(err)
	}
	idx.Close()

	// Reopen without PostgreSQL.
	idx, err = dbctx.Open("mydb.dtx")
	if err != nil {
		log.Fatal(err)
	}
	defer idx.Close()

	tables, _ := idx.Tables()
	fmt.Printf("Index has %d tables\n", len(tables))
}
Example (Selection)

This example demonstrates the Selection API for filtering and rendering query results in different ways. 

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/shrsv/dbctx"
)

func main() {
	ctx := context.Background()

	idx, err := dbctx.Build(ctx, "postgres://localhost/mydb", nil)
	if err != nil {
		log.Fatal(err)
	}
	defer idx.Close()

	result, err := idx.Query("failed reviews")
	if err != nil {
		log.Fatal(err)
	}

	// Only matched tables, with notation legend.
	fmt.Println(result.Matched().Text())

	// Without legend (tighter token budget).
	fmt.Println(result.Matched().TextRaw())

	// All tables including FK-expanded.
	fmt.Println(result.All().TextRaw())

	// Specific tables by name.
	fmt.Println(result.Include("reviews", "orgs").TextRaw())

	// Matched minus a table.
	fmt.Println(result.Matched().Exclude("migrations").TextRaw())
}

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type ColumnDetail 

type ColumnDetail struct {
	Name        string          `json:"name"`
	Type        string          `json:"type"`
	Nullable    bool            `json:"nullable"`
	IsPK        bool            `json:"is_pk"`
	FKTarget    string          `json:"fk_target,omitempty"`
	Distinct    int             `json:"distinct"`
	IsState     bool            `json:"is_state"`
	IsCategoric bool            `json:"is_categoric"`
	Values      []ValueInfo     `json:"values,omitempty"`
	JSONBPaths  []JSONBPathInfo `json:"jsonb_paths,omitempty"`
}

ColumnDetail describes a column in a table detail response. It includes distinct count, state/categorical flags, representative values, and JSONB paths.

type ColumnInfo 

type ColumnInfo struct {
	Name        string          `json:"name"`
	Type        string          `json:"type"`
	Nullable    bool            `json:"nullable"`
	IsPK        bool            `json:"is_pk"`
	FKTarget    string          `json:"fk_target,omitempty"`
	IsState     bool            `json:"is_state"`
	IsCategoric bool            `json:"is_categoric"`
	Values      []ValueInfo     `json:"values,omitempty"`
	JSONBPaths  []JSONBPathInfo `json:"jsonb_paths,omitempty"`
}

ColumnInfo describes a column in a query result, including its type, flags (PK, nullable, state, categorical), representative values, and JSONB paths if applicable.

type FKInfo 

type FKInfo struct {
	SrcColumns string `json:"src_columns"`
	RefTable   string `json:"ref_table"`
	DstColumns string `json:"dst_columns"`
}

FKInfo describes a foreign key relationship between tables.

type Index 

type Index struct {
	// contains filtered or unexported fields
}

Index is a compiled database context index. It provides methods to query the database structure, relationships, field semantics, and representative values extracted from PostgreSQL.

An Index is safe for concurrent use by multiple goroutines. Create one with Build, BuildAsync, or Open.

func Build 

func Build(ctx context.Context, dsn string, opts *Options) (*Index, error)

Build connects to PostgreSQL and builds a complete database context index.

It extracts schema, analyzes field statistics from pg_stats, discovers JSONB structure via sampling, and builds a full-text search index. The resulting index is ready for queries immediately upon return.

If opts is nil or opts.Path is empty, the index is stored in memory. Pass opts.Path to persist the index as a .dtx file on disk.

The caller must call Close on the returned Index when done.

func BuildAsync 

func BuildAsync(ctx context.Context, dsn string, opts *Options) (*Index, <-chan struct{}, error)

BuildAsync starts building the index in a background goroutine and returns immediately. The returned channel is closed when the build completes.

This is useful for non-blocking application startup. The returned Index can be registered with your application immediately. Any calls to Index.Query, Index.Tables, or other methods will block until the build completes.

Example: 

idx, ready, err := dbctx.BuildAsync(ctx, dsn, nil)
if err != nil {
    log.Fatal(err)
}
defer idx.Close()

// Register idx with your app immediately...

// Wait for readiness:
<-ready

If the build fails, Index.Err returns the error and Query/Tables/etc will return that error.

func Open 

func Open(path string) (*Index, error)

Open opens an existing .dtx file for querying. The file must exist and contain a valid dbctx index created by Build or the `dbctx build` CLI.

The caller must call Close on the returned Index when done.

Example

This example demonstrates opening a persisted .dtx file and listing all tables with summary information. 

package main

import (
	"fmt"
	"log"

	"github.com/shrsv/dbctx"
)

func main() {
	idx, err := dbctx.Open("mydb.dtx")
	if err != nil {
		log.Fatal(err)
	}
	defer idx.Close()

	tables, err := idx.Tables()
	if err != nil {
		log.Fatal(err)
	}

	for _, t := range tables {
		fmt.Printf("%-30s %6.0f rows  %d cols  %d FKs\n",
			t.Name, t.RowEstimate, t.ColCount, t.FKCount)
	}
}

func (*Index) Close 

func (idx *Index) Close() error

Close releases all resources held by the index, including the underlying SQLite database connection. After Close, no other methods may be called.

func (*Index) Err 

func (idx *Index) Err() error

Err returns the build error if an async build failed. Returns nil if the build succeeded or is still in progress. Check Index.Ready first to know when the build is done.

func (*Index) Query 

func (idx *Index) Query(query string) (*ResultSet, error)

Query searches the index for tables matching the given natural language query. It combines full-text search, fuzzy table name matching, value matching, and foreign-key expansion to find relevant tables and their context.

If the index was created with BuildAsync and the build is still in progress, Query blocks until the build completes.

Returns a ResultSet that can be filtered and converted to compact text: 

result, _ := idx.Query("failed reviews last month")
text := result.Matched().Text()         // only matched tables
text := result.All().Text()             // all tables including FK-expanded
text := result.Include("reviews").Text() // specific tables

func (*Index) Ready 

func (idx *Index) Ready() <-chan struct{}

Ready returns a channel that is closed when the index is ready for queries. For synchronous builds created with Build, the channel is already closed. For async builds created with BuildAsync, the channel closes when the background build completes.

func (*Index) Report 

func (idx *Index) Report(w io.Writer) error

Report writes a human-readable report of the entire index to w. The report includes schema, state fields, categorical fields, JSONB structure, relationships, and summary statistics.

Blocks until the index is ready if an async build is in progress.

func (*Index) Stats 

func (idx *Index) Stats() (*Stats, error)

Stats returns summary statistics about the index, including counts of tables, columns, foreign keys, state fields, categorical fields, JSONB paths, and field values.

Blocks until the index is ready if an async build is in progress.

Example

This example demonstrates getting summary statistics about the index. 

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/shrsv/dbctx"
)

func main() {
	ctx := context.Background()

	idx, err := dbctx.Build(ctx, "postgres://localhost/mydb", nil)
	if err != nil {
		log.Fatal(err)
	}
	defer idx.Close()

	stats, err := idx.Stats()
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("Tables:            %d\n", stats.Tables)
	fmt.Printf("Columns:           %d\n", stats.Columns)
	fmt.Printf("Foreign keys:      %d\n", stats.ForeignKeys)
	fmt.Printf("State fields:      %d\n", stats.StateFields)
	fmt.Printf("Categorical fields: %d\n", stats.CategoricalFields)
	fmt.Printf("JSONB paths:       %d\n", stats.JSONBPaths)
}

func (*Index) TableDetail 

func (idx *Index) TableDetail(name string) (*TableDetail, error)

TableDetail returns detailed information about a specific table, including columns with types, PK/FK tags, value distributions, JSONB paths, and foreign key relationships.

Returns nil and no error if the table is not found. Blocks until the index is ready if an async build is in progress.

Example

This example demonstrates getting detailed information about a single table including columns, types, primary keys, foreign keys, and representative values for state-like fields. 

package main

import (
	"context"
	"fmt"
	"log"
	"strings"

	"github.com/shrsv/dbctx"
)

func main() {
	ctx := context.Background()

	idx, err := dbctx.Build(ctx, "postgres://localhost/mydb", nil)
	if err != nil {
		log.Fatal(err)
	}
	defer idx.Close()

	detail, err := idx.TableDetail("reviews")
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("Table: %s\n", detail.Name)
	fmt.Printf("Primary key: %s\n", strings.Join(detail.PrimaryKey, ", "))
	fmt.Printf("Columns: %d\n", len(detail.Columns))

	for _, col := range detail.Columns {
		flags := ""
		if col.IsPK {
			flags += " PK"
		}
		if col.IsState {
			flags += " [state]"
		}
		if col.FKTarget != "" {
			flags += " -> " + col.FKTarget
		}
		fmt.Printf("  %-20s %-20s%s\n", col.Name, col.Type, flags)
	}
}

func (*Index) Tables 

func (idx *Index) Tables() ([]TableSummary, error)

Tables returns a summary of all tables in the index. Each entry includes the table name, schema, row estimate, column count, and FK count.

Blocks until the index is ready if an async build is in progress.

type JSONBPathInfo 

type JSONBPathInfo struct {
	Path         string `json:"path"`
	InferredType string `json:"inferred_type"`
	SampleValues string `json:"sample_values,omitempty"`
}

JSONBPathInfo describes a path within a JSONB column, including its inferred type and sample values.

type Options 

type Options struct {
	// Path is the file path for the .dtx file. If empty, an in-memory
	// SQLite database is used (no file created). In-memory indexes are
	// faster but must be rebuilt on each process start.
	Path string

	// Schemas is a comma-separated list of PostgreSQL schemas to extract.
	// Defaults to "public" if empty.
	Schemas string

	// MaxConns is the maximum number of concurrent PostgreSQL connections
	// in the connection pool. Higher values allow more parallel JSONB
	// analysis. Defaults to 4 if zero.
	MaxConns int

	// Logger receives progress messages during build. If nil, os.Stderr is used.
	Logger io.Writer
}

Options configures how a database context index is built.

type ResultSet 

type ResultSet struct {
	// Query is the original query string.
	Query string `json:"query"`
	// Tables contains all tables in the result, including both directly
	// matched tables (score > 0) and FK-expanded tables (score = 0).
	Tables []TableContext `json:"tables"`
}

ResultSet holds the results of a query and provides methods to select subsets of matched tables and render them as compact text.

The typical flow is: 

result, _ := idx.Query("failed reviews")
text := result.Matched().Text()  // compact schema of matched tables only

func (*ResultSet) All 

func (rs *ResultSet) All() *Selection

All returns a Selection containing all tables in the result set, including FK-expanded tables that were not directly matched.

func (*ResultSet) Include 

func (rs *ResultSet) Include(names ...string) *Selection

Include returns a Selection containing only the named tables. Tables not found in the result set are silently ignored.

func (*ResultSet) Matched 

func (rs *ResultSet) Matched() *Selection

Matched returns a Selection containing only tables with a match score > 0. These are the tables most relevant to the query.

func (*ResultSet) TableMap 

func (rs *ResultSet) TableMap() map[string]TableContext

TableMap returns a map of table name to TableContext for quick lookup.

type Selection 

type Selection struct {
	// contains filtered or unexported fields
}

Selection represents a subset of tables from a ResultSet. It provides methods to refine the selection and render it as compact text suitable for passing to an LLM or text-to-SQL system.

func (*Selection) Exclude 

func (s *Selection) Exclude(names ...string) *Selection

Exclude removes the named tables from the selection.

func (*Selection) Include 

func (s *Selection) Include(names ...string) *Selection

Include adds the named tables to the selection. Tables not in the result set are silently ignored.

func (*Selection) Len 

func (s *Selection) Len() int

Len returns the number of tables in the selection.

func (*Selection) Tables 

func (s *Selection) Tables() []TableContext

Tables returns the TableContext objects in this selection, in the same order they appear in the original result set.

func (*Selection) Text 

func (s *Selection) Text() string

Text renders the selected tables as compact, human-readable text with a notation legend at the top. The legend explains every symbol and annotation used in the output so that an LLM (or human) can interpret the schema without external documentation.

Use Selection.TextRaw to omit the legend.

func (*Selection) TextRaw 

func (s *Selection) TextRaw() string

TextRaw renders the selected tables as compact, human-readable text without the notation legend. Use this when the caller already knows the notation, or when token budget is tight and the legend would be wasted context.

The output includes table names, scores, primary keys, foreign keys, columns with type/flags, state/categorical values, and JSONB paths.

type Stats 

type Stats struct {
	Tables            int `json:"tables"`
	Columns           int `json:"columns"`
	ForeignKeys       int `json:"foreign_keys"`
	StateFields       int `json:"state_fields"`
	CategoricalFields int `json:"categorical_fields"`
	JSONBPaths        int `json:"jsonb_paths"`
	FieldValues       int `json:"field_values"`
}

Stats contains summary statistics about a database context index.

type TableContext 

type TableContext struct {
	TableName   string       `json:"table_name"`
	Schema      string       `json:"schema"`
	Columns     []ColumnInfo `json:"columns"`
	PrimaryKey  []string     `json:"primary_key"`
	ForeignKeys []FKInfo     `json:"foreign_keys"`
	IsMatch     bool         `json:"is_match"`
	MatchScore  float64      `json:"match_score"`
}

TableContext represents a table in a query result with its relevance score and full context (columns, values, relationships, JSONB paths).

type TableDetail 

type TableDetail struct {
	TableSummary
	PrimaryKey  []string       `json:"primary_key"`
	ForeignKeys []FKInfo       `json:"foreign_keys"`
	Columns     []ColumnDetail `json:"columns"`
}

TableDetail contains complete information about a table, including columns with types, flags, values, JSONB paths, and all relationships.

type TableSummary 

type TableSummary struct {
	ID          int     `json:"id"`
	Schema      string  `json:"schema"`
	Name        string  `json:"name"`
	RowEstimate float64 `json:"row_estimate"`
	ColCount    int     `json:"columns"`
	FKCount     int     `json:"fk_count"`
}

TableSummary is a lightweight table descriptor returned by Index.Tables.

type ValueInfo 

type ValueInfo struct {
	Value     string `json:"value"`
	Frequency int    `json:"frequency"`
}

ValueInfo represents a representative value for a field, with its frequency (as permille, 0-1000).

Source Files

View all Source files

Directories

Path Synopsis
internal
analyze
db
report
schema
search
testutil
Package testutil provides shared test helpers for dbctx tests.
 Package testutil provides shared test helpers for dbctx tests.
ui

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.