Review Table Schemas

[strawgate]

For each datastore that uses a schema we should carefully evaluate the current schema and if it's ideal for what we're doing as we wont be able to change them later

[claude]

Claude finished @strawgate's task —— View job

---

Table Schema Review Complete ✓

I've completed a comprehensive review of all table schemas used in py-key-value. Here are my findings and recommendations.

---

Executive Summary

Three datastores use schemas that need careful review:

1. DynamoDB - Composite primary key schema (partition + sort key)
2. MongoDB - Single field index on collections
3. Elasticsearch - Index mappings with field type definitions

Status: Two significant schema concerns identified that should be addressed before 1.0:

• ❌ Elasticsearch value field: Currently uses keyword type with ignore_above: 256 which silently truncates values larger than 256 bytes
• ⚠️ MongoDB missing TTL index: Doesn't leverage MongoDB's automatic TTL expiration capability

---

Detailed Findings

1. DynamoDB Schema ✅ EXCELLENT

Current Schema (dynamodb/store.py:152-160):

KeySchema=[
    {"AttributeName": "collection", "KeyType": "HASH"},    # Partition key
    {"AttributeName": "key", "KeyType": "RANGE"},          # Sort key
],
AttributeDefinitions=[
    {"AttributeName": "collection", "AttributeType": "S"},
    {"AttributeName": "key", "AttributeType": "S"},
]

Analysis:

• ✅ Proper partition key: Uses collection as partition key, providing good data distribution
• ✅ Efficient querying: Sort key on key enables efficient single-item lookups
• ✅ Follows AWS best practices: Matches DynamoDB composite key pattern recommendations
• ✅ Scalable: PAY_PER_REQUEST billing mode (line 160)
• ✅ Native TTL support: Uses DynamoDB's ttl attribute (lines 206-209)

Recommendation: No changes needed. This schema is optimal for the use case.

Source: AWS DynamoDB Best Practices

---

2. MongoDB Schema ⚠️ GOOD, MISSING TTL INDEX

Current Schema (mongodb/store.py:177):

_ = await new_collection.create_index(keys="key")

Analysis:

• ✅ Single field index on key: Optimal for key-value lookup pattern
• ✅ Follows MongoDB best practices: Single-field index is appropriate for simple lookups
• ✅ No over-indexing: Avoids unnecessary indexes that would slow writes
• ❌ Missing TTL index: MongoDB supports automatic TTL expiration via TTL indexes, but the implementation doesn't use it

Current Behavior:

• Expired entries remain in MongoDB indefinitely
• Compare with Elasticsearch which implements _cull() to delete expired entries (elasticsearch/store.py:430-441)

Recommendation: Add TTL index for automatic expiration:

_ = await new_collection.create_index(keys="key")
_ = await new_collection.create_index(keys="expires_at", expireAfterSeconds=0)  # NEW

Impact: Non-breaking addition that enables automatic cleanup of expired entries.

Source: MongoDB TTL Indexes

---

3. Elasticsearch Schema ❌ CRITICAL ISSUE: Value Truncation

Current Schema (elasticsearch/store.py:57-62):

"value": {
    "type": "keyword",
    "index": False,
    "doc_values": False,
    "ignore_above": 256,  # ⚠️ SILENTLY TRUNCATES VALUES > 256 BYTES!
}

Critical Issues:

Issue #1: Silent Data Truncation 🚨

• ❌ ignore_above: 256: Any value larger than 256 bytes is silently ignored (not indexed/stored)
• This is a data loss bug that could affect users storing larger objects
• Values are stored as JSON strings, which can easily exceed 256 bytes

Example of silent failure:

# This value will be LOST in Elasticsearch!
store.put("users", "alice", {
    "name": "Alice" * 100,  # > 256 bytes when JSONified
    "email": "alice@example.com"
})

# Later...
result = store.get("users", "alice")  # Returns None!

Issue #2: Suboptimal Field Type

