1. Executive summary

SQL is not a database product. It is a declarative language: you describe the result you need, and a database engine decides how to obtain it. SQLite is one implementation of an SQL database. It runs inside the application and stores the database in a file, which makes it excellent for prototypes, desktop tools, mobile apps, local research subsets, and read-heavy citation indexes.

2. What SQL is

SQL means Structured Query Language. It lets a developer define tables, enforce constraints, insert data, connect related tables, aggregate results, and update records. The developer specifies what should be returned; the engine selects how to execute the request.

SELECT p.title, a.name
FROM papers p
JOIN paper_authors pa ON pa.paper_id = p.id
JOIN authors a ON a.id = pa.author_id
WHERE p.year = 2024
  AND p.title_search MATCH 'citation matching';

Core relational concepts

Normalization: why data is separated

A paper may have five authors, and one author may have hundreds of papers. Repeating the full author record inside every paper wastes space and produces inconsistencies. A normalized schema stores each author once and represents authorship with a join table.

3. What SQLite is—and what “SQL light” actually means

The product is named SQLite, pronounced “S-Q-L-ite” or “sequel-lite.” It is not a simplified query language. It is a complete embedded relational database engine delivered as a library. The application calls SQLite directly; there is no separate database server process and no network round trip.

Important 2025–2026 direction

SQLite continues to improve schema evolution, JSON manipulation, command-line formatting, indexing, and reliability. For citation systems, the enduringly useful features are its B-tree indexes, ACID transactions, JSON functions, FTS5 full-text search, generated columns, expression indexes, and write-ahead logging.

4. What happens behind a SQL query

A SQL query looks compact, but the database performs a pipeline of interpretation, planning, storage access, and result construction.

Example: exact DOI lookup

SELECT id, title, year
FROM papers
WHERE doi = '10.1145/1234567.1234568';

Without an index, the engine may inspect every paper row. With a unique index on doi, it traverses a B-tree from the root to a small leaf page, finds the row pointer, and fetches the matching record.

How joins are chosen

5. How scholarly data looks before and after ingestion

Citation databases publish different data formats. An ingestion pipeline parses those formats, normalizes identifiers and text, and writes a query-friendly representation.

<article key="journals/example/Smith24">
  <author>Alice Smith</author>
  <author>Bob Lee</author>
  <title>Fuzzy Citation Matching at Scale</title>
  <year>2024</year>
  <journal>Journal of Data Systems</journal>
  <ee>https://doi.org/10.1234/example.2024.7</ee>
</article>

<PubmedArticle>
  <MedlineCitation PMID="40123456">
    <Article>
      <ArticleTitle>Reference Resolution in Biomedical Literature</ArticleTitle>
      <Abstract>...</Abstract>
      <Journal>...</Journal>
    </Article>
  </MedlineCitation>
</PubmedArticle>

{
  "id": "https://openalex.org/W1234567890",
  "doi": "https://doi.org/10.1234/example.2024.7",
  "title": "Fuzzy Citation Matching at Scale",
  "publication_year": 2024,
  "authorships": [
    {"author": {"id": "A100", "display_name": "Alice Smith"}}
  ],
  "referenced_works": ["W99", "W105"]
}

papers
┌──────┬──────────────────────────────────┬──────┬────────────────────────┐
│ id   │ title                            │ year │ doi                    │
├──────┼──────────────────────────────────┼──────┼────────────────────────┤
│ 7821 │ Fuzzy Citation Matching at Scale │ 2024 │ 10.1234/example.2024.7│
└──────┴──────────────────────────────────┴──────┴────────────────────────┘

paper_authors
┌──────────┬───────────┬──────────┐
│ paper_id │ author_id │ position │
├──────────┼───────────┼──────────┤
│ 7821     │ 100       │ 1        │
│ 7821     │ 205       │ 2        │
└──────────┴───────────┴──────────┘

{
  "_id": "7821",
  "title": "Fuzzy Citation Matching at Scale",
  "title_normalized": "fuzzy citation matching at scale",
  "authors": ["Alice Smith", "Bob Lee"],
  "year": 2024,
  "venue": "Journal of Data Systems",
  "doi": "10.1234/example.2024.7"
}

{
  "id": "7821",
  "vector": [0.021, -0.117, 0.308, ... 765 more values ...],
  "metadata": {
    "year": 2024,
    "source": "openalex"
  }
}

Node:  Work W7821
Edge:  W7821 --CITES--> W99
Edge:  W7821 --CITES--> W105
Node:  Author A100
Edge:  A100 --AUTHORED--> W7821

Row storage versus columnar storage

6. The index structures behind fast search

How an inverted index looks

D1: "fuzzy citation matching"
D2: "citation graph search"
D3: "fuzzy author resolution"

citation → [D1, D2]
fuzzy    → [D1, D3]
matching → [D1]
graph    → [D2]
author   → [D3]

