PERSISTENT ON-PREM SQL MEMORY IMPLEMENTATION PROMPT ROLE You are a senior AI systems engineer responsible for improving an agent's long-term memory architecture. OBJECTIVE Replace or augment the agent's current memory mechanism with a persistent, on-premises SQL-backed memory store designed for durable recall, efficient retrieval, low latency, long-term context retention, correction handling, and uncertainty-aware memory. The implementation must be production-oriented, measurable, and directly comparable against the agent's previous memory method. CORE REQUIREMENTS 1. ON-PREMISES SQL MEMORY - Use an on-premises SQL database as the durable source of truth for agent memory. - Do not depend on a hosted SaaS memory provider for the primary persistence layer. - Prefer PostgreSQL unless the existing environment has a clearly better-supported SQL engine. - Design the system so memory survives agent restarts, model changes, application restarts, and machine reboots. - Keep the storage architecture portable and vendor-independent wherever practical. 2. MEMORY SCHEMA Create a structured schema capable of storing at minimum: - memory_id - agent_id - user_id or conversation owner - conversation/session ID - timestamp created - timestamp updated - effective_from timestamp - effective_until timestamp where applicable - memory type/category - source - raw content - normalized/summary content - importance score - confidence score - recency score - decay score - verification state - contradiction state - access count - last accessed timestamp - tags/topics - entities - relationships to other memories - supersedes / superseded_by references - embedding/vector reference if semantic retrieval is used - expiration/retention metadata - archival status - correction history - provenance metadata Use normalized relational tables where appropriate, but do not over-engineer the schema if a simpler structure performs better. 3. MEMORY INGESTION Implement a pipeline that: - identifies information worth retaining - rejects obvious transient/noise content - deduplicates similar memories - updates existing memories when newer information supersedes them - preserves historical versions where useful - supports explicit user/agent instructions to remember, update, archive, correct, or forget information - records provenance so the agent can determine where a memory came from - distinguishes observations from assumptions, inferences, and user claims - avoids converting uncertain information into permanent high-confidence facts 4. DECAYED / STALE INFORMATION Memory must not treat all stored information as permanently current. Implement a decay model that accounts for: - age of the memory - memory type - likelihood that the information changes over time - whether the memory has been reaffirmed - whether newer evidence conflicts with it - frequency of successful use - source reliability Examples: - A user's preferred name may decay very slowly. - A temporary project deadline should decay quickly after the deadline. - Current employment, location, pricing, schedules, device configuration, and project status should be considered time-sensitive. - Stable historical events should generally remain valid unless corrected. Decay should influence retrieval ranking, not automatically erase history. Maintain both: - historical truth: what was believed or stated at a specific point in time - current truth candidate: the most recent and best-supported state Do not silently overwrite history. 5. USER ERROR, CORRECTIONS, AND CONTRADICTIONS Assume users, agents, imported data, and external systems can be wrong. The system must support: - explicit corrections - conflicting user statements - accidental typos - mistaken dates - changing preferences - outdated claims - uncertain statements - contradictory sources Do not simply use "latest statement always wins." Instead, evaluate: - source - timestamp - confidence - specificity - explicit correction language - repeated confirmation - external verification when available - whether statements can both be true at different times Examples: - "I work at Company A" followed months later by "I left Company A" is temporal evolution, not necessarily a contradiction. - "My appointment is Friday" followed immediately by "Sorry, I meant Thursday" should treat Thursday as an explicit correction. - Two incompatible claims without a correction should remain marked as unresolved until enough evidence exists. Store corrections as provenance-linked events. Never delete the old value solely because a new value exists unless deletion is explicitly required. 6. CONFIDENCE MODEL Assign and update confidence separately from importance. Confidence may increase when: - the user explicitly confirms a memory - the same fact is independently observed repeatedly - a trusted source verifies it - the memory continues to predict later interactions accurately Confidence may decrease when: - the memory becomes stale - contradictory information appears - the source is unreliable - the information was originally inferred - the user corrects it - the value is inherently volatile Do not confuse repetition with truth. Repeated copies from the same original source are not independent verification. 7. RETRIEVAL Implement a retrieval layer capable of combining: - exact SQL lookup - recent-memory lookup - importance-weighted lookup - confidence-weighted lookup - decay-aware lookup - entity-based lookup - topic/tag lookup - conversation/session lookup - full-text search - semantic/vector retrieval if justified The retrieval system should return only the most useful memories for the current request rather than dumping large amounts of irrelevant context into the model. Create a ranking formula or retrieval strategy that considers: - semantic relevance - recency - importance - confidence - decay/staleness - frequency of prior use - relationship to currently active entities/projects - contradiction status - whether the memory has been superseded Superseded or disputed memories should normally rank below current, verified memories but remain retrievable when historical context is relevant. 8. MEMORY CACHE Add a fast memory cache in front of the persistent SQL layer where beneficial. The cache must: - reduce repeated SQL work - have deterministic invalidation or refresh rules - never silently become the authoritative source of truth - recover cleanly after restart - fall back to SQL when cache data is missing or stale - invalidate affected entries when memories are corrected, superseded, deleted, or confidence changes If an in-process cache is sufficient, use it. If Redis or another local cache materially improves performance, justify the additional dependency before introducing it. 9. CONTEXT ASSEMBLY Build a memory-context assembler that: - receives the current user/agent request - identifies relevant topics/entities/projects - queries the memory system - ranks candidate memories - removes duplicates - resolves superseded facts - detects unresolved contradictions - applies decay and confidence rules - enforces a configurable token budget - returns a concise structured memory packet to the agent Do not inject the entire memory database into prompts. The context packet should clearly distinguish: - current high-confidence memories - historical memories - uncertain memories - disputed memories - explicitly corrected/superseded memories 10. LONG-TERM RETENTION The architecture should support months or years of memory history without retrieval quality degrading substantially. Include: - indexing strategy - archival strategy - cleanup strategy - optional summarization/compaction - database maintenance considerations - handling for millions of memories if the system grows to that scale - decay-aware archival - preservation of correction/supersession chains 11. PRIVACY AND CONTROL Memory must remain on premises unless explicitly configured otherwise. Implement or document: - memory deletion - per-user isolation - access controls - database backups - encryption options - audit logging - retention policies - ability to inspect exactly what the system remembers - ability to inspect why a memory is considered current, stale, disputed, or superseded 12. MIGRATION Inspect the current memory implementation before replacing it. Where practical: - migrate existing memories into SQL - preserve timestamps and provenance - avoid duplicate imports - preserve conflicting historical records instead of flattening them - initialize confidence and decay conservatively for legacy memories - verify migration integrity - retain a rollback path until the new implementation is validated Do not destroy the old memory implementation until the SQL version has been verified. 13. BENCHMARK AGAINST THE PREVIOUS METHOD Before switching permanently, benchmark the existing memory system and the new SQL implementation using the same representative workload. Measure at minimum: - number of durable memories retained - usable historical time span - successful recall rate - precision/relevance of retrieved memories - false or irrelevant recall rate - stale-memory retrieval rate - contradiction resolution accuracy - correction propagation accuracy - user-error recovery rate - retrieval latency - write latency - context tokens consumed per request - storage footprint - CPU usage - RAM usage - database query volume - cache hit rate - restart persistence - duplicate-memory rate - estimated operating cost - agent task success on memory-dependent tasks Create benchmark scenarios that specifically test: - a fact becoming outdated - an explicit user correction - two conflicting statements - a volatile fact changing multiple times - an old but historically relevant fact - an incorrect memory later disproven - repeated misinformation from the same source - correct information from a more reliable source overriding a weak prior belief Where possible, make benchmark cases automated and repeatable. 14. EFFICIENCY CALCULATION Report percentage changes relative to the previous memory method. For metrics where higher is better: Improvement % = ((new - old) / old) * 100 For metrics where lower is better: Improvement % = ((old - new) / old) * 100 Do not claim an improvement percentage unless it is directly supported by measured data. Report both: - raw values - percentage differences Do not use a single "memory improvement %" unless you define exactly how the composite score is calculated. 15. QUALITY SCORE Create a memory quality score that separately measures: - retrieval relevance - recall success - factual consistency - correction handling - staleness avoidance - temporal accuracy - confidence calibration - token efficiency If a composite score is created, document the weighting formula. 16. FAILURE MODES Explicitly test and document behavior for: - SQL unavailable - corrupted cache - failed writes - partial transactions - duplicate ingestion - stale cache entries - conflicting updates - simultaneous corrections - malformed imported memories - incorrect timestamps - bad user input - intentionally false user input - insufficient evidence to determine which memory is correct Prefer uncertainty over fabricating a resolution. 17. IMPLEMENTATION DISCIPLINE - Inspect the existing system before changing architecture. - Reuse existing infrastructure where sensible. - Do not introduce unnecessary services. - Keep the implementation understandable and maintainable. - Use transactions for multi-step memory updates. - Add indexes based on measured query patterns. - Avoid premature optimization. - Measure before and after. - Preserve rollback capability. - Do not report success until persistence and retrieval have been validated. 18. FINAL REPORT When implementation is complete, report back with: A. Previous architecture - how memory worked before - limitations found B. New architecture - SQL engine - schema - cache strategy - retrieval strategy - decay model - confidence model - correction/contradiction handling C. Migration - memories migrated - failures - duplicates removed - unresolved conflicts D. Benchmark Provide a before/after table containing every measured metric. E. Efficiency changes Provide raw deltas and percentage improvements/regressions. F. Memory quality Report: - relevant recall - stale recall - contradiction handling - correction accuracy - user-error recovery - confidence calibration G. Resource usage Report: - CPU - RAM - storage - SQL query volume - cache hit rate - token usage - estimated operating cost H. Risks and remaining limitations List any unresolved issues honestly. I. Final status State whether the SQL memory system is: - experimental - ready for parallel operation - ready to replace the previous memory system - not yet ready Do not exaggerate improvements. Do not fabricate benchmark results. Do not claim "X% better" without measured evidence. PRIMARY SUCCESS CRITERION The new system should improve the agent's practical ability to remember useful information over long periods while avoiding stale, incorrect, contradicted, or irrelevant memories and using fewer model-context tokens wherever possible. Build the system, validate it, benchmark it against the previous implementation, and report the results with reproducible evidence.