TableCatalog

⚠️ The Problem

Many services need a catalog — a lookup table that maps one identifier to another. Azure Table Storage is extremely cheap, but it only indexes on one key. Here's why that matters.

Imagine a hospital system. Each provider has three identifiers: an Employee ID (EID), a Social Security Number (SSN), and a National Provider Identifier (NPI). Any of the three might arrive in an incoming message. You need to be able to start from any one and retrieve the rest.

The obvious solution is a managed database like Azure Cosmos DB. The problem is cost: Cosmos DB carries a hard minimum monthly fee even at idle, and costs compound as you add more services.

📋 How It Works

TableCatalog sidesteps the single-index limitation by storing one physical row per (identifier, record) pair, encoding the identifier directly into the PartitionKey.

Core idea: one logical record → multiple physical rows

Because Azure Table Storage only indexes on the PartitionKey, the trick is to store the same record multiple times — once per identifier you want to search by. Each copy gets a different PartitionKey, so any identifier becomes a valid lookup path.

While this triples raw storage for a three-index catalog, Azure Table Storage costs are roughly an order of magnitude lower than Cosmos DB, making the tradeoff economically favorable for read-heavy or high-volume lookup workloads. See the cost comparison below.

The RowKey: deterministic ordering & range queries

The RowKey is derived from a configured sort field (e.g. a timestamp or last name) plus an 8-character MD5 fingerprint of all indexed values. The fingerprint makes each RowKey unique and deterministic across all copies of the record, enabling idempotent writes and safe re-runs.

// e.g. for a provider with sort field = lastName
RowKey = "faux:ec8246f2"
         ^^^^ lastName    ^^^^^^^^ fingerprint of EID|SSN|NPI

This also enables range queries: you can query any index partition filtered to a RowKey range, such as "all transactions for Location 12345 between Jan 1 and Feb 28".

🔑 Key Encoding

Different fields can share the same value (e.g. EID 12345 and Staff ID 12345 for different records). A simple concatenation would cause collisions. TableCatalog solves this by encoding the length of the field name into the PartitionKey — no delimiter needed.

The formula

PartitionKey = len(fieldName) + "_" + lowercase(fieldName) + lowercase(fieldValue)

Examples:
GetPartitionKey("eid",  "F314")        → "3_eidf314"       (len("eid") = 3)
GetPartitionKey("ssn",  "123-45-6789") → "3_ssn123456789"  (len("ssn") = 3)
GetPartitionKey("npi",  "1414213562")  → "3_npi1414213562" (len("npi") = 3)

Why not just use a colon as a separator?

Any fixed delimiter can appear in the data itself, creating ambiguity. For example, if field "a" has value "b:c", and field "a:b" has value "c", both produce the same string "a:b:c" — you can't tell them apart.

The length-prefix approach is collision-free because the length tells you exactly where the field name ends and the value begins, with no delimiter required:

🛡️ Consistency: The Write-Ahead Log

Storing one record across multiple partitions means multiple separate writes. Azure Table Storage only guarantees atomicity within a partition — not across them. A crash after the first write but before the last leaves the catalog in a corrupted split-state. TableCatalog solves this with a Write-Ahead Log (WAL).

How the WAL fixes this

Before any fan-out write, the full intent is recorded to a dedicated WAL partition. A recovery pass then replays any unprocessed WAL entries. Because all fan-out writes are idempotent (inserts skip rows that already exist; deletes skip rows already gone), recovery can safely re-apply any entry any number of times.

✨ Why TableCatalog?

🛠 Quick Start

Call configure() once at startup, then use insert(), query(), and delete() anywhere in your application. insert() returns the committed record for clean chained logic.

import az_table_catalog

# Configure schema once (or via environment variables)
az_table_catalog.configure(
    index_keys=["phone", "email"],
    row_key="timestamp"
)

# insert() returns the committed record
user = az_table_catalog.insert({
    "phone":     "555-634-5789",
    "email":     "jia@example.com",
    "timestamp": "2026-02-24T12:00:00Z",
    "status":    "active"
})

# Query any index; optionally filter to a row-key range
results = az_table_catalog.query(
    {"phone": "555-634-5789"},
    row_from="2026-01-01"
)

# Multi-filter: results must satisfy all conditions
results = az_table_catalog.query({
    "phone": "555-634-5789",
    "email": "jia@example.com"
})

# Delete all records matching an identifier
az_table_catalog.delete({"email": "jia@example.com"})

📦 Catalog Module Pattern

For production code, the recommended pattern is to create a dedicated catalog module that owns the configure() call and exposes a named function for every lookup your application needs. This keeps all catalog logic in one place and gives callers a clean, intention-revealing API.

With three index keys there are six pairwise lookups. The example below creates all six. Note that query() always returns a list, so each function takes the first element with an empty-list guard.

# provider_catalog.py
# Catalog module for the hospital provider directory.
# Call configure() once at application startup.
# Callers import only this module, never az_table_catalog directly.

import az_table_catalog

def configure():
    az_table_catalog.configure(
        index_keys="eid,ssn,npi",
        row_key="lastname"  # sorts multiple results alphabetically
    )

# ── EID lookups ────────────────────────────────
def eid_to_ssn(eid: str) -> str | None:
    rows = az_table_catalog.query({"eid": eid})
    return rows[0]["ssn"] if rows else None

def eid_to_npi(eid: str) -> str | None:
    rows = az_table_catalog.query({"eid": eid})
    return rows[0]["npi"] if rows else None

# ── SSN lookups ────────────────────────────────
def ssn_to_eid(ssn: str) -> str | None:
    rows = az_table_catalog.query({"ssn": ssn})
    return rows[0]["eid"] if rows else None

