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 %% NOTE on SQL injection safety: this module relies on string-level
 13 %% SQL because parameterized sqlite_query/4 variable binding is not
 14 %% available in the installed prosqlite version.  We therefore escape
 15 %% single quotes, backslashes, NUL characters, and LIKE wildcards
 16 %% (% and _) before interpolation.
 17 
 18 :- if(catch(use_module(library(prosqlite)), _, fail)).
 19 
 20 cache_open(DbPath, Connection) :-
 21     gensym(cache_db_, Connection),
 22     catch(
 23         sqlite_connect(DbPath, Connection, [ext(db), exists(false)]),
 24         E,
 25         (   print_message(error, cache_engine_error(connect(E))),
 26             fail
 27         )),
 28     ensure_cache_table(Connection).
 29 
 30 ensure_cache_table(Conn) :-
 31     format(atom(SQL),
 32            "CREATE TABLE IF NOT EXISTS cache ~w~w~w~w",
 33            ['(id INTEGER PRIMARY KEY, ',
 34             'content TEXT, ',
 35             'created_at DATETIME DEFAULT ',
 36             'CURRENT_TIMESTAMP)']),
 37     (   sqlite_query(Conn, SQL, _Row)
 38     ->  true
 39     ;   print_message(error, cache_engine_error(ensure_table)),
 40         fail
 41     ).
 42 
 43 %% cache_close(+Connection)
 44 %% Closes the SQLite database connection.
 45 cache_close(Connection) :-
 46     sqlite_disconnect(Connection).
 47 
 48 :- else.
 49 
 50 cache_open(_, _) :-
 51     print_message(warning, cache_engine_error(missing_pack)),
 52     fail.
 53 cache_close(_) :-
 54     print_message(warning, cache_engine_error(missing_pack)),
 55     fail.
 56 
 57 :- endif.
 58 
 59 %% cache_add(+Connection, +Text)
 60 %% Adds a string to the cache.
 61 cache_add(Connection, Text) :-
 62     escape_sql(Text, Escaped),
 63     format(atom(SQL), "INSERT INTO cache (content) VALUES ('~w')",
 64         [Escaped]),
 65     catch(
 66         sqlite_query(Connection, SQL, _Row),
 67         E,
 68         (   print_message(error, cache_engine_error(add(E))),
 69             fail
 70         )).
 71 
 72 %% cache_lookup(+Connection, +SearchTerms, -Results)
 73 %% Returns matching cached strings (default limit 3).
 74 %% SearchTerms is a list of atoms/strings to match against content.
 75 %% When SearchTerms is empty, returns up to 3 most recent entries.
 76 cache_lookup(Connection, SearchTerms, Results) :-
 77     cache_lookup(Connection, SearchTerms, Results, [limit(3)]).
 78 
 79 %% cache_lookup(+Connection, +SearchTerms, -Results, +Options)
 80 %% Options: limit(N), match_any(true/false)
 81 %%   limit(N)         max number of results (default 3)
 82 %%   match_any(true)  OR matching (default: AND)
 83 cache_lookup(Connection, [], Results, Options) :-
 84     option_limit(Options, Limit),
 85     format(atom(SQL),
 86         "SELECT content FROM cache ORDER BY created_at DESC LIMIT ~d",
 87         [Limit]),
 88     findall(Content,
 89         sqlite_query(Connection, SQL, row(Content)),
 90         Results).
 91 
 92 cache_lookup(Connection, SearchTerms, Results, Options) :-
 93     SearchTerms \= [],
 94     option_limit(Options, Limit),
 95     option_match_any(Options, MatchAny),
 96     build_where_clause(SearchTerms, MatchAny, WhereClause),
 97     format(atom(SQL),
 98         "SELECT content FROM cache WHERE ~w ORDER BY created_at DESC LIMIT ~d",
 99         [WhereClause, Limit]),
100     findall(Content,
101         sqlite_query(Connection, SQL, row(Content)),
102         Results).
103 
104 %% cache_count(+Connection, -Count)
105 %% Returns the number of items in the cache.
106 cache_count(Connection, Count) :-
107     sqlite_table_count(Connection, cache, Count).
108 
109 %% cache_clear(+Connection)
110 %% Removes all items from the cache.
111 cache_clear(Connection) :-
112     catch(
113         sqlite_query(Connection, "DELETE FROM cache", _Row),
114         E,
115         (   print_message(error, cache_engine_error(clear(E))),
116             fail
117         )).
118 
119 %% cache_clear_older_one_week(+Connection)
120 %% Removes items older than 7 days from the cache.
121 cache_clear_older_one_week(Connection) :-
122     format(atom(SQL),
123            "DELETE FROM cache WHERE created_at <= datetime('now', '~w')",
124            ['-7 days']),
125     catch(
126         sqlite_query(Connection, SQL, _Row),
127         E,
128         (   print_message(error, cache_engine_error(clear_old(E))),
129             fail
130         )).