A keyword search for fuzzy AND citation intersects the postings lists and immediately identifies D1, rather than scanning every title.

Why one index is not enough

7. SQL databases and their alternatives in 2026

“Alternative to SQL” is often the wrong framing. Most specialized systems solve a different access pattern. The architectural question is: which storage and index should own each responsibility?

Decision matrix

8. How DBLP, PubMed, and OpenAlex can be stored locally

Scale changes the architecture

Do companies really keep all of this “locally”?

“Local” can mean a laptop, an on-premise server, or the company’s own cloud account. Production companies often download provider snapshots into their own object storage, transform them with batch jobs, load canonical records into PostgreSQL or another serving database, and build separate text and vector indexes. They may not load every raw field into the serving database.

9. How a citation-matching system finds the correct reference

A raw citation is noisy. It may have a missing DOI, misspelled author, abbreviated venue, incorrect year, broken page range, reordered fields, or copied punctuation. Matching therefore uses multiple signals.

Example input

Smith A., Lee B. Fuzzy citaton maching scale.
J Data Syst, 2024, doi 10.1234/example.2024.?

Parsed representation

{
  "authors": ["Smith A", "Lee B"],
  "title": "Fuzzy citaton maching scale",
  "venue": "J Data Syst",
  "year": 2024,
  "doi_prefix": "10.1234",
  "doi_complete": false
}

Candidate-generation methods

A transparent scoring formula

score =
  0.40 * title_similarity
+ 0.20 * author_similarity
+ 0.12 * venue_similarity
+ 0.10 * year_compatibility
+ 0.08 * page_similarity
+ 0.05 * identifier_prefix_match
+ 0.05 * source_agreement

A learned model can replace fixed weights, but the features should remain inspectable. For a citation-fixing product, confidence calibration matters: a system should abstain when top candidates are too close.

Illustrative ranked candidates

10. Efficient local querying of very large datasets

Strategy A: import a filtered subset into SQLite

Select the relevant subject area, years, fields, or providers, then create a compact database containing only the fields needed for matching. This is ideal for a prototype or a computer-science-only DBLP system.

CREATE TABLE papers (
  id INTEGER PRIMARY KEY,
  source TEXT NOT NULL,
  source_id TEXT NOT NULL,
  doi TEXT,
  title TEXT NOT NULL,
  title_norm TEXT NOT NULL,
  year INTEGER,
  venue_id INTEGER,
  raw_json TEXT
);

CREATE UNIQUE INDEX ux_papers_source
ON papers(source, source_id);

CREATE INDEX ix_papers_doi
ON papers(doi);

CREATE INDEX ix_papers_year
ON papers(year);

Strategy B: keep snapshots as Parquet and query with DuckDB

For a huge OpenAlex snapshot, importing everything into a row database may take substantial time and disk space. DuckDB can query partitioned Parquet directly and only read the required columns and partitions.

SELECT id, doi, title, publication_year
FROM read_parquet('openalex/works/**/*.parquet')
WHERE publication_year BETWEEN 2022 AND 2026
  AND lower(title) LIKE '%citation matching%';

Strategy C: central PostgreSQL plus specialized indexes

Store canonical entities in PostgreSQL; send denormalized search documents to OpenSearch; store embeddings in pgvector initially; keep provider snapshots in object storage; and build batch pipelines for updates.

Storage optimizations

11. Recommended architecture for a citation-fixing company

The following design is appropriate for a product that accepts malformed references, searches DBLP, PubMed, OpenAlex, Crossref, and OpenCitations, and returns ranked canonical matches with evidence.

What each layer owns

12. A practical canonical schema

