Cache Engine

Caching LLM responses is a practical optimization that reduces API costs, lowers latency for repeated queries, and makes your applications more resilient to network interruptions. The cache engine presented here stores text responses in a local SQLite database, supports keyword-based retrieval, and automatically cleans up stale entries — a pattern directly applicable to any Prolog system that calls external LLM APIs.

This chapter is a SWI-Prolog port of the Common Lisp cache-engine library. The Prolog version uses the prosqlite pack for native SQLite access and exposes a clean, modular API.

Design Overview

The cache engine is built around a single SQLite table:

Column	Type	Description
id	INTEGER	Auto-incrementing primary key
content	TEXT	The cached text string
created_at	DATETIME	Automatic timestamp on insertion

The API provides six core operations:

cache_open/2 — Create or open a SQLite database file
cache_add/2 — Insert a text string into the cache
cache_lookup/3,4 — Retrieve matching entries by keyword search
cache_count/2 — Count total cached items
cache_clear/1 — Remove all entries
cache_clear_older_one_week/1 — Remove entries older than 7 days

Implementation

The cache engine module uses prosqlite for SQLite access. Install it with:

1 ?- pack_install(prosqlite).

Here is the file cache_engine/prolog/cache_engine.pl:

  1 :- module(cache_engine, [
  2     cache_open/2,
  3     cache_close/1,
  4     cache_add/2,
  5     cache_lookup/3,
  6     cache_lookup/4,
  7     cache_count/2,
  8     cache_clear/1,
  9     cache_clear_older_one_week/1
 10 ]).
 11 
 12 :- use_module(library(prosqlite)).
 13 
 14 %% cache_open(+DbPath, -Connection)
 15 %% Opens (or creates) a SQLite database at DbPath and ensures the
 16 %% cache table exists.  Returns the Connection alias.
 17 %% DbPath should be the base name without extension — prosqlite
 18 %% appends '.db' automatically via the ext(db) option.
 19 cache_open(DbPath, Connection) :-
 20     gensym(cache_db_, Connection),
 21     sqlite_connect(DbPath, Connection, [ext(db), exists(false)]),
 22     ensure_cache_table(Connection).
 23 
 24 ensure_cache_table(Conn) :-
 25     atom_concat(
 26         'CREATE TABLE IF NOT EXISTS cache ',
 27         '(id INTEGER PRIMARY KEY, ',
 28         S1),
 29     atom_concat(
 30         S1,
 31         'content TEXT, ',
 32         S2),
 33     atom_concat(
 34         S2,
 35         'created_at DATETIME DEFAULT ',
 36         S3),
 37     atom_concat(
 38         S3,
 39         'CURRENT_TIMESTAMP)',
 40         SQL),
 41     ( sqlite_query(Conn, SQL, _Row)
 42     -> true
 43     ;  true
 44     ).
 45 
 46 %% cache_close(+Connection)
 47 %% Closes the SQLite database connection.
 48 cache_close(Connection) :-
 49     sqlite_disconnect(Connection).
 50 
 51 %% cache_add(+Connection, +Text)
 52 %% Adds a string to the cache.
 53 cache_add(Connection, Text) :-
 54     escape_sql(Text, Escaped),
 55     format(atom(SQL), "INSERT INTO cache (content) VALUES ('~w')",
 56         [Escaped]),
 57     ( sqlite_query(Connection, SQL, _Row) -> true ; true ).
 58 
 59 %% cache_lookup(+Connection, +SearchTerms, -Results)
 60 %% Returns matching cached strings (default limit 3).
 61 %% SearchTerms is a list of atoms/strings to match against content.
 62 %% When SearchTerms is empty, returns up to 3 most recent entries.
 63 cache_lookup(Connection, SearchTerms, Results) :-
 64     cache_lookup(Connection, SearchTerms, Results, [limit(3)]).
 65 
 66 %% cache_lookup(+Connection, +SearchTerms, -Results, +Options)
 67 %% Options: limit(N), match_any(true/false)
 68 %%   limit(N)         — max number of results (default 3)
 69 %%   match_any(true)  — OR matching (default: AND)
 70 cache_lookup(Connection, [], Results, Options) :-
 71     option_limit(Options, Limit),
 72     format(atom(SQL),
 73         "SELECT content FROM cache ORDER BY created_at DESC LIMIT ~d",
 74         [Limit]),
 75     findall(Content,
 76         sqlite_query(Connection, SQL, row(Content)),
 77         Results).
 78 
 79 cache_lookup(Connection, SearchTerms, Results, Options) :-
 80     SearchTerms \= [],
 81     option_limit(Options, Limit),
 82     option_match_any(Options, MatchAny),
 83     build_where_clause(SearchTerms, MatchAny, WhereClause),
 84     format(atom(SQL),
 85         "SELECT content FROM cache WHERE ~w ORDER BY created_at DESC LIMIT ~d",
 86         [WhereClause, Limit]),
 87     findall(Content,
 88         sqlite_query(Connection, SQL, row(Content)),
 89         Results).
 90 
 91 %% cache_count(+Connection, -Count)
 92 %% Returns the number of items in the cache.
 93 cache_count(Connection, Count) :-
 94     sqlite_table_count(Connection, cache, Count).
 95 
 96 %% cache_clear(+Connection)
 97 %% Removes all items from the cache.
 98 cache_clear(Connection) :-
 99     ( sqlite_query(Connection, "DELETE FROM cache", _Row)
100     -> true ; true ).
101 
102 %% cache_clear_older_one_week(+Connection)
103 %% Removes items older than 7 days from the cache.
104 cache_clear_older_one_week(Connection) :-
105     SQL = "DELETE FROM cache WHERE created_at <= datetime('now', '-7 days')",
106     ( sqlite_query(Connection, SQL, _Row)
107     -> true
108     ;  true
109     ).