The helper predicates handle SQL construction and escaping:

 1 option_limit(Options, Limit) :-
 2     (   member(limit(Limit), Options)
 3     ->  (   integer(Limit), Limit > 0
 4         ->  true
 5         ;   domain_error(positive_integer, Limit)
 6         )
 7     ;   Limit = 3
 8     ).
 9 
10 option_match_any(Options, MatchAny) :-
11     ( member(match_any(MatchAny), Options) -> true ; MatchAny = false ).
12 
13 %% build_where_clause(+Terms, +MatchAny, -Clause)
14 %% Builds a SQL WHERE clause from search terms.
15 %% LIKE wildcards % and _ (and the escape char \\) are escaped so
16 %% user search terms cannot inject wildcard patterns or SQL.
17 build_where_clause([Term], _, Clause) :-
18     escape_sql_like(Term, Escaped),
19     format(atom(Clause),
20            "content LIKE '%~w%' ESCAPE '\\'", [Escaped]).
21 build_where_clause([Term|Rest], MatchAny, Clause) :-
22     Rest \= [],
23     ( MatchAny = true -> Connector = " OR " ; Connector = " AND " ),
24     escape_sql_like(Term, Escaped),
25     format(atom(TermClause),
26            "content LIKE '%~w%' ESCAPE '\\'", [Escaped]),
27     build_where_clause(Rest, MatchAny, RestClause),
28     format(atom(Clause), "~w~w~w", [TermClause, Connector, RestClause]).
29 
30 %% escape_sql(+Input, -Escaped)
31 %% Escapes single quotes, backslashes, and NUL characters for SQL
32 %% string interpolation.  Used for values NOT inside LIKE patterns.
33 escape_sql(Input, Escaped) :-
34     atom_string(Input, Str),
35     string_codes(Str, Codes),
36     escape_sql_codes(Codes, EscapedCodes),
37     atom_codes(Escaped, EscapedCodes).
38 
39 escape_sql_codes([], []).
40 escape_sql_codes([0|Cs], [0'\\, 0'0|Es]) :- !, escape_sql_codes(Cs, Es).
41 escape_sql_codes([0'\\|Cs], [0'\\, 0'\\|Es]) :- !, escape_sql_codes(Cs, Es).
42 escape_sql_codes([0'\'|Cs], [0'\', 0'\'|Es]) :- !, escape_sql_codes(Cs, Es).
43 escape_sql_codes([C|Cs], [C|Es]) :- escape_sql_codes(Cs, Es).
44 
45 %% escape_sql_like(+Input, -Escaped)
46 %% As escape_sql/2, but additionally escapes the LIKE wildcard
47 %% characters % and _ (with \\ as the LIKE escape character).
48 escape_sql_like(Input, Escaped) :-
49     atom_string(Input, Str),
50     string_codes(Str, Codes),
51     escape_sql_like_codes(Codes, EscapedCodes),
52     atom_codes(Escaped, EscapedCodes).
53 
54 escape_sql_like_codes([], []).
55 escape_sql_like_codes([0|Cs], [0'\\, 0'0|Es]) :-
56     !, escape_sql_like_codes(Cs, Es).
57 escape_sql_like_codes([C|Cs], [0'\\, C|Es]) :-
58     memberchk(C, [0'%, 0'_, 0'\\]),
59     !,
60     escape_sql_like_codes(Cs, Es).
61 escape_sql_like_codes([0'\'|Cs], [0'\', 0'\'|Es]) :-
62     !, escape_sql_like_codes(Cs, Es).
63 escape_sql_like_codes([C|Cs], [C|Es]) :- escape_sql_like_codes(Cs, Es).

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 protect them with two escapers: escape_sql/2 escapes single quotes, backslashes, and NUL characters for plain values, and escape_sql_like/2 additionally escapes the LIKE wildcards % and _ for search terms inside LIKE patterns. 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.

Optional Practice Problems

Time-To-Live Cache: In the cache_engine project, modify the caching rules to store a timestamp with each cached query, and invalidate any cache entries that are older than a user-specified Time-To-Live (TTL) duration.
Selective Invalidation: Implement a predicate invalidate_cache_pattern/1 that accepts a pattern (e.g. weather(city, _)), matching and retracting all cached entries corresponding to that pattern.

LLM Integration

Up next

LLM Logic Guardrails Using a Neuro-Symbolic Pattern