CREATE TABLE works (
  work_id            BIGSERIAL PRIMARY KEY,
  canonical_title    TEXT NOT NULL,
  title_normalized   TEXT NOT NULL,
  publication_year   SMALLINT,
  work_type          TEXT,
  venue_id            BIGINT,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE work_identifiers (
  work_id             BIGINT NOT NULL REFERENCES works(work_id),
  scheme              TEXT NOT NULL,  -- doi, pmid, openalex, dblp
  value               TEXT NOT NULL,
  source              TEXT NOT NULL,
  is_primary          BOOLEAN NOT NULL DEFAULT false,
  PRIMARY KEY (scheme, value)
);

CREATE TABLE authors (
  author_id            BIGSERIAL PRIMARY KEY,
  display_name         TEXT NOT NULL,
  name_normalized      TEXT NOT NULL,
  orcid                TEXT
);

CREATE TABLE work_authors (
  work_id              BIGINT NOT NULL REFERENCES works(work_id),
  author_id            BIGINT NOT NULL REFERENCES authors(author_id),
  author_position      SMALLINT,
  raw_name             TEXT,
  PRIMARY KEY (work_id, author_id)
);

CREATE TABLE citations (
  citing_work_id       BIGINT NOT NULL REFERENCES works(work_id),
  cited_work_id        BIGINT NOT NULL REFERENCES works(work_id),
  source               TEXT NOT NULL,
  confidence           REAL,
  PRIMARY KEY (citing_work_id, cited_work_id, source)
);

CREATE TABLE source_records (
  source               TEXT NOT NULL,
  source_record_id     TEXT NOT NULL,
  work_id              BIGINT REFERENCES works(work_id),
  payload              JSONB NOT NULL,
  source_updated_at    TIMESTAMPTZ,
  ingested_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (source, source_record_id)
);

Indexes for matching

CREATE UNIQUE INDEX ux_identifier
ON work_identifiers (scheme, value);

CREATE INDEX ix_work_year
ON works (publication_year);

CREATE INDEX ix_title_trgm
ON works USING gin (title_normalized gin_trgm_ops);

CREATE INDEX ix_author_name_trgm
ON authors USING gin (name_normalized gin_trgm_ops);

CREATE INDEX ix_citations_cited
ON citations (cited_work_id);

Why raw payloads are retained

The canonical tables make querying simple, but provider-specific fields can be lost during normalization. Keeping the original payload enables audits, reprocessing, new feature extraction, and resolution of disagreements between sources.

13. Practical query examples

Exact DOI resolution

SELECT w.work_id, w.canonical_title, w.publication_year
FROM work_identifiers i
JOIN works w ON w.work_id = i.work_id
WHERE i.scheme = 'doi'
  AND i.value = '10.1234/example.2024.7';

Typo-tolerant title candidates in PostgreSQL

SELECT
  work_id,
  canonical_title,
  similarity(title_normalized,
             'fuzzy citaton maching at scale') AS title_score
FROM works
WHERE title_normalized % 'fuzzy citaton maching at scale'
ORDER BY title_score DESC
LIMIT 25;

Combined title, author, and year ranking

WITH title_candidates AS (
  SELECT
    w.work_id,
    similarity(w.title_normalized, :title_norm) AS title_score
  FROM works w
  WHERE w.title_normalized % :title_norm
  LIMIT 100
)
SELECT
  w.work_id,
  w.canonical_title,
  w.publication_year,
  tc.title_score,
  MAX(similarity(a.name_normalized, :first_author_norm)) AS author_score,
  CASE WHEN w.publication_year = :year THEN 1.0 ELSE 0.0 END AS year_score
FROM title_candidates tc
JOIN works w ON w.work_id = tc.work_id
LEFT JOIN work_authors wa ON wa.work_id = w.work_id
LEFT JOIN authors a ON a.author_id = wa.author_id
GROUP BY w.work_id, w.canonical_title, w.publication_year, tc.title_score
ORDER BY
  0.65 * tc.title_score +
  0.25 * MAX(similarity(a.name_normalized, :first_author_norm)) +
  0.10 * CASE WHEN w.publication_year = :year THEN 1.0 ELSE 0.0 END
  DESC
LIMIT 10;

Find all works citing a DOI

SELECT citing.canonical_title, citing.publication_year
FROM work_identifiers target_id
JOIN citations c ON c.cited_work_id = target_id.work_id
JOIN works citing ON citing.work_id = c.citing_work_id
WHERE target_id.scheme = 'doi'
  AND target_id.value = '10.1234/example.2024.7'
ORDER BY citing.publication_year DESC;

SQLite FTS5 prototype

CREATE VIRTUAL TABLE work_search USING fts5(
  title,
  authors,
  venue,
  content='works_flat',
  content_rowid='work_id'
);

SELECT rowid, title, bm25(work_search) AS rank
FROM work_search
WHERE work_search MATCH '"citation matching" OR "reference resolution"'
ORDER BY rank
LIMIT 20;

Vector search with SQL filters

SELECT
  work_id,
  canonical_title,
  1 - (embedding <=> :query_embedding) AS semantic_score
FROM work_embeddings
WHERE publication_year BETWEEN 2020 AND 2026
ORDER BY embedding <=> :query_embedding
LIMIT 50;

14. Implementation roadmap for a real citation matcher

Recommended evaluation dataset

Start from known canonical references, then generate realistic corruption: delete fields, alter punctuation, introduce OCR-like substitutions, abbreviate venues, reorder authors, change initials, remove DOI suffixes, and perturb years. Keep the original work ID as ground truth.

Final recommendation

15. Research sources and further reading

This article consolidates the prior deep-research report and extends it with original explanatory diagrams, sample schemas, and architecture recommendations. Product versions and dataset sizes change, so verify operational details against current official documentation before deployment.

• SQLite official documentation and release notes
• ISO/IEC SQL standard information
• DBLP XML data and documentation
• PubMed data download guidance
• OpenAlex developer documentation
• Crossref REST API documentation
• OpenCitations datasets and services
• PostgreSQL official documentation
• DuckDB official documentation
• pgvector documentation
• OpenSearch documentation

Interactive hyperlinks are available in the HTML version.