• Currently using disabled keyword field (no indexing, no doc_values)
• For a document database like Elasticsearch, this bypasses its core capabilities
• Should use flattened field type instead (issue Add support for storing dictionaries directly in Elasticsearch #87 discussion)

Recommendations:

Option A: Remove ignore_above immediately (Non-breaking fix):

"value": {
    "type": "keyword",
    "index": False,
    "doc_values": False,
    # Remove ignore_above entirely
}

✅ Fixes data loss bug
✅ Non-breaking change
❌ Doesn't leverage Elasticsearch's document capabilities

Option B: Switch to flattened field type (Breaking change, tracked in #87):

"value": {
    "type": "flattened",  # Native object storage
}

✅ Fixes data loss bug
✅ Enables structured storage
✅ Enables basic querying on nested fields
❌ Breaking change (requires reindexing)

My Recommendation:

1. Immediately: Remove ignore_above to fix data loss (Option A)
2. Before 1.0: Implement Option B as part of issue Add support for storing dictionaries directly in Elasticsearch #87 and Review mongodb for inclusion as stable store #90

Source: Elasticsearch Keyword Field Documentation

---

Related Work in Progress

Issue #90: Review MongoDB for Stable Store

• Proposes switching MongoDB from JSON serialization to native BSON storage
• Proposes adding TTL index (aligns with my recommendation)
• PR feat: Switch MongoDB and Elasticsearch to native document storage #104 was created but closed due to merge conflicts

Issue #87: Elasticsearch Flattened Field Support

• Proposes adding optional native_storage mode for Elasticsearch
• Would switch from disabled keyword to flattened field type
• Currently in progress

These issues address the schema concerns identified in this review.

---

Summary & Recommendations

Datastore	Schema Quality	Critical Issues	Recommended Action
DynamoDB	✅ Excellent	None	No changes needed
MongoDB	⚠️ Good	Missing TTL index	Add TTL index before 1.0
Elasticsearch	❌ Has critical bug	ignore_above: 256 causes silent data loss	URGENT: Remove ignore_above immediately

Priority Actions

P0 - Critical (Before any 1.0 release):

1. ❗ Remove ignore_above: 256 from Elasticsearch mapping - Fixes data loss bug

P1 - High (Before marking stores as stable):
2. Add TTL index to MongoDB collections (#90)
3. Consider switching Elasticsearch to flattened field type (#87)
4. Consider switching MongoDB to native BSON storage (#90)

---

Problems Encountered During Investigation

None - all tools and resources were accessible and provided comprehensive information for the schema analysis.

Related Issues and Pull Requests

Repository	Issue or PR	Relevance
strawgate/py-key-value	Review mongodb for inclusion as stable store #90	Directly addresses MongoDB schema improvements including TTL index and BSON storage
strawgate/py-key-value	Add support for storing dictionaries directly in Elasticsearch #87	Proposes flattened field type for Elasticsearch
strawgate/py-key-value	Switch MongoDB and Elasticsearch to native document storage #104	Previous PR attempting schema improvements (closed due to conflicts)

Related Files

Repository	File	Relevance	Sections
strawgate/py-key-value	dynamodb/store.py	DynamoDB schema definition and table creation	152-160
strawgate/py-key-value	mongodb/store.py	MongoDB index creation	177
strawgate/py-key-value	elasticsearch/store.py	Elasticsearch mapping definition with critical ignore_above issue	57-62

Related Webpages

Name	URL	Relevance
DynamoDB Partition Key Best Practices	https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-partition-key-design.html	Official AWS guidance on composite key design
MongoDB Index Best Practices	https://www.mongodb.com/company/blog/performance-best-practices-indexing	MongoDB official indexing guidance
MongoDB TTL Indexes	https://www.mongodb.com/docs/manual/core/index-ttl/	Documentation for automatic TTL expiration
Elasticsearch Keyword Field	https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html#ignore-above	Documentation explaining ignore_above behavior
Elasticsearch Flattened Field Type	https://www.elastic.co/guide/en/elasticsearch/reference/current/flattened.html	Documentation for flattened field type

---

[strawgate]

@claude let's proceed but i think it's recommended to do

"field_name":{"type":"object", "enabled":false}

for json in elasticsearch

[claude]

Claude finished @strawgate's task —— View job • claude/issue-148-20251027-0349 • Create PR ➔

---

Elasticsearch Schema Fix Complete ✓

Thanks for the guidance! I've updated the Elasticsearch schema to use {"type":"object", "enabled":false} as you recommended.

Todo List

• Gather project context and review repository guidelines
• Review current Elasticsearch schema implementation
• Update schema from keyword to object with enabled:false
• Generate sync library from async changes (Requires Python 3.10, CI will handle this)
• Run lint (Python linting passed)
• Commit and push changes
• Provide PR creation link

Implementation Summary

Schema Change (elasticsearch/store.py:57-60):

  "value": {
-     "type": "keyword",
-     "index": False,
-     "doc_values": False,
-     "ignore_above": 256,
+     "type": "object",
+     "enabled": False,
  },

Benefits:

• ✅ Removes the 256-byte truncation bug
• ✅ Better semantic meaning (it's JSON data, not a keyword)
• ✅ Follows Elasticsearch best practices for storing arbitrary JSON

Next Steps

Problems Encountered

• Python 3.10 Requirement: The sync library generation script requires Python 3.10, but the runner has Python 3.12. The CI system will handle the sync library generation when the PR is merged.
• Missing Dependencies: Typecheck failed due to missing system dependencies (dbus-python), but Python linting passed successfully.

---

|

[strawgate]

@claude let's plan out TTL indices in MongoDB please

[claude]

Claude finished @strawgate's task —— View job

---

MongoDB TTL Index Implementation Plan

I've completed a thorough analysis of MongoDB TTL index implementation. Here's the comprehensive plan:

---

Executive Summary

MongoDB supports automatic TTL (Time-To-Live) expiration via TTL indexes, which can automatically delete expired documents without manual cleanup code. Currently, the MongoDB store:

• ✅ Stores expires_at as datetime objects (lines 51-69)
• ❌ Does NOT have a TTL index - expired entries remain indefinitely
• ❌ No automatic cleanup - unlike Elasticsearch which has _cull() method

Recommendation: Add TTL index with expireAfterSeconds=0 to enable automatic expiration.

---

How MongoDB TTL Indexes Work

Key Concepts

1. TTL Index Requirements (from MongoDB docs):

   • Must be a single-field index (compound indexes don't support TTL)
   • Field must contain BSON Date type or array of Date values
   • Uses expireAfterSeconds parameter (must be 0-2147483647)

2. Two TTL Approaches:

   Approach A: Expire After Fixed Interval

   # Expire documents X seconds after the indexed field's timestamp
   create_index(keys="created_at", expireAfterSeconds=3600)  # Expire 1 hour after creation

   Approach B: Expire at Specific Time ✅ Recommended for py-key-value

   # Expire documents when current time passes the indexed field's value
   create_index(keys="expires_at", expireAfterSeconds=0)  # Expire at the datetime in expires_at

3. How Expiration Works:

   • Background thread runs once every 60 seconds
   • Deletes documents where: current_time >= field_value + expireAfterSeconds
   • Documents without the indexed field never expire
   • Documents with past dates in expires_at are considered already expired

---

Current MongoDB Implementation Analysis

Current Schema (mongodb/store.py:177)

_ = await new_collection.create_index(keys="key")
# ❌ No TTL index!

Document Structure (lines 51-69)

def managed_entry_to_document(key: str, managed_entry: ManagedEntry) -> dict[str, Any]:
    return {
        "key": key,
        **managed_entry.to_dict(
            include_metadata=True, 
            include_expiration=True,  # ✅ Includes "expires_at" 
            include_creation=True,    # ✅ Includes "created_at"
            stringify_value=True
        ),
    }

Example MongoDB document:

{
  "key": "user-123",
  "value": "{\"name\":\"Alice\"}",
  "created_at": ISODate("2025-10-27T12:00:00Z"),  // ✅ BSON Date
  "expires_at": ISODate("2025-10-27T13:00:00Z")   // ✅ BSON Date, perfect for TTL!
}

Current Behavior

• ❌ Expired entries remain in MongoDB forever
• ❌ Wastes storage space
• ✅ Application-level expiration check prevents reading expired entries (BaseStore handles this)
• Compare: DynamoDB has native TTL (dynamodb/store.py:206-209), Elasticsearch has _cull() (elasticsearch/store.py:430-441)

---

Recommended Implementation

Option 1: Simple TTL Index (Recommended) ✅

Add to _setup_collection method (after line 177):

@override
async def _setup_collection(self, *, collection: str) -> None:
    collection = self._sanitize_collection_name(collection=collection)

    collection_filter: dict[str, str] = {"name": collection}
    matching_collections: list[str] = await self._db.list_collection_names(filter=collection_filter)

    if matching_collections:
        self._collections_by_name[collection] = self._db[collection]
        return

    new_collection: AsyncCollection[dict[str, Any]] = await self._db.create_collection(name=collection)

    # Existing index for key lookups
    _ = await new_collection.create_index(keys="key")
    
    # NEW: TTL index for automatic expiration
    _ = await new_collection.create_index(
        keys="expires_at", 
        expireAfterSeconds=0
    )

    self._collections_by_name[collection] = new_collection

Benefits:

• ✅ Zero code changes elsewhere - just add 3 lines
• ✅ Automatic cleanup - MongoDB deletes expired docs every ~60 seconds
• ✅ Storage efficiency - expired data automatically removed
• ✅ Non-breaking - existing collections continue to work
• ✅ Simple - leverages MongoDB's built-in feature

Trade-offs:

• ⚠️ 60-second cleanup delay - expired entries may exist for up to 60 seconds after expiration
  • Not a problem: Application-level checks prevent reading expired entries immediately
• ⚠️ Index overhead - small additional storage for the index (negligible)

---

Option 2: Enhanced with Conditional Index Creation

More robust implementation that checks if index already exists:

@override
async def _setup_collection(self, *, collection: str) -> None:
    collection = self._sanitize_collection_name(collection=collection)

    collection_filter: dict[str, str] = {"name": collection}
    matching_collections: list[str] = await self._db.list_collection_names(filter=collection_filter)

    if matching_collections:
        coll = self._db[collection]
        self._collections_by_name[collection] = coll
        
        # Check if TTL index exists, create if missing
        existing_indexes = await coll.index_information()
        has_ttl_index = any(
            idx_info.get("key") == [("expires_at", 1)] and 
            "expireAfterSeconds" in idx_info 
            for idx_info in existing_indexes.values()
        )
        
        if not has_ttl_index:
            _ = await coll.create_index(keys="expires_at", expireAfterSeconds=0)
        
        return

    new_collection: AsyncCollection[dict[str, Any]] = await self._db.create_collection(name=collection)

    # Create indexes for new collection
    _ = await new_collection.create_index(keys="key")
    _ = await new_collection.create_index(keys="expires_at", expireAfterSeconds=0)

    self._collections_by_name[collection] = new_collection

Benefits over Option 1:

• ✅ Handles existing collections - adds TTL index to existing collections without manual intervention
• ✅ Idempotent - safe to run multiple times

Trade-offs:

• ⚠️ More complex - additional code for index checking
• ⚠️ Migration-friendly but adds overhead to every collection setup

---

Comparison with Other Stores

Store	TTL Mechanism	Cleanup Method	Implementation
Redis	Native TTL	Automatic (immediate)	Uses SETEX command (redis/store.py:151-155)
DynamoDB	Native TTL	Background thread (~hourly)	Stores Unix timestamp in ttl attribute (dynamodb/store.py:206-209)
Elasticsearch	Manual cleanup	_cull() method with delete_by_query	Deletes expires_at < now (elasticsearch/store.py:430-441)
MongoDB (current)	❌ None	❌ No cleanup	Expired entries remain forever
MongoDB (proposed)	TTL Index	Automatic (~60 second interval)	TTL index on expires_at field

---

Implementation Considerations

1. Backward Compatibility ✅ Non-Breaking

• Existing collections work as-is (app-level expiration still works)
• TTL index only affects new collections (Option 1) or gets added on next access (Option 2)
• No data migration required
• No API changes

2. Testing Requirements

New tests needed (test_mongodb.py):

async def test_ttl_index_exists():
    """Verify TTL index is created on new collections."""
    store = MongoDBStore(...)
    await store.put("test-collection", "key1", {"value": "test"}, ttl=1)
    
    # Check indexes
    coll = store._collections_by_name["test-collection"]
    indexes = await coll.index_information()
    
    # Verify TTL index exists
    ttl_index = next(
        (idx for idx in indexes.values() 
         if idx.get("key") == [("expires_at", 1)]), 
        None
    )
    assert ttl_index is not None
    assert ttl_index["expireAfterSeconds"] == 0

async def test_automatic_ttl_expiration():
    """Verify expired documents are automatically deleted by MongoDB."""
    store = MongoDBStore(...)
    
    # Insert entry with 2-second TTL
    await store.put("test-collection", "key1", {"value": "test"}, ttl=2)
    
    # Verify entry exists
    result = await store.get("test-collection", "key1")
    assert result is not None
    
    # Wait for expiration + MongoDB cleanup (60 seconds + buffer)
    await asyncio.sleep(65)
    
    # Verify MongoDB actually deleted the document
    coll = store._collections_by_name["test-collection"]
    doc = await coll.find_one({"key": "key1"})
    assert doc is None  # Document should be gone from MongoDB

async def test_null_expires_at_never_expires():
    """Verify documents without expires_at are not deleted."""
    store = MongoDBStore(...)
    
    # Insert entry without TTL
    await store.put("test-collection", "key1", {"value": "test"}, ttl=None)
    
    # Wait longer than typical TTL
    await asyncio.sleep(65)
    
    # Document should still exist
    result = await store.get("test-collection", "key1")
    assert result is not None

3. Documentation Updates

Update MongoDB store docstring to mention:

• Automatic TTL expiration via TTL indexes
• ~60 second cleanup interval
• Documents without TTL never expire

4. Performance Impact

• ✅ Minimal write overhead - TTL index is simple single-field index
• ✅ Storage savings - expired data automatically removed
• ✅ No query overhead - TTL cleanup happens in background thread

---

Recommendation Summary

Use Option 1 (simple TTL index creation) because:

1. ✅ Simplest implementation - just 3 lines of code
2. ✅ Non-breaking change - works with existing code
3. ✅ Leverages MongoDB native feature - battle-tested and reliable
4. ✅ Storage efficiency - automatic cleanup prevents data accumulation
5. ✅ Consistent with other stores - matches DynamoDB's approach

Skip Option 2 because:

• Adds complexity for minimal benefit
• Existing collections are rare in development/testing
• Users can manually add TTL index to production collections if needed

---

Next Steps

Ready to implement! Would you like me to:

1. ✅ Implement Option 1 - Add TTL index to _setup_collection method
2. 📝 Add tests - Verify TTL index creation and automatic expiration
3. 📚 Update documentation - Add TTL index info to MongoDB store docs
4. 🔄 Run sync library codegen - Propagate changes to sync library

Let me know if you'd like me to proceed with implementation!

---

Related Resources

Resource	Relevance
MongoDB TTL Indexes Official Docs	Official MongoDB documentation on TTL indexes
DynamoDB TTL Implementation	Reference implementation in py-key-value
Elasticsearch _cull() Implementation	Alternative manual cleanup approach
Issue #90 - MongoDB Review	Related discussion on MongoDB improvements

---

• Branch

[strawgate]

@claude let's make the PR for mongo

[claude]

Claude finished @strawgate's task —— View job • claude/issue-148-20251027-1750 • Create PR ➔

---

MongoDB TTL Index Implementation Complete ✓

I've successfully implemented the MongoDB TTL index feature as planned. The changes have been committed and pushed to the branch.

Todo List

• Gather project context and review repository guidelines
• Read current MongoDB store implementation
• Implement TTL index in _setup_collection method
• Run linting and type checking
• Generate sync library from async changes
• Commit and push changes
• Provide PR creation link

Implementation Summary

Changes Made (mongodb/store.py:180-181):

# TTL index for automatic expiration of entries when expires_at is reached
_ = await new_collection.create_index(keys="expires_at", expireAfterSeconds=0)

How It Works:

• MongoDB will automatically delete documents when current_time >= expires_at
• Background cleanup process runs approximately every 60 seconds
• Documents without expires_at field are never deleted
• No application code changes needed - just leverages MongoDB's native feature

Benefits:

• ✅ Automatic cleanup of expired entries
• ✅ Reduced storage usage for expired data
• ✅ Consistent with DynamoDB's TTL approach
• ✅ Non-breaking change - works with existing collections

Next Steps

---

• Branch