The helper predicates handle SQL construction and escaping:

 1 option_limit(Options, Limit) :-
 2     ( member(limit(Limit), Options) -> true ; Limit = 3 ).
 3 
 4 option_match_any(Options, MatchAny) :-
 5     ( member(match_any(MatchAny), Options) -> true ; MatchAny = false ).
 6 
 7 %% build_where_clause(+Terms, +MatchAny, -Clause)
 8 %% Builds a SQL WHERE clause from search terms.
 9 build_where_clause([Term], _, Clause) :-
10     escape_sql(Term, Escaped),
11     format(atom(Clause), "content LIKE '%~w%'", [Escaped]).
12 build_where_clause([Term|Rest], MatchAny, Clause) :-
13     Rest \= [],
14     ( MatchAny = true -> Connector = " OR " ; Connector = " AND " ),
15     escape_sql(Term, Escaped),
16     format(atom(TermClause), "content LIKE '%~w%'", [Escaped]),
17     build_where_clause(Rest, MatchAny, RestClause),
18     format(atom(Clause), "~w~w~w", [TermClause, Connector, RestClause]).
19 
20 %% escape_sql(+Input, -Escaped)
21 %% Doubles single quotes for safe SQL interpolation.
22 escape_sql(Input, Escaped) :-
23     atom_string(Input, Str),
24     split_string(Str, "'", "", Parts),
25     atomics_to_text(Parts, "''", Escaped).
26 
27 %% atomics_to_text(+Parts, +Sep, -Text)
28 %% Joins a list of strings with separator Sep.
29 atomics_to_text([P], _, P) :- !.
30 atomics_to_text([P|Ps], Sep, Text) :-
31     atomics_to_text(Ps, Sep, Rest),
32     atom_concat(P, Sep, Temp),
33     atom_concat(Temp, Rest, Text).

Usage Examples

Open a cache, add entries, and look them up:

 1 ?- cache_open(my_cache, C),
 2    cache_add(C, 'The quick brown fox jumps over the lazy dog'),
 3    cache_add(C, 'Common Lisp is powerful'),
 4    cache_add(C, 'SQLite is a great database').
 5 
 6 ?- cache_lookup(C, [fox], Results).
 7 Results = ['The quick brown fox jumps over the lazy dog'].
 8 
 9 ?- cache_lookup(C, ['Lisp', powerful], R).
10 R = ['Common Lisp is powerful'].

Use OR matching to broaden the search:

1 ?- cache_lookup(C, [fox, database], R, [match_any(true)]).
2 R = ['SQLite is a great database',
3      'The quick brown fox jumps over the lazy dog'].

Clean up old entries and close:

1 ?- cache_clear_older_one_week(C).
2 ?- cache_close(C).

Key Design Decisions

Why prosqlite? SWI-Prolog does not ship with a built-in SQLite interface, but the prosqlite pack provides native C bindings to libsqlite3. This gives us proper SQL semantics, ACID transactions, and the full power of SQLite’s query language — including LIKE for fuzzy matching and datetime() functions for timestamp arithmetic.

SQL construction vs. parameterized queries. The prosqlite pack does not support parameterized queries (prepared statements with ? placeholders). We construct SQL strings using format/2 and escape single quotes in user input via the escape_sql/2 helper. For a cache engine handling LLM responses, this is sufficient and keeps the code straightforward.

Connection management. Each call to cache_open/2 generates a unique connection alias via gensym/2. This allows multiple independent caches to be open simultaneously — useful when different subsystems (e.g., an LLM client and a web scraper) maintain separate caches.

Practical Applications

This cache engine is designed to sit between your Prolog application and an external LLM API. Common use patterns include:

Deduplication: Before calling an expensive LLM API, look up the cache for similar prior responses.
Session context: Accumulate LLM responses during a session and use cache_lookup/4 with match_any(true) to retrieve relevant context for follow-up queries.
Cost control: Cache responses to avoid redundant API calls, especially during development and testing.
Stale data management: Use cache_clear_older_one_week/1 in a periodic cleanup to prevent unbounded growth.

Up next

LLM Logic Guardrails