Imported from paveletto99/memora (packages/db/AGENTS.md). Install upstream with npx skills add paveletto99/memora --skill db. Copyright stays with the author.

AGENTS.md

Purpose

This directory contains database and persistence code for the Team Context Engine.

The DB package owns schema, migrations, repositories, SQLite setup, lexical index setup, vector index integration, and persistence helpers.

Act as a conservative implementation assistant. Prioritize data correctness, migration safety, reversibility, clear repositories, and testable persistence behavior.

Scope

This file applies to:

packages/db/**

Follow the root AGENTS.md plus this file. This file is more specific for database work.

Responsibilities

The DB package owns:

• database connection setup
• schema definitions
• migrations
• repository implementations
• SQLite configuration
• FTS5 lexical index setup
• vector table/index integration
• transaction helpers
• persistence DTOs
• database test fixtures

The DB package does not own:

• memory lifecycle policy
• memory approval decisions
• retrieval ranking logic
• MCP tool handlers
• GraphQL resolvers
• Svelte UI behavior
• background job orchestration

Core Rules

1. Do not put business policy in repositories.
2. Do not decide memory approval rules in the DB layer.
3. Do not expose raw SQL details outside repository boundaries unless existing patterns do so.
4. Keep migrations deterministic and committed.
5. Prefer reversible or rebuildable index changes.
6. Preserve provenance fields.
7. Preserve auditability for memory and retrieval records.
8. Do not silently drop or rewrite memory data.
9. Do not store secrets in logs or test fixtures.
10. Use transactions for multi-table state changes where consistency matters.

Package Boundaries

The DB package may depend on domain types when needed, but should not implement domain decisions.

Expected callers:

apps/api          GraphQL service/resolver layer
apps/mcp          MCP service layer
apps/worker       ingestion/indexing jobs
packages/core     domain services, if architecture allows
packages/retrieval primitive lexical/vector search adapters

Avoid importing app-layer code into this package.

Core Tables

Expected persistence areas include:

memory_artifacts
memories
memory_chunks
memory_chunks_fts
memory_chunk_vectors
skills
retrieval_traces
audit_events
job_runs / background jobs, if implemented

Do not add tables casually. If adding a table, explain:

• why existing tables are insufficient
• migration impact
• rollback plan
• tests needed

Memory Persistence

Memory records should preserve:

• id
• type
• title
• summary
• body
• team ID
• project ID when available
• source artifact ID
• source span when available
• visibility
• status
• confidence
• supersedes memory ID when available
• expiry timestamp when available
• creator
• created timestamp
• updated timestamp

Do not conflate raw artifacts with approved memory.

Do not delete memory records when deprecation is the safer operation.

Artifact Persistence

Artifacts should preserve raw source material and metadata.

Expected artifact metadata:

• id
• source type
• title
• storage location or raw text reference
• submitter
• team
• project when available
• suggested visibility
• created timestamp

Artifacts are not approved memory by default.

Chunk Persistence

Memory chunks are retrieval units.

Chunks should preserve:

• chunk ID
• memory ID
• team ID
• project ID when available
• chunk text
• chunk index
• memory type
• memory status
• token count when available
• timestamps

Chunk records should be rebuildable from approved memory where possible.

Avoid duplicate chunks for the same memory and chunk index.

Lexical Index

SQLite FTS5 or equivalent lexical index should support exact and keyword search.

Lexical index updates should follow memory status rules.

Default indexed memory should be:

approved
active
not expired
not superseded
not rejected
not deprecated unless explicitly supported

Do not index raw artifacts for agent retrieval by default.

If the FTS index gets out of sync, prefer a rebuild path.

Vector Index

Vector records should preserve:

• chunk ID
• embedding vector
• embedding model
• embedding dimension
• created timestamp

Do not silently mix incompatible embedding models or dimensions.

If model or dimension changes, treat it as an index migration or reindexing task.

Vector indexes should be rebuildable from approved chunks.

Repository Style

Prefer repository methods with clear names, such as:

createMemoryArtifact
getMemoryArtifactById
createMemoryCandidate
updateMemoryStatus
getMemoryById
listMemories
createMemoryChunks
searchMemoryChunksLexical
searchMemoryChunksVector
insertRetrievalTrace
insertAuditEvent

Repository methods should:

• validate required persistence inputs when practical
• use transactions for related writes
• return typed records
• avoid hidden lifecycle transitions
• avoid broad catch-all update methods unless justified

Migrations

Migration changes are high risk.

For migrations:

• make them deterministic
• avoid destructive changes when possible
• include rollback notes
• preserve existing data
• backfill explicitly when needed
• add tests or migration validation where possible

Do not rely on runtime auto-sync for schema changes.

For destructive migrations, require explicit human confirmation.

Transactions

Use transactions when changing multiple related records, such as:

• creating memory plus chunks
• approving memory and scheduling indexing
• deprecating memory and writing audit event
• inserting retrieval trace with result links
• updating job status and output records

Avoid partial writes that expose inconsistent memory state.

Audit and Trace Persistence

Audit events should capture important state changes such as:

• candidate created
• candidate approved
• candidate rejected
• memory deprecated
• visibility changed
• skill approved
• skill deprecated

Retrieval traces should capture safe retrieval metadata.

Do not store raw secrets or sensitive artifact content in audit or trace records.

Test Fixtures

Fixtures should be realistic but safe.

Do not include real secrets, real credentials, sensitive personal data, or customer data.

Useful fixture types:

• approved decision memory
• draft candidate
• rejected candidate
• deprecated memory
• expired memory
• superseded memory
• private/team/org visibility examples
• multiple projects
• retrieval trace sample
• conflicting memory pair

Tests

Add or update tests for persistence behavior changes.

Prioritize tests for:

• migrations
• repository CRUD
• memory status updates
• artifact creation
• candidate creation
• chunk creation
• FTS search behavior
• vector record persistence
• transaction rollback
• visibility/status filtering
• retrieval trace insertion
• audit event insertion
• no duplicate chunk/index records

For migration or schema changes, add tests or at least a validation script when possible.

Risk Escalation

Be conservative when touching:

• schema definitions
• migrations
• memory status fields
• visibility fields
• source provenance fields
• FTS index schema
• vector index schema
• embedding model/dimension storage
• deletion behavior
• transaction boundaries
• audit or trace persistence

For these changes, include risks, rollback notes, and tests.

Implementation Style

Prefer:

• explicit schema definitions
• small repositories
• typed persistence records
• transactions for consistency
• deterministic migrations
• rebuildable indexes
• clear fixture data

Avoid:

• business policy in repositories
• broad update methods
• silent data deletion
• unscoped queries
• migration auto-sync
• raw SQL scattered outside repositories
• storing sensitive data in logs, traces, or fixtures
• irreversible index transformations without a rebuild path

Output Style

For non-trivial DB changes, respond with:

1. Task understanding
2. Relevant schema/migration/repository files
3. Current persistence behavior
4. Assumptions / uncertainties
5. Proposed approach
6. Code changes
7. Tests or migration validation
8. Data, rollback, and operational notes

For small mechanical edits, be concise but still mention tests and risks when relevant.