def ssn_to_npi(ssn: str) -> str | None:
    rows = az_table_catalog.query({"ssn": ssn})
    return rows[0]["npi"] if rows else None

# ── NPI lookups ────────────────────────────────
def npi_to_eid(npi: str) -> str | None:
    rows = az_table_catalog.query({"npi": npi})
    return rows[0]["eid"] if rows else None

def npi_to_ssn(npi: str) -> str | None:
    rows = az_table_catalog.query({"npi": npi})
    return rows[0]["ssn"] if rows else None

⚙️ Environment Variables

If you prefer not to call configure() explicitly, the library initializes itself on first use from the following environment variables:

📊 Cost Comparison

TableCatalog is designed for developers who want the query flexibility of an indexed database at the price of raw storage. The table below uses Azure's published pricing at time of writing.

These savings compound as you add more tables and services that share the same lookup pattern.

⏱️ No Cold Start

Some alternatives (like Azure SQL Serverless) save money by pausing the database when idle. This causes a cold start delay — the first user after a break waits 30–60 seconds for the database to wake up. To eliminate cold starts on SQL Serverless, you must switch to Provisioned mode, which costs significantly more.

Because Azure Table Storage is a built-in cloud primitive, it never sleeps. TableCatalog gives you the $0.00 idle cost of a paused database with the instant response of an always-on server.

🧠 Technical Specification (Pseudocode)

The TableCatalog pattern can be implemented in any language that can talk to Azure Table Storage (Go, C#, Node.js, etc.). This section defines the exact logic as language-agnostic pseudocode.

1. Key Generation

FUNCTION GetPartitionKey(fieldName, fieldValue):
    // Prefix with field-name length to prevent value collisions
    normalizedField = Lowercase(fieldName)
    normalizedValue = Lowercase(fieldValue)
    RETURN Format("{0}_{1}{2}", Length(normalizedField), normalizedField, normalizedValue)
    // e.g. GetPartitionKey("eid", "F314") → "3_eidf314"

FUNCTION GetRowKey(record, rowKeyValue, indexKeys):
    // Collect indexed values in alphabetical key order, lowercase
    sortedValues = []
    FOR EACH key IN Sort(indexKeys):
        Append Lowercase(str(record[key])) TO sortedValues

    fingerprint = Substring(MD5_Hex(Join(sortedValues, "|")), 0, 8)
    RETURN Lowercase(Format("{0}:{1}", rowKeyValue, fingerprint))
    // e.g. "faux:ec8246f2"

2. Write-Ahead Log & Checkpoint Pattern

FUNCTION Insert(record):
    walRowKey = ISO_Timestamp() + "_" + UUID()
    WAL.Create({ PartitionKey: "wal", RowKey: walRowKey, operation: "insert", ...record })
    Recover()

FUNCTION Delete(filter):
    records = Query(filter)
    FOR EACH record IN records:
        walRowKey = ISO_Timestamp() + "_" + UUID()
        WAL.Create({ PartitionKey: "wal", RowKey: walRowKey, operation: "delete", ...record })
    Recover()

FUNCTION Recover(startTime = NULL):
    IF startTime IS NULL:
        checkpoint = WAL.Get("metadata", "checkpoint")
        startTime = checkpoint ? checkpoint.datetime : "1900-01-01"

    orphans = WAL.Query("PartitionKey eq 'wal' AND RowKey gt '" + startTime + "'")

    FOR EACH entry IN orphans:
        IF entry.operation == "insert": ApplyInsert(entry)
        IF entry.operation == "delete": ApplyDelete(entry)
        WAL.Upsert({ PartitionKey: "metadata", RowKey: "checkpoint", datetime: entry.RowKey })

3. Idempotent Fan-out

FUNCTION ApplyInsert(payload):
    rowKey = GetRowKey(payload, payload[ROW_KEY_FIELD], INDEX_KEYS)
    FOR EACH fieldName IN INDEX_KEYS:
        partitionKey = GetPartitionKey(fieldName, payload[fieldName])
        CatalogTable.CreateIfNotExists(partitionKey, rowKey, payload)
        // Silently ignore ResourceExistsError

FUNCTION ApplyDelete(payload):
    rowKey = GetRowKey(payload, payload[ROW_KEY_FIELD], INDEX_KEYS)
    FOR EACH fieldName IN INDEX_KEYS:
        partitionKey = GetPartitionKey(fieldName, payload[fieldName])
        CatalogTable.DeleteIfExists(partitionKey, rowKey)
        // Silently ignore ResourceNotFoundError

4. Range Querying

FUNCTION Query(filter, rowFrom = NULL, rowTo = NULL):
    field, value = FirstItem(filter)
    pk = GetPartitionKey(field, value)
    odata = "PartitionKey eq '" + pk + "'"

    IF rowFrom IS NOT NULL:
        odata += " AND RowKey ge '" + Lowercase(rowFrom) + ":'"
    IF rowTo IS NOT NULL:
        odata += " AND RowKey le '" + Lowercase(rowTo) + ":z'"
        // ":z" works because "z" sorts after all hex characters (0–9, a–f)

    results = CatalogTable.Query(odata)

    FOR EACH (field, value) IN RemainingItems(filter):
        matches = Query({field: value}, rowFrom, rowTo)
        results = Intersect(results, matches)

    RETURN results

🚀 Getting Started

⚖️ License & Disclaimer

TableCatalog is released under the MIT License. It was developed to solve real-world distributed indexing challenges and is provided "as-is" with no warranty. Users are encouraged to adapt the implementation for their specific environment and requirements.

TableCatalog is a personal project inspired by work-related architectural challenges. The underlying WAL + fan-out pattern is portable and can be applied to any similar storage system in any language.