A database is a collection of structured data that is stored in a computer system, and it can be hosted on-premises or in the cloud. As databases are designed to enable easy access to data, our resources are compiled here for smooth browsing of everything you need to know from database management systems to database languages.
Understanding Vector Databases: Concepts, Similarity Search, and Trade-offs for Enterprise Applications
When Jev Says It's Sure, Is It Right? A Fraud Case Study in Neo4j
Why "Search" Isn't Enough Anymore A plain RAG pipeline — embed a query, hit a vector index, stuff the top-k chunks into a prompt — works fine for a single-turn FAQ bot. It falls apart the moment you need: Multi-turn memory ("what about last quarter?" needs to know what "last quarter" refers to)A decision about whether to search at all (some questions are structured lookups, not document retrieval)Citations the caller can verify This post builds a small but complete grounded search agent: Azure OpenAI (via Microsoft Foundry) decides when to call a search tool versus a structured data tool, Azure AI Search handles retrieval and semantic ranking over unstructured documents, and Azure Cosmos DB stores conversation state and structured order/customer metadata. The agent uses OpenAI function calling to route between the two data sources and always returns citations. We'll build this from the ground up in Python. We’ll then package the agent in a Docker container and instrument it with Prometheus metrics and Grafana dashboards, so you can see what it’s doing in production. Architecture Overview The system has two phases: ingestion (getting documents into a searchable index) and runtime (the agent loop that answers questions). At runtime, the agent sits between the user and two tools, using function calling to decide which one (or both) to invoke: The runtime is a stateless service packaged as a Docker image, which is why session state lives in Cosmos DB, and it exposes metrics that Grafana visualizes. Step 1: Provision and Index Documents in Azure AI Search Create the index with both a vector field (for semantic similarity) and a semantic configuration (for ranking), since combining the two consistently outperforms either alone. Python # create_index.py from azure.search.documents.indexes import SearchIndexClient from azure.search.documents.indexes.models import ( SearchIndex, SimpleField, SearchableField, SearchField, SearchFieldDataType, VectorSearch, HnswAlgorithmConfiguration, VectorSearchProfile, SemanticConfiguration, SemanticPrioritizedFields, SemanticField, SemanticSearch ) from azure.core.credentials import AzureKeyCredential endpoint = "https://<your-search-service>.search.windows.net" credential = AzureKeyCredential("<admin-key>") index_client = SearchIndexClient(endpoint, credential) fields = [ SimpleField(name="id", type=SearchFieldDataType.String, key=True), SearchableField(name="content", type=SearchFieldDataType.String), SimpleField(name="source_url", type=SearchFieldDataType.String), SearchField( name="content_vector", type=SearchFieldDataType.Collection(SearchFieldDataType.Single), searchable=True, vector_search_dimensions=1536, vector_search_profile_name="default-profile", ), ] vector_search = VectorSearch( profiles=[VectorSearchProfile(name="default-profile", algorithm_configuration_name="hnsw-config")], algorithms=[HnswAlgorithmConfiguration(name="hnsw-config")], ) semantic_config = SemanticConfiguration( name="default-semantic", prioritized_fields=SemanticPrioritizedFields( content_fields=[SemanticField(field_name="content")] ), ) index = SearchIndex( name="support-docs-index", fields=fields, vector_search=vector_search, semantic_search=SemanticSearch(configurations=[semantic_config]), ) index_client.create_or_update_index(index) print("Index created.") Now embed and upload chunks. Chunking matters more than most tutorials admit — 512-token chunks with ~15% overlap is a reasonable default for policy/support documents. Python # ingest.py from openai import AzureOpenAI from azure.search.documents import SearchClient from azure.core.credentials import AzureKeyCredential import uuid aoai = AzureOpenAI( azure_endpoint="https://<your-foundry-resource>.openai.azure.com", api_key="<key>", api_version="2024-10-21", ) search_client = SearchClient( endpoint=endpoint, index_name="support-docs-index", credential=AzureKeyCredential("<admin-key>"), ) def chunk_text(text: str, max_tokens: int = 512, overlap: int = 80): words = text.split() step = max_tokens - overlap return [" ".join(words[i:i + max_tokens]) for i in range(0, len(words), step)] def embed(text: str) -> list[float]: resp = aoai.embeddings.create(model="text-embedding-3-small", input=text) return resp.data[0].embedding def ingest_document(text: str, source_url: str): docs = [] for chunk in chunk_text(text): docs.append({ "id": str(uuid.uuid4()), "content": chunk, "source_url": source_url, "content_vector": embed(chunk), }) search_client.upload_documents(documents=docs) with open("return_policy.txt") as f: ingest_document(f.read(), source_url="policies/return_policy.txt") Step 2: Structured Lookups in Cosmos DB Not every question is a document-retrieval problem. "Where's my order?" is a point lookup. Store operational data in Cosmos DB, partitioned by customer_id for predictable query performance. Python # cosmos_setup.py from azure.cosmos import CosmosClient, PartitionKey client = CosmosClient("<cosmos-endpoint>", credential="<cosmos-key>") db = client.create_database_if_not_exists("retail") orders = db.create_container_if_not_exists( id="orders", partition_key=PartitionKey(path="/customer_id"), offer_throughput=400, ) orders.upsert_item({ "id": "4471", "customer_id": "cust_9001", "status": "delayed", "delay_reason": "carrier weather disruption", "eta": "2026-08-02", }) Step 3: The Agent Loop With Function Calling This is the core piece: Azure OpenAI decides, per turn, whether to call search_docs, get_order, both, or neither. We define the tools as JSON schemas and let the model pick. Python # agent.py import json from openai import AzureOpenAI from azure.search.documents import SearchClient from azure.cosmos import CosmosClient aoai = AzureOpenAI( azure_endpoint="https://<your-foundry-resource>.openai.azure.com", api_key="<key>", api_version="2024-10-21", ) search_client = SearchClient(endpoint, "support-docs-index", AzureKeyCredential("<admin-key>")) cosmos = CosmosClient("<cosmos-endpoint>", credential="<cosmos-key>") orders_container = cosmos.get_database_client("retail").get_container_client("orders") tools = [ { "type": "function", "function": { "name": "search_docs", "description": "Semantic search over policy and support documents. Returns passages with citations.", "parameters": { "type": "object", "properties": {"query": {"type": "string"}, "required": ["query"], }, }, }, { "type": "function", "function": { "name": "get_order", "description": "Look up a customer order by ID for status, ETA, and delay reason.", "parameters": { "type": "object", "properties": { "order_id": {"type": "string"}, "customer_id": {"type": "string"}, }, "required": ["order_id", "customer_id"], }, }, }, ] def search_docs(query: str): results = search_client.search( search_text=query, query_type="semantic", semantic_configuration_name="default-semantic", top=3, ) return [{"content": r["content"], "source": r["source_url"]} for r in results] def get_order(order_id: str, customer_id: str): item = orders_container.read_item(item=order_id, partition_key=customer_id) return {"status": item["status"], "reason": item.get("delay_reason"), "eta": item.get("eta")} def run_agent(user_message: str, customer_id: str, history: list): messages = history + [{"role": "user", "content": user_message}] response = aoai.chat.completions.create( model="gpt-4o", messages=messages, tools=tools, tool_choice="auto", ) msg = response.choices[0].message if msg.tool_calls: messages.append(msg) for call in msg.tool_calls: args = json.loads(call.function.arguments) if call.function.name == "search_docs": result = search_docs(**args) elif call.function.name == "get_order": args["customer_id"] = customer_id # enforce, don't trust model-provided id result = get_order(**args) messages.append({ "role": "tool", "tool_call_id": call.id, "content": json.dumps(result), }) final = aoai.chat.completions.create(model="gpt-4o", messages=messages) return final.choices[0].message.content return msg.content A few things worth calling out: Never trust model-supplied identity fields. customer_id is overwritten server-side before hitting Cosmos DB, even though the model could technically supply one — this prevents cross-tenant data leaks via prompt injection.tool_choice="auto" lets the model skip tools entirely for small talk, avoiding unnecessary search calls.The second chat.completions.create call (after tool results are appended) is what produces the grounded, cited final answer. Step 4: Session State in Cosmos DB Conversation history should not live in application memory — store it in Cosmos DB so any instance of the service, including multiple Docker containers behind a load balancer, can resume a session. Python # session_store.py def load_history(session_id: str) -> list: container = cosmos.get_database_client("retail").get_container_client("sessions") try: item = container.read_item(item=session_id, partition_key=session_id) return item["messages"] except Exception: return [] def save_history(session_id: str, messages: list): container = cosmos.get_database_client("retail").get_container_client("sessions") container.upsert_item({"id": session_id, "messages": messages}) Comparing Retrieval Strategies Choosing between search modes is a real architectural decision, not just a config toggle: Retrieval ModeBest forWeaknessKeyword (BM25)Exact terms, SKUs, error codesMisses paraphrased queriesPure vector searchConceptual / paraphrased questionsCan surface plausible-but-wrong matchesHybrid (keyword + vector)General-purpose document Q&ASlightly higher latency than either aloneSemantic ranking on top of hybridConversational, nuanced queriesAdds a reranking pass; not needed for simple lookupsStructured query (Cosmos DB point read)Known-ID lookups (orders, accounts)Not applicable to unstructured content For most support/document agents, hybrid retrieval with semantic ranking is the right default, reserving structured lookups for anything with a stable ID. Production Considerations Grounding failures are silent failures. If retrieval returns weak matches, the model can still generate a fluent but wrong answer — always surface citations so users (and evaluators) can catch this.Access control matters. If your index mixes content from different permission levels, filter search results by the caller's identity before they reach the model, not after.Rising input tokens. Retrieved passages add to the prompt on every call — budget for this in both cost and latency.Observability in Grafana. Track tool-call rates, retrieval latency, and token usage in Grafana, and alert on spikes. References Retrieval-augmented generation (RAG) and indexes in Microsoft Foundry — Microsoft LearnRAG and Generative AI overview for Azure AI Search — Microsoft LearnRAG with Agent Framework and Semantic Kernel — Microsoft LearnRAG using Semantic Kernel with Azure OpenAI and Azure Cosmos DB for MongoDB vCore — Microsoft Learn ShowsStep-by-Step Guide on Building Agentic RAG with Semantic Kernel and Azure AI Search — Data Science Collective
AI is changing the way we code. Last week, a friend told me he completed a user story to add new columns to a table and tested it in under an hour. Upstream teams are now shipping changes in days that once took months. For data pipelines that sit downstream of these fast-moving systems, this speed is a new design constraint. One of the potential solutions is to build dynamic data pipelines that are built to keep up. What Is a Dynamic Data Pipeline Dynamic data pipelines are data processing workflows that adapt to changing data and business logic. They use parameterized templates and config files to run custom pipelines on the go as per data requirements. These pipelines are better suited to today’s fast-changing data environment. staticdynamic Hard-coded, fixed tasks Configuration-driven, flexible logic Breaks on schema changes Adapts automatically to schema changes Requires coding and redeployment to handle business logic changes Requires just config changes to handle business logic changes High maintenance with constant updates to prevent failure Low maintenance, may require some config changes from time to time Quick to build the pipeline Takes more time upfront for initial coding but once built, it’s easy to add more tasks Sample Dynamic Data Pipeline Architecture I'll walk you through a sample architecture to show how dynamicity is configured into a data pipeline. Sample dynamic data pipeline architecture 1. Configuration The key configurations of a dynamic data pipeline can be stored in Git (YAML/JSON) and a database. Git stores the pipeline logic, and a PostgreSQL/MongoDB database stores the dynamic values used when the pipeline runs. For example, you may want to build a standardized data ingestion workflow; the logic for it will be in Git, but the list of tables to be ingested can be stored in the database. So when an ingestion workflow initiates, it triggers multiple workflows for each table entry. Let’s understand it with code: YAML config of dynamic data ingestion pipeline: YAML pipeline: name: dynamic_data_ingestion_pipeline version: “1.0” source: type: "{{ source_type }" connection: host: "{{ source_host }" database: "{{ source_database }" table: "{{ source_table }" schema: on_new_column_detected: “add” # automatically add new columns on_column_removal_detected: “ignore” # ignore column removal on_type_change_detected: “cast” # cast the datatype on change transformations: name: “if_filter_null” enabled: “{{ if_filter_null }” column: “{{ null_filter_column }” target: type: “{{ target_type }” dataset: “{{ target_dataset }” table: “{{ target_table }” runtime: schedule: “{{ cron_schedule }” retry_attempts: 3 alert_on_failure: true Database entry of dynamic data ingestion pipeline: Markdown -- Table: pipeline_config | id | source_type | source_host | source_database | source_table | if_filter_null | null_filter_column | column_mapping | target_type | target_dataset | target_table | cron_schedule | |----|-------------|------------------------|-----------------|--------------|----------------|--------------------|----------------|-------------|----------------|-----------------------|---------------| | 1 | postgres | db.customers.prod.com | customers_db | customers | true | customer_id | true | bigquery | analytics | customers_details | 0 * * * * | | 2 | s3 | s3.amazonaws.com | transactions_db | transactions | false | null | true | redshift | finance | transactions_details | 0 0 * * * | These configurations are what make the pipeline dynamic. Instead of hardcoding column names or schema expectations into the pipeline code, the script reads the schema at runtime and adapts automatically, handling new, removed, or renamed columns without breaking. And whenever any changes happen upstream, all that's needed is a config table update. No pipeline rework, no redeployment. Tip: Make sure the database fetch step for dynamic pipelines happens during execution inside a task, not during DAG parsing. Because DAG parsing occurs on every scheduler heartbeat, it can crash your orchestrator. 2. Orchestrator After those configurations are ready, you can design the dynamic pipeline in an orchestrator (such as Airflow, Prefect, or Dagster). It generates pipelines based on configurations defined in YAML/database and dynamically creates the required tasks. Sample flow in orchestrator: Plain Text Reads YAML template ↓ Loops over DB config table ↓ One sub DAG per row with all tasks Dynamic DAG skeleton in Airflow: Python with open("/opt/airflow/configs/pipeline_config.yaml", "r") as f: raw_yaml_template = f.read() def get_pipeline_settings(): connection = psycopg2.connect( host="company_db.com", database="metadata_db", user="sys_admin", password="******" ) cur = connection.cursor() cur.execute(""" SELECT id, source_type, source_host, source_database, source_table, if_filter_null, null_filter_column, target_type, target_dataset, target_table, cron_schedule FROM pipeline_config """) rows = cur.fetchall() field_names = [col[0] for col in cur.description] cur.close() connection.close() return [dict(zip(field_names, row)) for row in rows] all_pipeline_settings = get_pipeline_settings() all_configs = [] for pipeline_settings in all_pipeline_settings: resolved_yaml = Template(raw_yaml_template).render(**pipeline_settings) pipeline_config = yaml.safe_load(resolved_yaml) all_configs.append(pipeline_config) def task1 (): #logic def task2 (): #logic def task3 (): #logic task1= PythonOperator( task_id="task1", python_callable=task1, dag=ingestion_dag ) task2= PythonOperator( task_id="task2", python_callable=task2, dag=ingestion_dag ) task3= PythonOperator( task_id="task3", python_callable=task3, dag=ingestion_dag ) # ========================================================== # DAG FLOW # ========================================================== task1>> task2>> task3 3. Executor Your pipeline logic will usually run on compute engines such as Apache Spark, BigQuery, Snowflake, or Presto/Trino, depending on your infrastructure and pipeline configuration. The output will be stored in cloud systems such as Amazon S3, Google Cloud Storage, or Azure Data Lake Storage. 4. Monitoring Depending on your environment, built-in logging platforms are available, such as AWS CloudWatch, Google Cloud Monitoring, or Azure Monitor. For advanced monitoring, tools like Logstash and Elasticsearch help to aggregate logs and perform analytics. Should Every Data Pipeline Be Dynamic No, not every pipeline needs to be dynamic. It will become very tough to maintain all dynamic pipelines. And then it doesn’t make sense to write this logic for every workflow. In some cases, explicit hardcoded logic is actually simpler to read and maintain. Dynamic pipelines are a good choice if: You have 20+ pipelines with similar logic that differ only in a few configuration valuesYour data source schema changes frequentlyYour pipelines have frequent business rule changesYou can handle pipeline failures in isolation, meaning one bad config cannot break all pipelines at once.You can easily monitor DAGs even after the shift to dynamicYou have non-engineers such as analysts, PMs, or business users who need to make changes to DAG configurations Best Practices to Maintain Dynamic Data Pipelines Dynamic data pipelines are still a relatively new concept. Here are some best practices to help you maintain them. Avoid Doing Too Much in One DAG One single DAG generating 1000s of sub-DAGs will be a maintenance nightmare. Also, if it’s hitting any API or shared services, it can cause overload. Limit how many sub-DAGs one parent DAG can initiate. How to implement this? Group pipelines based on business function or sources. For example, you can have one parent DAG for all tables from the same source. Alternatively, you can group pipelines by business function, such as marketing, finance, or reporting. Maintain Version History Given the dynamic nature of sources and pipelines, schemas and sources can change over time. The DAG must maintain version history, and any backfill must respect the configuration that was active when the partition originally ran. How to implement this? Always store the configuration associated with each run so you can trace it for any backfills. You can also design your pipeline config table to maintain history by adding "valid_from" and "valid_to" columns with the exact timelines when that config was active. Monitor at Sub-DAG Level Dynamic pipelines are essentially sets of tasks grouped together and generated multiple times based on configuration. For example, an ingestion pipeline is a set of tasks to extract, transform, and load data for different tables. Monitoring must be done at the sub-DAG level rather than the individual task level. That means ingestion must be tracked separately for each source. How to implement this? Maintain separate logs and tracking for each sub-DAG. For example, you can push a new entry for every sub-DAG with metadata like source name, config ID, row count, and duration to a monitoring table or tool like Datadog or Grafana. One Sub-DAG Should Not Block the Rest A dynamic pipeline can trigger hundreds of sub-DAGs, but each sub-DAG must operate as an independent entity. A failed or skipped sub-DAG should not affect other sub-DAGs generated from the same pipeline. How to implement this? Set failure rules at the sub-DAG level to prevent one failed task from blocking the rest. For example, configurations like on_failure_callback in Airflow can be set at the sub-DAG level to isolate failures and let the rest of the sub-DAGs run fine. Dynamic Data Pipelines Are No Longer a Nice-to-Have With AI, companies will soon be building high-velocity products. That means new features will get added at a pace that leaves rigid downstream systems struggling to keep up. The pipelines that run smoothly won't be the ones built for today's schema/rules. It’ll be the ones built to adapt to whatever comes next.
Feature stores solve a problem that every machine learning (ML) team eventually hits: the features you compute during training are different from the ones you retrieve at serving time. Feast is an open-source feature store that bridges that gap, handling the registry, materialization, and online retrieval so a model sees the same data in production as it did during training. Often, Feast tutorials pair it with a relational database or a cloud data warehouse. In this article, we'll do something different. We'll use Neo4j Desktop as the feature computation engine. Five features are ordinary aggregations that Cypher handles as easily as SQL. The sixth, a customer community_id, comes from graph algorithms: customers with similar purchase mixes are found with Graph Data Science (GDS) node similarity and then grouped with Louvain community detection. We'll hand all six features to Feast, which materializes them into a SQLite online store, serves them at inference time, and builds point-in-time correct training data. We'll also test, on held-out orders, whether the graph feature tells a model anything that a plain favorite_category column doesn't. The full source code is available on GitHub. Architecture Figure 1. Architecture As shown in Figure 1, the architecture has three layers. Neo4j Desktop handles feature computation: standard aggregations via Cypher and a graph-native community feature via the Graph Data Science (GDS) library. The results, computed at several points in time, land in a Parquet file, which acts as Feast's offline store. Feast then materializes the latest values from Parquet into SQLite for low-latency online serving. At inference time, a single get_online_features() call retrieves the full feature vector, regardless of whether a feature came from a Cypher query or a GDS algorithm. For training data, get_historical_features() reads the same offline store. The key insight is that Feast doesn't know or care how the Parquet file was produced. What We're Building We'll work with a synthetic e-commerce dataset of 100 customers, 3,000 orders, and 5 product categories (Electronics, Clothing, Home, Books, and Sports) spread over the last 90 days. Each customer belongs to a hidden segment defined by a primary and a secondary category. For 55% of their orders the category is the segment's primary one, for 30% it is the secondary one, and for the remaining 15% it is drawn uniformly from all five categories. There are five segments: Electronics (primary) and Home (secondary)Electronics and SportsClothing and BooksClothing and HomeBooks and Sports The design is deliberate. Two segments share Electronics as their primary category and two share Clothing, so a customer's single most-bought category can't tell those segments apart. Only the combination of categories can. To make the churn example meaningful, 18 customers are also made to go quiet: 8 have not ordered for more than 60 days and 10 for more than 30 days. The segment is stored on each Customer node, but only so we can check our results later. It is never used to compute any feature. We'll compute six features per customer, as of a given date: total_orders – Number of orders placed up to the as-of datetotal_spent – Total order valueavg_order_value – Mean order valuedays_since_last_order – Recency, measured from the as-of datefavorite_category – The category with the most orderscommunity_id – Graph-native feature from node similarity and Louvain community detection We compute them at three as-of dates (60 days ago, 30 days ago, and today), so that Feast can later reconstruct what was known at any point in time. Setup A virtual environment is highly recommended. For example: Shell python3 -m venv ~/feast-env source ~/feast-env/bin/activate The notebook was run against Neo4j 2026.07.0 (Enterprise) and GDS 2026.07.0. We need Neo4j Desktop running with the Graph Data Science (GDS) plugin installed. In Neo4j Desktop, go to Plugins and install the Graph Data Science Library. Credentials go in environment variables before running the Jupyter notebook: Shell export NEO4J_URI="bolt://localhost:7687" export NEO4J_USERNAME="neo4j" export NEO4J_PASSWORD="your-password" export NEO4J_DATABASE="your-database" Warning: The notebook removes all data, indexes, and constraints from the database named in NEO4J_DATABASE before loading its sample data. Use a database you are happy to wipe. We'll connect to Neo4j with the standard driver: Python driver = GraphDatabase.driver( NEO4J_URI, auth = (NEO4J_USERNAME, NEO4J_PASSWORD) ) Feature Definitions Feast needs two files: a project config and a feature view declaration. The config points to SQLite for the online store: YAML project: ecommerce_features registry: feature_repo/registry.db provider: local online_store: type: sqlite path: feature_repo/online_store.db The feature definitions declare the customer_id entity and a feature view with all six features, including community_id as an Int64: Python from feast import Entity, FeatureView, Field, FileSource from feast.types import Float32, Int64, String, ValueType from datetime import timedelta customer = Entity( name = "customer_id", description = "Customer identifier", value_type = ValueType.STRING ) customer_features = FeatureView( name = "customer_features", entities = [customer], ttl = timedelta(days = 90), schema = [ Field(name = "total_orders", dtype = Int64), Field(name = "total_spent", dtype = Float32), Field(name = "avg_order_value", dtype = Float32), Field(name = "days_since_last_order", dtype = Int64), Field(name = "favorite_category", dtype = String), Field(name = "community_id", dtype = Int64), ], online = True, source = FileSource( path = "data/customer_features.parquet", timestamp_field = "event_timestamp" ), ) From Feast's perspective, community_id is just another integer column. It doesn't know it took graph algorithms to produce it. Loading Data into Neo4j Rather than rows in a table, we'll model the data as a graph. Figure 2 shows the structure. Figure 2. Graph Data Model Customer and Order nodes connect via PLACED relationships. Each Order connects to a Category node via IN_CATEGORY. We don't store a direct customer-to-category relationship. Instead, the GDS projection derives one at query time, so that it can be limited to orders placed before a given date. All timestamps are timezone-aware (UTC). Cypher UNWIND $orders AS o MERGE (c:Customer {id: o.customer_id}) ON CREATE SET c.segment = o.segment MERGE (cat:Category {name: o.product_category}) CREATE (ord:Order { id: o.order_id, value: o.order_value, timestamp: datetime(o.order_timestamp), quantity: o.quantity }) MERGE (c)-[:PLACED]->(ord) MERGE (ord)-[:IN_CATEGORY]->(cat) Standard Features via Cypher The first five features come from two Cypher queries, both limited to orders placed on or before the as-of date $as_of. The main aggregation computes counts, totals, averages, and recency in a single pass: Cypher MATCH (c:Customer)-[:PLACED]->(o:Order) WHERE o.timestamp <= datetime($as_of) RETURN c.id AS customer_id, count(o) AS total_orders, sum(o.value) AS total_spent, avg(o.value) AS avg_order_value, duration.inDays(max(o.timestamp), datetime($as_of)).days AS days_since_last_order Favorite category requires a separate query, since we need to find the top category per customer and collect() the first result after ordering. Ties are broken alphabetically so that the result is deterministic: Cypher MATCH (c:Customer)-[:PLACED]->(o:Order)-[:IN_CATEGORY]->(cat:Category) WHERE o.timestamp <= datetime($as_of) WITH c.id AS customer_id, cat.name AS category, count(*) AS cnt ORDER BY customer_id, cnt DESC, category WITH customer_id, collect(category)[0] AS favorite_category RETURN customer_id, favorite_category The event_timestamp that Feast uses is the as-of date itself. In other words, the moment the features describe, rather than the time of the customer's last order. Graph-Native Feature: Community ID The sixth feature groups customers whose purchase mixes are similar. It takes four steps, all run inside Neo4j. Step 1: Project customers and categories. We use the current Cypher projection, which builds an in-memory graph with the gds.graph.project aggregation function and replaces the deprecated gds.graph.project.cypher procedure. Each customer-category relationship is weighted by the number of orders placed up to the as-of date: Cypher MATCH (c:Customer)-[:PLACED]->(o:Order)-[:IN_CATEGORY]->(cat:Category) WHERE o.timestamp <= datetime($as_of) WITH c, cat, count(o) AS order_count WITH gds.graph.project( $name, c, cat, { sourceNodeLabels: labels(c), targetNodeLabels: labels(cat), relationshipType: 'PURCHASED_IN', relationshipProperties: {order_count: order_count} } ) AS g RETURN g.graphName AS graph, g.nodeCount AS nodes, g.relationshipCount AS relationships Step 2: Find similar customers. GDS node similarity with the cosine metric, weighted by order count, scores two customers close to 1 when their category mixes point in the same direction. We keep each customer's 15 most similar customers above a cutoff of 0.8: Cypher CALL gds.nodeSimilarity.stream($name, $config) YIELD node1, node2, similarity RETURN gds.util.asNode(node1).id AS src, gds.util.asNode(node2).id AS dst, similarity Python config = { "similarityMetric": "COSINE", "relationshipWeightProperty": "order_count", "topK": SIMILARITY_TOP_K, "similarityCutoff": SIMILARITY_CUTOFF, } Step 3: Project a customer-to-customer graph from those pairs, weighted by similarity: Cypher UNWIND $pairs AS p MATCH (a:Customer {id: p.src}), (b:Customer {id: p.dst}) WITH gds.graph.project( $name, a, b, { sourceNodeLabels: labels(a), targetNodeLabels: labels(b), relationshipType: 'SIMILAR', relationshipProperties: {similarity: p.similarity} }, {undirectedRelationshipTypes: ['*']} ) AS g RETURN g.graphName AS graph, g.nodeCount AS nodes, g.relationshipCount AS relationships Step 4: Run Louvain on that graph, using the similarity scores as relationship weights. consecutiveIds gives us small, tidy community IDs: Cypher CALL gds.louvain.stream($name, $config) YIELD nodeId, communityId WITH gds.util.asNode(nodeId) AS n, communityId WHERE n:Customer RETURN n.id AS customer_id, communityId AS community_id A customer with no sufficiently similar neighbor isn't in the similarity graph, so we give each such customer a community of their own. The two settings, SIMILARITY_TOP_K (15) and SIMILARITY_CUTOFF (0.8), are the only knobs, and we didn't tune them. Why not run Louvain directly on the customer-category graph and skip the similarity step? We tried, and the next section shows why we didn't keep it. Which Grouping Recovers the Hidden Segments? We compared four ways of grouping the same customers against the hidden segments, using the Adjusted Rand Index (ARI), where 1.0 means identical groupings and values near 0 mean no better than chance: Plain Text Grouping Groups ARI vs hidden segments ---------------------------------------------------- -------- ------------------------ 1. favorite_category 5 0.52 2. Louvain, customer-category graph, unweighted 2 0.08 3. Louvain, customer-category graph, weighted 3 0.41 4. Louvain, customer-similarity graph (community_id) 5 0.98 A plain favorite_category scores 0.52. It can't separate segments that share a primary category and the cross-tabulation shows exactly that: Electronics lumps together 21 customers from one segment and 22 from another and Clothing does the same with 20 and 15. Plain Text favorite_category vs hidden segment (latest snapshot): segment Books + Sports Clothing + Books Clothing + Home Electronics + Home Electronics + Sports favorite_category Books 14 2 0 0 0 Clothing 0 20 15 0 0 Electronics 0 0 0 21 22 Home 0 0 0 2 0 Sports 2 0 0 0 2 Louvain run directly on the customer-category graph does worse than that, with an ARI of 0.41 when weighted by order count and 0.08 when unweighted. We didn't investigate why, but the likely cause is that two segments sharing a primary category both attach to the same category node, which pulls them together. Without weights, every customer is linked to every category equally, and there is little left to separate them. The similarity-graph method scores 0.98 and finds five communities that line up with the five segments: Plain Text community_id vs hidden segment (latest snapshot): segment Books + Sports Clothing + Books Clothing + Home Electronics + Home Electronics + Sports community_id 0 0 0 0 23 0 1 0 1 15 0 0 2 0 21 0 0 0 3 0 0 0 0 24 4 16 0 0 0 0 Figure 3 shows the five communities, each labeled with the segment that makes up most of it. Figure 3. Communities Found by Node Similarity and Louvain. Only one customer lands in the wrong community. The algorithm was never given the segments, so it recovered them from purchase behavior alone. Does the Community Feature Add Predictive Signal? Recovering hidden segments is reassuring, but a feature earns its place by helping predictions. We test that without training a model, using only the features as they were 30 days ago. For each group of customers, we estimate a mix of product categories from orders up to that date, then score how well that mix predicts the category of every order placed afterward. The score is log-loss, the average of the negative log of the probability given to the category actually bought, so lower is better. There is no leakage: both favorite_category and community_id were computed from orders placed before the cutoff. Python def heldout_losses(orders, snapshot, cutoff_ts, group_col, alpha = 1.0): """How well does a grouping of customers predict the category of FUTURE orders? For each group, estimate the category mix from orders up to `cutoff_ts` (with add-`alpha` smoothing). Then score each later order by the negative log of the probability that mix gave its category. Lower is better. Returns one loss per held-out order.""" groups = snapshot[["customer_id", group_col]].rename(columns = {group_col: "group"}) train = orders[orders["ts"] <= cutoff_ts].merge(groups, on = "customer_id") test = orders[orders["ts"] > cutoff_ts].merge(groups, on = "customer_id") counts = (train.groupby(["group", "category"]).size().unstack(fill_value = 0) .reindex(columns = CATEGORIES, fill_value = 0) + alpha) probs = counts.div(counts.sum(axis = 1), axis = 0) idx = pd.MultiIndex.from_arrays([test["group"], test["category"]]) p = probs.stack().reindex(idx).fillna(1 / len(CATEGORIES)).values return -np.log(p) We compare no grouping at all, favorite_category, community_id and the hidden segment as an upper bound that no real feature could beat. A paired bootstrap gives a confidence interval for the improvement of community_id over favorite_category: Python diff = losses["favorite_category"] - losses["community_id"] rng = np.random.default_rng(SEED) boot = np.array([rng.choice(diff, size = len(diff), replace = True).mean() for _ in range(2000)]) low, high = np.percentile(boot, [2.5, 97.5]) Plain Text Held-out orders (placed after the 30-day snapshot): 827 Grouping Groups Held-out log-loss (lower is better) ------------------------------------- -------- ------------------------------------- No grouping (everyone shares one mix) 1 1.565 favorite_category 5 1.171 community_id 5 1.025 Hidden segment (oracle upper bound) 5 1.009 Improvement of community_id over favorite_category: 0.146 (95% CI 0.110 to 0.180) Grouping customers by favorite_category cuts the loss from 1.565 to 1.171. Grouping by community_id cuts it to 1.025, close to the 1.009 of the hidden segments. The improvement over favorite_category is 0.146, with a 95% confidence interval of 0.110 to 0.180. The reason is intuitive: knowing a customer's secondary category tells you what they are likely to buy after their favorite, and the community captures that while a single favorite category can't. Some caveats apply. The data are synthetic and designed so the combination of categories matters, so the size of the gain reflects that design, not what you would see in real orders. The predictor is a simple count-based one rather than a trained model. The bootstrap resamples individual orders and ignores that orders from one customer are related, so the interval is somewhat optimistic. And this is one dataset from one random seed. It is also worth saying what happened before we changed the data. In an earlier version of this example, every customer had a single preferred category. There, community_id and favorite_category agreed exactly (ARI 1.00), so the graph feature added nothing. The gain here appears when group membership depends on a combination of categories, which is the situation where a single aggregate column falls short. Snapshots and Stable Community IDs To support point-in-time retrieval, we compute all six features at three as-of dates: 60 days ago, 30 days ago, and today, and stack the results. Louvain runs independently for each date, and that creates a problem. Community IDs are arbitrary labels, and a label such as 3 in one run need not mean the same group in the next. A model trained on one snapshot would then be served IDs with a different meaning. We solve this by relabeling each snapshot to match the previous one, assigning each new community the old ID it overlaps with most: Python def align_communities(current, reference): """Relabel `current` community IDs to match `reference` by greatest member overlap.""" merged = current[["customer_id", "community_id"]].merge( reference[["customer_id", "community_id"]], on = "customer_id", suffixes = ("_cur", "_ref") ) overlap = (merged.groupby(["community_id_cur", "community_id_ref"]) .size().reset_index(name = "n") .sort_values("n", ascending = False)) mapping, used = {}, set() for _, r in overlap.iterrows(): cur, ref = int(r["community_id_cur"]), int(r["community_id_ref"]) if cur not in mapping and ref not in used: mapping[cur] = ref used.add(ref) # Any community with no counterpart in the reference gets a fresh ID next_id = int(max(reference["community_id"].max(), max(mapping.values(), default = -1))) + 1 for cur in current["community_id"].unique(): if int(cur) not in mapping: mapping[int(cur)] = next_id next_id += 1 aligned = current.copy() aligned["community_id"] = aligned["community_id"].map(lambda x: mapping[int(x)]).astype("int64") return aligned Python for days in SNAPSHOT_OFFSETS_DAYS: as_of = now - timedelta(days = days) df, stats = compute_features_as_of(as_of) if reference is not None: df = align_communities(df, reference) reference = df[["customer_id", "community_id"]] frames.append(df) features_df = pd.concat(frames, ignore_index = True) The notebook prints how well each snapshot recovers the segments and how much the labels would have disagreed without alignment: Plain Text As of 2026-08-27 (60 days ago): 100 customers, 844 similar pairs, 5 communities, ARI vs hidden segments 0.95 As of 2026-09-26 (30 days ago): 100 customers, 847 similar pairs, 5 communities, ARI vs hidden segments 0.95 label agreement with previous snapshot: raw 65% -> aligned 96% As of 2026-10-26 ( 0 days ago): 100 customers, 856 similar pairs, 5 communities, ARI vs hidden segments 0.98 label agreement with previous snapshot: raw 63% -> aligned 99% Each snapshot finds five communities and recovers the hidden segments well (ARI 0.95, 0.95 and 0.98), even the earliest one, which has far fewer orders per customer. Without alignment, only 65% and 63% of customers kept their raw label from one run to the next. After alignment, 96% and 99% did. The remainder are customers whose community changed as more orders arrived. Because community_id is an arbitrary label, a model should treat it as a categorical value (one-hot or embedding), never as an ordered number. Merging Features and Writing to Parquet The three snapshots are written to a single Parquet file, one row per customer per as-of date: Python features_df.to_parquet("feature_repo/data/customer_features.parquet", index = False) Plain Text Wrote 300 rows to Parquet. event_timestamp 2026-08-27 18:02:57.920521+00:00 100 2026-09-26 18:02:57.920521+00:00 100 2026-10-26 18:02:57.920521+00:00 100 From here, Feast takes over. It doesn't know that community_id came from graph algorithms and it's just another column in the Parquet file. Feast Apply and Materialize We'll apply the feature definitions and materialize into the SQLite online store: Python result = subprocess.run( ["feast", "-c", "feature_repo", "apply"], capture_output = True, text = True ) end_date = datetime.now(timezone.utc) start_date = end_date - timedelta(days = 90) result = subprocess.run( ["feast", "-c", "feature_repo", "materialize", start_date.isoformat(), end_date.isoformat()], capture_output = True, text = True ) feast apply registers the entity and feature view. feast materialize reads the Parquet file and loads the most recent value for each customer into SQLite, ready for low-latency retrieval. SQLite and Parquet are convenient for a local demo; in production you would swap in stores built for scale and concurrency. Feast keeps that largely a configuration change rather than a change to the feature code. Online Feature Retrieval We'll reinitialize the feature store and retrieve features for three customers, one active, one cooling, and one churned: Python fs = FeatureStore(repo_path = "feature_repo") feature_vector = fs.get_online_features( features = [ "customer_features:total_orders", "customer_features:total_spent", "customer_features:avg_order_value", "customer_features:days_since_last_order", "customer_features:favorite_category", "customer_features:community_id", ], entity_rows = [{"customer_id": cid} for cid in test_customers] ) The retrieved features look like this: Plain Text customer_id favorite_category total_spent total_orders days_since_last_order community_id avg_order_value ------------- ------------------- ------------- -------------- ----------------------- -------------- ----------------- CUST_0081 Books 7765.10 28 5 4 277.33 CUST_0007 Electronics 7000.73 26 35 0 269.26 CUST_0032 Clothing 6580.14 26 66 2 253.08 community_id appears alongside the standard aggregations, served identically through the same Feast interface. Using Features for Prediction A simple rule-based churn risk function shows how the features feed into inference. Recency drives the risk tier, and community_id is returned with the result so that downstream systems can segment or report by community: Python def predict_customer_churn(customer_id): """Use features for real-time churn prediction.""" features = fs.get_online_features( features = [ "customer_features:total_orders", "customer_features:total_spent", "customer_features:days_since_last_order", "customer_features:community_id", ], entity_rows = [{"customer_id": customer_id}] ).to_df() days_since = features["days_since_last_order"].iloc[0] community = features["community_id"].iloc[0] if days_since > 60: risk, action = "HIGH", "Send 20% discount" elif days_since > 30: risk, action = "MEDIUM", "Send personalized email" else: risk, action = "LOW", "Continue engagement" return { "customer_id": customer_id, "days_since_last_order": int(days_since), "churn_risk": risk, "action": action, "community_id": int(community), } predictions = [predict_customer_churn(c) for c in test_customers] print(tabulate(predictions, headers = "keys", tablefmt = "simple")) Plain Text customer_id days_since_last_order churn_risk action community_id ------------- ----------------------- ------------ ----------------------- -------------- CUST_0081 5 LOW Continue engagement 4 CUST_0007 35 MEDIUM Send personalized email 0 CUST_0032 66 HIGH Send 20% discount 2 The rule itself doesn't use community_id; the point is that serving is identical whichever way a feature was computed. One caveat applies to days_since_last_order: it was calculated when we materialized, so served a week later without re-materializing it would be a week out of date. In production, re-run materialization on a schedule, or store the last-order timestamp and compute recency at request time, for example with a Feast on-demand feature view. Point-in-Time Historical Features The other half of a feature store's job is building training data in which each row sees only what was known at that row's timestamp. We ask Feast for one customer's features at three timestamps: Python entity_df = pd.DataFrame({ "customer_id": [target] * 3, "event_timestamp": [ pd.Timestamp(now - timedelta(days = 45)), pd.Timestamp(now - timedelta(days = 20)), pd.Timestamp(datetime.now(timezone.utc)), ], }) historical_df = fs.get_historical_features( entity_df = entity_df, features = [ "customer_features:total_orders", "customer_features:total_spent", "customer_features:avg_order_value", "customer_features:days_since_last_order", "customer_features:favorite_category", "customer_features:community_id", ], ).to_df() Plain Text customer_id event_timestamp total_orders total_spent avg_order_value days_since_last_order favorite_category community_id ------------- -------------------------------- -------------- ------------- ----------------- ----------------------- ------------------- -------------- CUST_0081 2026-09-11 18:02:57.920521+00:00 12 3609.80 300.82 2 Books 4 CUST_0081 2026-10-06 18:02:57.920521+00:00 20 6086.00 304.30 6 Books 4 CUST_0081 2026-10-26 18:03:04.843967+00:00 28 7765.10 277.33 5 Books 4 Feast joins each timestamp to the most recent snapshot at or before it, so the customer's order count grows from 12 to 20 to 28 as time moves forward. Every snapshot, including its community_id, was computed using only orders placed up to its own date, so none of these rows contain information from the future. The event_timestamp shown is the one we asked about, not the snapshot's own date. Gotchas and Lessons Learned A few things worth knowing before you run the notebook. Dependency conflicts. To avoid incompatibilities between Feast and the graphdatascience Python client, the solution is to drop graphdatascience entirely and call GDS via raw Cypher through the Neo4j driver. Every GDS procedure used here (gds.graph.project, gds.nodeSimilarity.stream, gds.louvain.stream, gds.graph.drop) is callable this way and the result is a cleaner install. GDS notifications. GDS warns that the schema field returned by gds.graph.drop is deprecated. The warning is harmless, but it repeats every time the procedure is called. Rather than silencing all server notifications on the driver, we yield only the field we need: CALL gds.graph.drop($name, false) YIELD graphName RETURN graphName. With that change no deprecation notifications appeared in our run. Choose the graph that matches the question. Louvain on the customer-category graph groups customers by which categories they are attached to, and struggles when groups share a category. Comparing whole category mixes with node similarity first, then clustering that customer-to-customer graph, is what separated the overlapping segments. Community IDs are labels, not values. Independent Louvain runs relabel the same groups, as the 65% and 63% agreement above shows. Align IDs across snapshots, and treat them as categorical in your model. Check your environment variables. The notebook reads its connection details from NEO4J_* environment variables. Stale variables left over from another project can point the notebook at a different server, which can show up as confusing errors that have nothing to do with your code. Pandas version. Feast 0.66.0 requires pandas<3 and >=1.4.3. Summary Neo4j works well as a feature computation engine alongside Feast. The Cypher queries for standard aggregations are comparable in complexity to their SQL equivalents, and GDS lets you add graph-algorithm output to the same feature set with a few projections and procedure calls. In our example, node similarity followed by Louvain recovered the hidden customer segments with an ARI of 0.98, where a plain favorite-category column managed 0.52, and Feast served the result like any other column. On held-out orders, grouping customers by community_id predicted category mix better than grouping by favorite_category, by 0.146 log-loss (95% interval 0.110 to 0.180). Those numbers come from synthetic data built so that combinations of categories matter, and from a simple count-based predictor, so treat them as a demonstration of the pipeline and the method rather than a benchmark. When we first used single-category preferences, the graph feature was redundant with favorite_category, which is a useful reminder to test whether a graph feature adds something before building it into a pipeline. The pattern generalizes beyond community detection. PageRank scores and node embeddings (for example from FastRP, node2vec or GraphSAGE) can be written back to node properties and flow through the same pipeline, with embeddings stored as array-valued features. Two cautions apply. Some graph outputs, such as shortest-path distances, are defined between pairs of nodes, so they need a fixed reference node before they can become a per-entity feature. And graph features are computed in batch, so a brand-new customer has none until the next run. The offline store (Parquet) and the serving layer (Feast) stay the same regardless of how the features were computed. The full source code is available on GitHub.
Why Single-Gateway Payment Pipelines Break Under Concurrency When building enterprise retail and commerce platforms, many engineering teams treat payment processing as a standard REST API call. A point-of-sale (POS) terminal initiates a checkout request, the application layer sends the payload to a payment service provider (PSP), and the resulting network response (200 OK, 402 Declined, or 504 Gateway Timeout) determines order completion. It feels pretty simple, but there are three particular systemic weaknesses that need to be considered during high transactional traffic: Slow processors eat up your server resources: Payment gateways rarely crash cleanly without warning. Instead, response times gradually stretch from a fast 300ms baseline up to 8 or 10 seconds before failing. When your checkout service sits there waiting on hanging connections, it ties up worker threads and drains your server connection pools. Before long, your servers run out of capacity to handle new requests, turning an outside payment delay into a site-wide checkout freeze.The timeout blind spot: If a network connection drops halfway through a request, your server has no idea whether the card was actually charged. Triggering an immediate client-side retry risks hitting the customer with a duplicate charge. Aborting the order immediately loses revenue, even if a backup processor is sitting completely idle.Zero routing flexibility: When you're tied to one pipe, you take every hit directly. You can't steer transactions toward local acquiring banks to trim interchange fees, and you can't route around regional bank outages when an acquirer starts sputtering. Designing a Dynamic Multi-Rail Routing Engine The core shift here is treating payment processors as swappable endpoints rather than permanent integrations. We score and select gateways on the fly based on how they're performing in real time. Real-Time Routing Logic Before sending an authorization payload downstream, the engine runs through three rules: BIN and geography: The routing service reads the first 6 to 8 digits of the card number (BIN) against a local card table. Domestic cards route to local acquiring partners to keep interchange fees down and reduce false declines triggered by issuing-bank fraud filters.Rolling latency thresholds: We don't wait for a hard 500 error to react. The engine monitors p95 and p99 response times over rolling 60-second windows. If our primary processor averages over 1,500ms, non-essential traffic automatically shifts to a backup rail before shoppers ever see a spinning wheel at checkout.Circuit breakers: Every third-party integration is wrapped in a standard circuit breaker: Closed: Normal traffic routes to the primary processor.Open: If 5xx errors or timeouts cross 5% over 100 requests, the breaker trips to open. Inbound traffic bypasses that processor entirely, preserving server resources.Half-open: After a 30-second cool-down, small test probes check if the provider has recovered before routing production traffic back. Handling Distributed Idempotency and Preventing Double Charges The biggest hazard in multi-rail failover is the mid-flight race condition. If Gateway A slows down, your orchestrator might log a timeout after 3 seconds. If you switch to Gateway B without confirming the state of Gateway A, Gateway A might finish processing in the background. Now the customer gets billed on both rails, and your customer support queue explodes. How to Prevent It Deterministic Request Fingerprinting Every inbound transaction must carry an immutable idempotency key (UUIDv4) paired with a SHA-256 hash of the order details (amount, currency, merchant ID). If the same key arrives with different order values, reject it immediately with an HTTP 422 error. Atomic Redis Mutex Before calling any gateway API, grab a distributed lock using Redis (SETNX) with a short expiration (around 60 seconds). If a duplicate request arrives while the status is still in progress, return an HTTP 409 Conflict. The Void-Before-Switch Rule Never fail over blindly. If the primary processor times out, the system must issue a synchronous cancellation or void call to that processor first. Only when you receive a confirmed cancellation or a definitive decline should you release the lock and route the transaction to the fallback rail. If the void call also times out, drop the event into a Dead-Letter Queue (DLQ) for manual or asynchronous reconciliation rather than guessing. Edge-to-Cloud Integration and PCI-DSS Scope When deploying this model across retail store terminals and cloud backends, two practical operational considerations stand out: Keeping PCI-DSS Scope Manageable You never want raw Primary Account Numbers (PANs) flowing through multiple cloud services. Use a processor-agnostic vault at the edge to tokenize card details right at the terminal or browser level. The multi-rail routing engine should handle only universal tokens, exchanging them for processor-specific tokens via an isolated proxy right before dispatching the final API call. Offline Resilience for Physical Terminals Store terminals frequently face spotty internet connections. Keep local encrypted transaction logs on the hardware itself. If the store loses WAN connectivity, the terminal queues customer intents locally. When connectivity comes back, an asynchronous sync process drains the queue into the cloud routing layer without holding up customers in line. Production Realities and Tradeoffs Building a multi-rail payment layer adds upfront architectural complexity, especially around financial reconciliation and state management. Settling chargebacks across multiple acquirer dashboards requires tighter accounting controls than a single-gateway integration. However, for high-volume commerce platforms, the tradeoff is clear. When a downstream provider suffers an outage during a peak shopping day, an engine that fails over cleanly — without charging a customer twice — pays for itself immediately.
AI agents are moving beyond simple chat interfaces and becoming part of real application workflows. In a modern distributed system, one agent might handle customer refunds, another might manage inventory, while a background process updates the same data at the same time. The main problem isn't always that an agent makes a bad decision. The real issue is that two agents can make valid decisions using the same data and then overwrite each other's changes without knowing that the data has changed. race condition scenario Agent A reads an order record in 'pending' status and decides to approve it.Agent B reads the exact same record concurrently and decides to cancel it.Agent A commits status = 'approved'.Agent B commits status = 'cancelled'.The database now reflects 'cancelled', completely overwriting Agent A's approval based on stale information. This article shows how to prevent this problem using Optimistic Concurrency Control (optimistic locking) with Python and SQLite. 1. The Concurrent Agent Problem In a typical multi-agent setup, multiple agents work through an application or tool layer: Suppose our database table initially contains a single pending order: idStatusupdated_byversion101PendingSystem1 A naive update pattern executes unversioned queries sequentially: SQL -- Agent A commits approval UPDATE orders SET status = 'approved', updated_by = 'agent-a' WHERE id = 101; -- Agent B commits cancellation moments later UPDATE orders SET status = 'cancelled', updated_by = 'agent-b' WHERE id = 101; Because neither query verifies the record state at the exact moment of execution, Agent B silently destroys Agent A's update. This is the classic lost update anomaly. 2. Why AI Agents Widen the Concurrency Window In a traditional web application, updates usually happen within a short request-response cycle. The time between reading and updating data is often limited to network and database delays. Agentic systems work differently. An agent may take much longer to complete a task: An agent may retrieve data, call an LLM, run tools, and process the results before writing back to the database. This read-to-write cycle can take seconds or even minutes. During that time, another agent or process may change the same record. The database therefore needs to check that the data has not changed since the agent first read it. 3. Mechanics of Optimistic Locking Optimistic locking assumes that conflicts are uncommon. It prevents them by checking a record's version before every update. The database table includes a version number that increases with each successful update: SQL CREATE TABLE orders ( id INTEGER PRIMARY KEY, status TEXT NOT NULL, updated_by TEXT, version INTEGER NOT NULL ); INSERT INTO orders (id, status, updated_by, version) VALUES (101, 'pending', 'system', 1); When an agent reads an order, it also reads its current version, such as version = 1. When the agent updates the order, the application checks that the version is still 1 and increases it to 2 as part of the same update. SQL UPDATE orders SET status = 'approved', updated_by = 'agent-a', version = version + 1 WHERE id = 101 AND version = 1; If the record is still at version 1, the update succeeds, and the version becomes 2. SQL UPDATE orders SET status = 'cancelled', updated_by = 'agent-b', version = version + 1 WHERE id = 101 AND version = 1; -- Evaluates to FALSE! (Current database version is 2) Now, if Agent B tries to update the same record using version 1, the condition fails because the database already has version 2. The update affects zero rows, so the application knows that the data has changed and safely rejects the stale update. 4. Python and SQLite Implementation Here is a simple implementation that shows how to set up the database, read records, and safely update them using version checks. Database Setup and Helper Functions Python import sqlite3 def initialize_database(): with sqlite3.connect("orders.db") as conn: conn.execute(""" CREATE TABLE IF NOT EXISTS orders ( id INTEGER PRIMARY KEY, status TEXT NOT NULL, updated_by TEXT, version INTEGER NOT NULL ) """) conn.execute(""" INSERT OR IGNORE INTO orders (id, status, updated_by, version) VALUES (101, 'pending', 'system', 1) """) conn.commit() def get_order(order_id: int): with sqlite3.connect("orders.db") as conn: cursor = conn.execute(""" SELECT id, status, updated_by, version FROM orders WHERE id = ? """, (order_id,)) return cursor.fetchone() def update_order(order_id: int, status: str, agent: str, expected_version: int): with sqlite3.connect("orders.db") as conn: cursor = conn.execute(""" UPDATE orders SET status = ?, updated_by = ?, version = version + 1 WHERE id = ? AND version = ? """, (status, agent, order_id, expected_version)) conn.commit() if cursor.rowcount == 0: raise RuntimeError( f"Conflict detected on order {order_id}. " f"Expected version {expected_version}, but the record has changed." ) return True Demonstrating a Stale Update Python # Both agents inspect the order concurrently order_agent_a = get_order(101) order_agent_b = get_order(101) print(f"Agent A snapshot: {order_agent_a}") print(f"Agent B snapshot: {order_agent_b}") # Agent A successfully commits update_order( order_id=101, status="approved", agent="agent-a", expected_version=order_agent_a[3] ) print("Agent A update: SUCCESS (Version 1 -> 2)") # Agent B attempts update with stale version try: update_order( order_id=101, status="cancelled", agent="agent-b", expected_version=order_agent_b[3] ) except RuntimeError as err: print(f"Agent B update: FAILED -> {err}") Plain Text Console Output: Agent A snapshot: (101, 'pending', 'system', 1) Agent B snapshot: (101, 'pending', 'system', 1) Agent A update: SUCCESS (Version 1 -> 2) Agent B update: FAILED -> Conflict detected on order 101. Expected version 1, but the record has changed. 5. Conflict Resolution: What to Do Next Detecting a conflict is only the first step. What the application does next is important for keeping the system reliable. A common mistake is to retry the failed operation with the latest version without checking the updated data. Don't blindly retry an agent's decision. For example, an agent may decide to cancel an order because it was pending. If the order is now shipped, retrying the cancellation could create a serious data or business logic problem. 6. Optimistic Locking vs. Idempotency These two patterns solve different problems: Optimistic locking: Prevents stale updates when multiple agents or processes change the same record.Idempotency keys: Prevents the same request from being processed more than once, such as after a timeout or retry. 7. Architectural Separation: Keep Consistency Out of the Model LLMs are good at reasoning, but they are not designed to manage database consistency. System prompts alone cannot guarantee data integrity. Core Principle: Layered Responsibility AI agent: Decides what action to take, such as "Cancel order #101."Application service: Checks permissions and business rules.Database: Ensures data stays consistent using version checks and atomic updates. 8. Testing Concurrent Updates The earlier example shows how a stale update is rejected. We can take it one step further and run two updates at the same time. Python's threading module can help simulate two agents working on the same order: Python import threading def agent_update(agent, status): order = get_order(101) try: update_order( order_id=101, status=status, agent=agent, expected_version=order[3] ) print(f"{agent}: update successful") except RuntimeError as err: print(f"{agent}: update failed -> {err}") thread_a = threading.Thread( target=agent_update, args=("agent-a", "approved") ) thread_b = threading.Thread( target=agent_update, args=("agent-b", "cancelled") ) thread_a.start() thread_b.start() thread_a.join() thread_b.join() Both threads read the order and try to update it. Only the update using the current version can succeed. The other update receives a conflict instead of overwriting the first change. The exact order of the output can vary because the threads run concurrently. For example: Plain Text agent-a: update successful agent-b: update failed -> Conflict detected on order 101. This is the key benefit of optimistic locking: the database decides which update is valid based on the version, rather than relying on which agent happens to write last. 9. Production Checklist When building multi-agent workflows that update operational databases, keep these practices in mind: Use transactions: Use transactions when updating multiple tables so all changes succeed or fail together.Add backoff and jitter: Add random delays between retries to avoid too many agents retrying at the same time.Keep audit logs: Record details such as the agent ID, model version, and data version for each update.Limit retries: Limit automatic retries to 2–3 attempts. If conflicts continue, send the task for human review. Final Thoughts As AI agents become more involved in real-world applications, traditional distributed systems problems become important again. Two agents can make correct decisions independently but still cause problems when they update the same data at the same time. Treating agents as independent clients and using optimistic locking at the database level helps prevent stale updates and keeps data consistent, even when agents take longer to complete their tasks.
Most explanations of HTTP verbs in ASP.NET Core Web API stop at the dictionary definition: GET reads, POST creates, PUT updates, DELETE removes. That's true, but it doesn't explain why real enterprise APIs often bend these rules — and understanding why matters more in practice than reciting the definitions. This article walks through HTTP verb selection using a real HR/Payroll workflow scenario: submitting, updating, and removing leave requests. The Textbook Mapping In a typical Web API controller, the four core verbs map to CRUD operations like this: C# [ApiController] [Route("api/[controller]")] public class LeaveRequestController : ControllerBase { [HttpGet("get-emp-details")] public IActionResult GetEmployeeDetails(int finSlno, string empId, int createdBy) { // fetch and return data return Ok(employeeDetails); } [HttpPost("save-leave-request")] public IActionResult SaveLeaveRequest([FromBody] LeaveRequestDto request) { // create a new record return Ok(result); } [HttpPut("update-leave-request")] public IActionResult UpdateLeaveRequest([FromBody] LeaveRequestDto request) { // update an existing record return Ok(result); } [HttpDelete("delete-leave-request/{id}")] public IActionResult DeleteLeaveRequest(int id) { // remove a record return Ok(result); } } This is correct, and it's what most tutorials teach. But real enterprise systems — particularly HRMS and Payroll applications — frequently deviate from this pattern for reasons that only make sense once you understand what's actually happening to the data underneath. Why "Delete" Often Isn't Really DELETE Consider a leave request that an employee wants to withdraw. The textbook answer says: call [HttpDelete], remove the row from the database, done. In practice, most HR and Payroll systems don't want a "delete" action to genuinely erase the record. There are real, practical reasons for this: Audit trails – organizations often need to prove what happened and when, especially for anything touching attendance, payroll, or approvals. Permanently deleting a leave request destroys that history.Compliance – payroll systems in particular are subject to regulations that may require retaining records for a defined period, regardless of whether the user considers them "deleted."Traceability during disputes – if an employee claims a leave request was withdrawn incorrectly, having the original record (even if marked inactive) is the only way to investigate what actually happened. Because of this, many systems implement what's called a soft delete: instead of removing the row, the operation updates a status field: C# public class LeaveRequest { public int LeaveRequestId; public string EmpID; public int Status; // 0 = Pending, 1 = Approved, 2 = Withdrawn } A "delete" action becomes, in reality, an update to Status: C# [HttpPost("save-delete-role-mapping")] public IActionResult SoftDeleteLeaveRequest([FromBody] List<LeaveRequestDto> requests) { foreach (var req in requests) { req.Status = 2; // mark as withdrawn, don't remove the row } // save changes return Ok(result); } Because this is technically an update operation, not a true deletion, using [HttpPost] (or [HttpPut]) instead of [HttpDelete] is a defensible, common real-world choice — not a mistake or a shortcut. The verb should reflect what actually happens to the data, not just what the button on the screen says. Why POST Sometimes Wins Even for Genuine Deletes There's a second, more practical reason POST shows up in place of DELETE in real systems: payload complexity. [HttpDelete] is traditionally designed around simple identifiers, often passed in the URL: C# [HttpDelete("delete-leave-request/{id}")] public IActionResult Delete(int id) { // remove a single record by id return Ok(); } This works cleanly for deleting one record by one ID. But real operations are frequently batch operations — deleting or withdrawing multiple leave requests at once, each potentially needing additional context (who's performing the action, why, which workflow step it affects): C# [HttpPost("save-delete-role-mapping")] public IActionResult BatchDelete([FromBody] List<LeaveRequestDto> requests) { // process a batch of records, each carrying extra context return Ok(); } Passing a list of complex objects in a DELETE request body is technically possible but goes against how DELETE has traditionally been used and supported across tooling. POST, which has always supported rich request bodies, handles this case more naturally — which is exactly why it's common to see "delete" operations implemented as POST endpoints in real enterprise codebases. How This Looks From the Angular Side The verb choice on the backend directly shapes how the frontend calls it. In an Angular service, each HTTP verb maps to a specific HttpClient method: C# // GET - fetching data, no body needed getEmployeeDetails(empId: string): Observable<EmployeeDto> { return this.http.get<EmployeeDto>(`${this.apiUrl}/get-emp-details`, { params: new HttpParams().set('empId', empId) }); } // POST - creating a new record, or a batch/complex operation saveLeaveRequest(request: LeaveRequestDto): Observable<BaseResultModel<boolean>> { return this.http.post<any>(`${this.apiUrl}/save-leave-request`, request); } // POST used for a soft delete - batch of complex objects saveDeleteWorkFlows(models: LeaveRequestDto[]): Observable<BaseResultModel<boolean>> { return this.http.post<any>(`${this.apiUrl}/save-delete-role-mapping`, models); } Notice that both the create and the soft-delete operations use .post() from the Angular side, mirroring the [HttpPost] decorator on the backend. This consistency matters: if the backend soft-deletes via POST but the Angular service tries to call .delete(), the request will fail or hit the wrong endpoint entirely, since Angular's HttpClient.delete() doesn't send a request body the same way .post() does by default. Keeping verb usage consistent across frontend and backend isn't just a style preference — mismatched verbs are a common, easy-to-miss source of failed API calls that can take a while to diagnose, since the error often looks like a routing problem rather than a verb mismatch. A Note on Idempotency A related concept worth understanding alongside verb selection is idempotency — whether calling the same operation multiple times produces the same result as calling it once. GET, PUT, and DELETE are conventionally idempotent: fetching the same data twice returns the same result; updating a record to the same values twice leaves it in the same final state; deleting an already-deleted record (in systems that handle this gracefully) doesn't cause additional harm. POST is conventionally not idempotent — calling it twice can create two records instead of one. This matters directly for the soft-delete pattern discussed above. If SoftDeleteLeaveRequest is implemented as a POST that simply sets Status = 2, calling it twice by accident (say, from a double-clicked button or a retried failed request) produces the same end state — the record is still just marked as withdrawn. This is a case where a POST endpoint behaves idempotently in practice, even though POST isn't required to be idempotent by convention. It's worth being deliberate about this: an endpoint that changes a status flag is safer to retry than one that increments a counter or appends to a list, and understanding which category an operation falls into helps predict how it will behave under real-world conditions like network retries or double submissions. A Practical Rule of Thumb None of this means HTTP verb conventions don't matter — they're still a useful default. The practical guidance is: Use GET for anything that only reads data and has no side effectsUse POST for creating new records, and also for operations — including some deletes — that involve complex payloads or don't map cleanly to a single identifierUse PUT for straightforward updates to an existing, fully-identified recordUse DELETE for simple, single-identifier removals where a true, permanent delete is actually intended When you encounter an API that doesn't follow the textbook mapping exactly — like a "delete" endpoint implemented as POST — it's worth asking why, rather than assuming it's wrong. Often, as in the soft-delete case above, there's a real business reason: the data isn't actually being removed; it's being marked, and the verb reflects that reality rather than the UI label. Takeaway HTTP verb selection in a real Web API isn't just about matching a CRUD acronym to a decorator. It's about accurately representing what actually happens to the underlying data. A "delete" that's really a status update should probably be a POST or PUT, not a DELETE — and recognizing that distinction is a sign of understanding the system, not a shortcut around convention.
We have documents that need full-text search, but those documents also have meaningful relationships. Do we split search and graph traversal across two systems or keep them together? Most developers reach for Elasticsearch or a dedicated search service when they need full-text search. It's a reasonable reaction, but it introduces an extra service to operate, an extra data pipeline to maintain, and an extra hop in every query. What's easy to miss is that Neo4j ships with Apache Lucene built in — the same underlying search library on which Elasticsearch is built — and when our data are already in a graph, adding full-text search on top is a single Cypher command! More importantly, full-text search in Neo4j can do something a standalone search engine can't: it serves as an entry point into the graph. We can find our initial results with Lucene, then traverse relationships to discover what surrounds them. That two-phase pattern — search-then-traverse — is what this article describes. We'll build a research paper explorer using a generated dataset of 200 papers with titles, abstracts, authors, fields, venues, and citation relationships. The same papers appear in the Weaviate chapter of Seven Vector Databases in Seven Days, where we used hybrid vector search to find them. Here we use Neo4j's full-text search instead, and we add something the Weaviate version didn't have: a citation graph that we can traverse after search. The comparison is instructive — same papers, similar queries, but a very different graph layer. The full source code is available on GitHub. Prerequisites AuraDB is Neo4j's fully managed cloud database. A free tier is available with no credit card required. Sign up at Get Started for Free.Create a new AuraDB Free instance.When the instance is created, download or note the credentials — the connection URI, username, and password.Once the instance is running, open the Query tab, and connect to the instance.Confirm it's empty with MATCH (n) RETURN count(n) which should return 0. The code runs in a Jupyter notebook with Python 3.12 or later. Before starting Jupyter, export the connection details as environment variables in your shell: Shell export NEO4J_URI="neo4j+s://xxxx.databases.neo4j.io" export NEO4J_USERNAME="your-username" export NEO4J_PASSWORD="your-password" One version note. This article queries the full-text index with Cypher's SEARCH clause, which replaces the older db.index.fulltext.queryNodes() procedure. The documentation lists SEARCH on full-text indexes as available from Neo4j 2026.09 and it requires Cypher 25. Our AuraDB Free instance accepted it, so the most reliable check is simply to try the clause, which the notebook does. On instances that don't support it, the procedure does the same job but now produces a deprecation warning. The Graph Model The dataset has three node labels and two relationship types: Plain Text (:Author)-[:AUTHORED]->(:Paper)-[:CITES]->(:Paper) Paper nodes carry the properties we'll search and filter on: title, abstract, field, year, venue and citation_count. Author nodes have a single property: name. The AUTHORED and CITES relationships carry no properties — all the meaning is in the graph structure. A conventional search index doesn't give us Neo4j's native graph model and Cypher traversal over relationships. A paper's citation neighborhood, the other work its authors have published, the papers that are frequently cited alongside it — that structure lives in the relationships, and it's what we'll traverse after search. Generating the Dataset We'll generate 200 papers from pools of fields, venues, author names, methods, datasets, and abstract templates, using a fixed random seed so the results are reproducible. The generation code is adapted directly from the Weaviate chapter, so the papers are the same across both articles. Python random.seed(RANDOM_SEED) papers = [generate_paper(i) for i in range(NUM_PAPERS)] Each paper gets between two and five authors, drawn from pools of first and last names. We add two things the Weaviate version didn't have: author nodes and citation edges. Citation edges are generated with three rules that keep the graph plausible: A paper can only cite papers with a lower paper_id and a publication year no later than its own, so nothing cites the futureCitations are biased toward papers in the same or adjacent fields, using a weighted random selectionEach paper makes between three and eight citation attempts, and duplicates are removed Python weights = [ 3 if c["field"] == paper["field"] else 2 if c["field"] in adjacent else 1 for c in candidates ] This gives us 937 citation edges that cluster by field, which makes the traversal results more interesting. Because the relationships are synthetic, the traversal results demonstrate the mechanics of graph-based discovery rather than real bibliometric relationships. Loading Into Neo4j Before loading, the notebook clears any existing data — dropping constraints and indexes first, then deleting nodes in batches of 1,000 to avoid transaction timeouts on Aura. It then creates uniqueness constraints on Paper.paper_id and Author.name, which keep authors unique and make the lookups in the loading queries fast: Cypher CREATE CONSTRAINT paper_id IF NOT EXISTS FOR (p:Paper) REQUIRE p.paper_id IS UNIQUE; CREATE CONSTRAINT author_name IF NOT EXISTS FOR (a:Author) REQUIRE a.name IS UNIQUE; We'll load the data in three passes using UNWIND to batch the inserts. First, the papers: Cypher UNWIND $records AS r CREATE (p:Paper { paper_id: r.paper_id, title: r.title, year: r.year, field: r.field, venue: r.venue, citation_count: r.citation_count, abstract: r.abstract }) For authors, we use MERGE rather than CREATE, since the same author can appear on multiple papers and we want a single node per author: Cypher UNWIND $records AS r MERGE (a:Author {name: r.author_name}) WITH a, r MATCH (p:Paper {paper_id: r.paper_id}) CREATE (a)-[:AUTHORED]->(p) Finally, the citations: Cypher UNWIND $citations AS c MATCH (from:Paper {paper_id: c.from_id}) MATCH (to:Paper {paper_id: c.to_id}) CREATE (from)-[:CITES]->(to) After loading, the graph has 200 Paper nodes, 215 unique Author nodes, 691 AUTHORED relationships and 937 CITES relationships. One caveat about the authors: names are drawn from 15 first names and 15 last names, so there are only 225 possible combinations. "The same author" here just means "the same generated name". Real bibliographic data needs author disambiguation, which is a hard problem in its own right. Creating the Full-Text Index We'll create a single Lucene index across both the title and abstract properties of Paper nodes: Cypher CREATE FULLTEXT INDEX paper_fulltext IF NOT EXISTS FOR (p:Paper) ON EACH [p.title, p.abstract] OPTIONS { indexConfig: { `fulltext.analyzer`: 'standard' } } A single index across multiple properties means a single query scores both together — no merging of separate result sets in application code. The OPTIONS line chooses the analyzer, the component that splits text into searchable terms. By default Neo4j uses standard-no-stop-words, which indexes every word, including "and", "for," and "the." That makes long natural-language queries noisy, because a paper can pick up a score just by containing "and." The standard analyzer drops common English stop words but doesn't stem, so fuzzy matching, which we'll use shortly, still works on the original terms. The index is created asynchronously, so we'll poll until it's ONLINE before querying: Cypher SHOW INDEXES YIELD name, state WHERE name = 'paper_fulltext' RETURN state Basic Full-Text Search We'll query the index with the SEARCH clause, which returns matching nodes alongside a Lucene BM25 relevance score: Cypher CYPHER 25 MATCH (p) SEARCH p IN ( FULLTEXT INDEX paper_fulltext FOR $search_query LIMIT 5 ) SCORE AS score WITH p, score RETURN p.title, p.field, p.venue, p.year, p.citation_count, score ORDER BY score DESC Two details are worth knowing. First, LIMIT is part of the clause: it sets how many top-scoring matches the index returns. Second, the CYPHER 25 prefix selects the Cypher version that provides SEARCH. We show it once here and omit it from the snippets that follow, although the notebook includes it in every query. The notebook builds this text with a small helper that fills in the limit, so every example below reuses the same clause. The score is based on BM25, which considers term frequency, inverse document frequency, and document length. A paper whose title and abstract both mention "transformer models" will score higher than one where the term appears only once in the abstract. Python search_papers("transformer models for natural language understanding") Example output: Plain Text Efficient Transformer Models for Natural Language Processing Natural Language Processing | Symposium on Knowledge Discovery and Data Mining | 2022 | Citations: 18 Score: 9.926 Efficient Transformer Models for Natural Language Processing Natural Language Processing | Transactions on Machine Learning and Data Mining | 2020 | Citations: 196 Score: 9.741 ... The generated titles aren't unique, so the same title can appear twice. They are different papers, which the year and venue make clear. Special Characters in User Input Lucene treats characters such as + - ! ( ) { } [ ] ^ " ~ * ? : \ / as query syntax. If the query text comes from a user, an unbalanced bracket or a stray colon will raise a parse error or quietly change the meaning of the query. Unless we deliberately want operators like phrases, ~ and boosts to work, we should escape the text first: Python def escape_lucene(text: str) -> str: """Escape Lucene query-syntax characters in raw user input.""" return re.sub(r'([+\-!(){}\[\]^"~*?:\\/&|])', r'\\\1', text) search_papers("stream processing (survey") # raises a parse error search_papers(escape_lucene("stream processing (survey")) # works The unescaped query fails with a message that Neo4j could not parse the full-text search string. The escaped one returns the stream processing papers. Boosting Titles Over Abstracts Because the index covers both properties, we can address them by name in the query and weight them. Here a match in the title counts three times as much as a match in the abstract: Python search_papers("title:transformer^3 OR abstract:transformer", top_k=5) The Index Stays in Sync One of the costs of a separate search service is the pipeline that keeps it in sync with the primary database. Here the full-text index is updated as part of the same transaction that writes the node. To see it, we create a paper containing a unique word, search for it straight away, and then delete it: Python with driver.session() as session: session.run(""" CREATE (:Paper {paper_id: 9999, title: 'Quokka Navigation for Robotics', abstract: 'A throwaway paper used to show the index updates on commit.', year: 2024, field: 'Robotics', venue: 'Demo', citation_count: 0}) """) search_papers("quokka", top_k=1) # found immediately with driver.session() as session: session.run("MATCH (p:Paper {paper_id: 9999}) DETACH DELETE p") search_papers("quokka", top_k=1) # gone again The first search returns the new paper (score 2.815) and the second returns nothing. Phrase Search Wrapping a query in double quotes tells Lucene to treat it as a phrase — the words must appear together in that order: Python search_papers('"graph neural networks"', top_k=3) Without quotes, Lucene scores documents that contain any of the words, awarding higher scores for more matches. With quotes, every result must contain the exact phrase. Score Comparison: Keyword vs. Phrase Search The chart below (Figure 1) runs the same query twice, once as keywords and once as a phrase, and plots the scores of the top eight results of each. A bar of zero means the paper is outside that mode's top eight, not that it fails to match. Figure 1. Keyword vs. Phrase Search. The result is worth reading carefully. In this dataset, every paper in the Graph Neural Networks field contains the exact phrase, because the field name appears in its title and abstract. So both queries match essentially the same papers and their scores are close: 6.1 to 6.8 for keywords and 5.4 to 5.65 for the phrase. What changes is which papers make the top eight. Only three of the eight appear in both lists, and the top result differs: keyword search ranks "Efficient Graph Attention Networks for Graph Neural Networks" first (6.774), while phrase search ranks "Efficient Link Prediction for Graph Neural Networks" first (5.649). A plausible reason is that keyword scoring rewards papers that repeat the individual words, such as "graph" and "networks" in a title that also mentions graph attention networks, while phrase scoring only counts the phrase itself. Raw scores aren't directly comparable across the two query forms, so membership and order are the things to compare. On noisier text, where the words in a query don't usually sit together, the phrase search would also drop papers entirely, which is the more familiar reason to use it. Fuzzy Search Appending ~ to a term enables Lucene's fuzzy matching, which finds terms within a given edit distance. A misspelled query like tranformer~ correctly matches transformer: Python search_papers("tranformer~", top_k=3) Example output: Plain Text Efficient Transformer Models for Natural Language Processing Natural Language Processing | Transactions on Machine Learning and Data Mining | 2020 | Citations: 196 Score: 3.015 ... Without the ~, the misspelled query returns nothing. With it, Lucene finds the closest matching terms and scores them. This is useful for any field where exact spelling can't be guaranteed, such as technical terms or partially remembered titles. Filtered Search We can combine the full-text index with filters on structured node properties. There is one thing to be aware of: for full-text indexes, SEARCH doesn't allow a WHERE inside the clause. Filtering is a second step. The index first returns its top-scoring candidates, then a WHERE after it filters them by field, year or citation count: Cypher MATCH (p) SEARCH p IN ( FULLTEXT INDEX paper_fulltext FOR $search_query LIMIT 200 ) SCORE AS score WITH p, score WHERE p.field = $field AND p.year >= $min_year AND p.citation_count >= $min_citations RETURN p.title, p.field, p.venue, p.year, p.citation_count, score ORDER BY score DESC LIMIT $top_k The two limits do different jobs. The LIMIT 200 inside SEARCH sets the size of the candidate pool, here every paper in the collection, and the final LIMIT $top_k trims the filtered results. If we asked the index for only the top five and filtered afterward, a selective filter could leave us with nothing. On a large collection, we would choose a pool big enough for our filters to leave enough results, without asking the index to score everything. These are the same three filtered queries used in the Weaviate chapter. Weaviate ran them as hybrid searches, combining vectors and keywords, whereas this is keyword-only BM25, so we should expect overlap in the results rather than identical rankings: Python # NLP papers from 2020 onwards with at least 100 citations search_papers_filtered( "attention mechanisms and transformer architectures", field = "Natural Language Processing", min_year = 2020, min_citations = 100, ) # Recent computer vision papers search_papers_filtered( "object detection in real time", field = "Computer Vision", min_year = 2022, ) # Highly cited papers across all fields search_papers_filtered( "self-supervised learning for representation learning", min_citations = 200, ) The first query returns two papers (scores 3.350 and 3.154) and the second returns one (7.250). The third returns five: the three self-supervised learning papers score between 10.273 and 9.537, then the scores drop to 3.543 for the fourth paper, which matches only more generic terms such as "learning". With the default analyzer, an earlier run of the first query also returned papers scoring below 0.1, apparently matching only on common words such as "and". With stop words removed, that tail no longer appears. Search Then Traverse: The Graph Layer Full-text search answers "Which documents match?" Graph traversal answers "What is connected to those matches?" This section is where the two work together. We'll use the Lucene results as an entry point into the graph, then traverse CITES and AUTHORED relationships to discover related content. The pattern is always the same: SEARCH finds the initial set, then Cypher traverses from there. In a synthetic graph, some papers have few connections, so the notebook runs ten candidate queries and measures each top result's neighborhood: how many papers it cites, how many cite it, and how strongly it is co-cited with others. It picks the richest as the demonstration query. That query is "protein structure prediction", whose top result, "Efficient Protein Structure Prediction for Bioinformatics" (score 12.926), cites 3 papers and is cited by 17. What Does a Top Result Cite? Starting from the highest-scoring paper, we'll follow outgoing CITES edges to see what it builds on. Note that citation_count is a property generated independently for each paper — it isn't derived from the CITES relationships in this graph. We use it here as a proxy for importance when ordering results. Cypher MATCH (p) SEARCH p IN ( FULLTEXT INDEX paper_fulltext FOR $search_query LIMIT 1 ) SCORE AS score WITH p, score MATCH (p)-[:CITES]->(cited:Paper) RETURN p.title AS source_title, score AS source_score, cited.title, cited.field, cited.year, cited.citation_count ORDER BY cited.citation_count DESC LIMIT $top_k Example output: Plain Text Drug Discovery for Bioinformatics: A Scalable Approach Bioinformatics | 2016 | Citations: 483 Improving Bioinformatics with Drug Discovery Bioinformatics | 2016 | Citations: 467 Cryptographic Protocols in Cybersecurity: Challenges and Opportunities Cybersecurity | 2016 | Citations: 283 What Cites a Top Result? Traversing in the opposite direction — incoming CITES edges — finds papers that build on a highly relevant result: Cypher MATCH (p) SEARCH p IN ( FULLTEXT INDEX paper_fulltext FOR $search_query LIMIT 1 ) SCORE AS score WITH p, score MATCH (citing:Paper)-[:CITES]->(p) RETURN p.title AS source_title, score AS source_score, citing.title, citing.field, citing.year, citing.citation_count ORDER BY citing.year DESC LIMIT $top_k The most recent citing papers include "A Unified Framework for Reinforcement Learning using Hierarchical Rl" (2024), "Efficient Malware Classification for Cybersecurity" (2024), and "Meta-Learning for Machine Learning: A Scalable Approach" (2024). Citations reach across fields because the generator only biases them toward related fields; it doesn't confine them. Visualizing the Citation Network We can visualize the citation neighborhood of the top result as an interactive graph with neo4j-viz. In Figure 2, the central red node is the top-scoring paper, blue nodes are papers it cites, and green nodes are papers that cite it. Arrows point from the citing paper to the cited paper, and node size reflects citation count. In the notebook, hovering over a node shows the full title, year, and citation count. Python # One Cypher query returns the top result with the papers it cites and the papers that cite it nodes, relationships = {}, [] nodes[top["id"]] = Node(id=top["id"], caption=short(top["title"]), properties={"role": "Top result", "citations": top["citations"]}) for p in top["cited"]: nodes[p["id"]] = Node(id=p["id"], caption=short(p["title"]), properties={"role": "Cites", "citations": p["citations"]}) relationships.append(Relationship(source=top["id"], target=p["id"])) for p in top["citing"]: nodes[p["id"]] = Node(id=p["id"], caption=short(p["title"]), properties={"role": "Cited by", "citations": p["citations"]}) relationships.append(Relationship(source=p["id"], target=top["id"])) VG = VisualizationGraph(nodes=list(nodes.values()), relationships=relationships) VG.color_nodes(property="role", colors={ "Top result": "#EF553B", "Cites": "#636EFA", "Cited by": "#00CC96"}) VG.resize_nodes(property="citations", node_radius_min_max=(14, 40)) VG.render(height="520px") Figure 2. Citation Network. This kind of neighborhood visualization isn't something we'd get from a search engine, as it requires the graph. Find Other Work by the Same Authors From the top result, we'll traverse AUTHORED edges to the authors, then follow their other AUTHORED edges to find their other papers: Cypher MATCH (p) SEARCH p IN ( FULLTEXT INDEX paper_fulltext FOR $search_query LIMIT 1 ) SCORE AS score WITH p, score MATCH (a:Author)-[:AUTHORED]->(p) MATCH (a)-[:AUTHORED]->(other:Paper) WHERE other <> p RETURN p.title AS source_title, score AS source_score, a.name AS author, other.title, other.field, other.year, other.citation_count ORDER BY other.citation_count DESC LIMIT $top_k The highest-cited results are papers by Chen Singh and Lena Li, two of the authors of the top result, including "Improving Computer Vision with Generative Adversarial Networks" (475 citations) and "Efficient Multi-Agent Systems for Reinforcement Learning" (433). This is the kind of discovery path that's natural in a graph but would typically require additional application-side queries or data modeling outside the search index. Co-Citation Discovery Two papers are co-cited when a third paper cites both of them. Frequently co-cited papers can indicate related research topics, shared foundations, or intellectual proximity — a relationship that doesn't appear in the papers themselves, only in how others use them: Cypher MATCH (p) SEARCH p IN ( FULLTEXT INDEX paper_fulltext FOR $search_query LIMIT 1 ) SCORE AS score WITH p, score MATCH (citing:Paper)-[:CITES]->(p) MATCH (citing)-[:CITES]->(cocited:Paper) WHERE cocited <> p WITH p, score, cocited, count(citing) AS shared_citations RETURN p.title AS source_title, score AS source_score, cocited.title, cocited.field, cocited.year, shared_citations ORDER BY shared_citations DESC LIMIT $top_k For our top result, the most co-cited paper is "Drug Discovery for Bioinformatics: A Scalable Approach" with 6 shared citing papers, followed by "Rethinking Robotics with Imitation Learning" with 5, and three papers with 4 each. The Robotics paper is a reminder that the generator's field bias is only a bias. Co-citation analysis is a classical technique in bibliometrics, and here it is a single Cypher query. Traversing From More Than One Result So far we've started from the single best hit. The same pattern works from the top N results, and aggregation then surfaces something no single result shows: the papers that several of the best matches have in common. Here we ask which papers are cited by the most of the top five results, a rough proxy for foundational work on the topic: Cypher MATCH (p) SEARCH p IN ( FULLTEXT INDEX paper_fulltext FOR $search_query LIMIT 5 ) SCORE AS score WITH p, score MATCH (p)-[:CITES]->(ref:Paper) RETURN ref.title AS title, ref.field AS field, ref.year AS year, ref.citation_count AS citations, count(DISTINCT p) AS cited_by_hits ORDER BY cited_by_hits DESC, citations DESC LIMIT $top_k Two papers, "Drug Discovery for Bioinformatics: A Scalable Approach" and "Improving Bioinformatics with Drug Discovery," are each cited by two of the five best matches, ahead of papers cited by only one. A standalone search engine would return the five matches and stop there. When to Use This Neo4j's full-text search is worth reaching for when our data are already in a graph, and we want to add keyword search without operating a separate service. The setup is minimal: one Cypher command creates the index, one clause queries it, and we immediately get phrase matching, fuzzy matching, field boosting, and BM25 relevance scoring. Because the index is updated in the same transaction as the data, there is no synchronization pipeline to build or monitor. The search-then-traverse pattern is the real differentiator. If our data have relationships, such as citations, authorship, product categories, or social connections, and we want search to serve as an entry point into those relationships, Neo4j gives us both in one place. It is not the right choice for every workload. A dedicated search engine offers capabilities that weren't part of what we used here, such as faceted aggregations and result highlighting, and it lets us scale search independently of the database. If search traffic is large, if it needs different hardware from the graph workload, or if the data have no meaningful relationships, a separate service may be the better fit. The SEARCH behavior described above also matters at scale: the index returns a bounded candidate set, and filters apply afterward, so queries with very selective filters need a candidate pool sized for them. For purely semantic workloads where we want vector similarity rather than keyword matching, Neo4j also supports vector indexes, which we can combine with full-text search in the same query. But for the common case where keyword search and graph traversal belong together, the built-in Lucene integration is often all we need. Summary We've seen how to create a multi-property full-text index in Neo4j, query it with the SEARCH clause using keyword, phrase, and fuzzy search, escape user input and boost fields, combine the results with structured property filters, and use them as the entry point for graph traversals that follow citations, authorship, co-citation, and multiple matches at once. Further examples of Neo4j's multi-model capabilities can be found in the free online book Neo4j Beyond the Graph. The full source code is available on GitHub.
The Policy That Started the Argument We were looking at a purple policy between ListingSold and StopListingFee. Read aloud, the flow expressed a simple business expectation: when a listing is sold, the applicable recurring listing fee should stop. The interesting question was where that policy belonged. If Billing owns it, Billing receives the fact that a listing was sold and determines what that means for its fee model. Listing reports the sale without naming the operation Billing should perform. If Listing owns the coordination, it requests that Billing stop the fee, which means Listing knows that capability exists. A third option is a separate process manager that receives the sale event and coordinates the response. At the level of a simple event-policy-command sequence, these versions can look almost identical. Once ownership is made explicit, the differences matter. Billing depends on the sale event’s contract in the first version. The coordinating component depends on Billing’s command contract in the others. Billing still validates changes to its own fee model in every version. Responsibility for detecting an incomplete outcome, such as a fee that continues accruing after a sale, also needs an explicit owner. We were still discussing the business rule, but we were now deciding how software responsibilities and contracts would support it. This was already a discussion about where coupling belongs and how it is managed. We had moved into architectural reasoning without changing the business outcome we wanted. I later discussed this on LinkedIn and encountered the objection that EventStorming is not software design at all. I disagree, but the objection helped me distinguish two claims. EventStorming can host software design. It does not follow that the arrangement of a board is sufficient justification for the boundaries of the resulting system. This article is about that gap. EventStorming Already Includes Design The official EventStorming material explicitly recognizes Big Picture, Process Modeling, and Software Design formats. The patterns collection also addresses collaborative design activities. EventStorming is not limited to collecting domain events on a long wall of paper. The title therefore makes a narrower claim than it might suggest. “Your Event Storming” means the model your group produced, with its particular clusters, policies, and arrows. That model may contain exploratory ideas, agreed decisions, and reasons for those decisions. What cannot be assumed is that the notation alone captures all the reasoning needed to justify the resulting software structure. A policy between two notes expresses a relationship: when this happens, that response should follow. Unless ownership and the relevant assumptions are also made explicit, the sequence leaves important questions unanswered. Who owns the rule? Which contract does the response depend on? What happens if the response is rejected or never completes? A Software Design session can address those questions. It can expose invariants, clarify responsibilities, and help participants explore alternative models. The policy discussion above is an example of that work. The problem is automatic inference: treating a cluster as sufficient reason for a service, a purple note as a prescribed message handler, or a temporal sequence as an execution topology. This is related to the architectural decisions a domain model leaves open. Modeling can establish constraints and support architectural choices without uniquely determining how the system should be deployed or operated. Those choices can be discussed during the workshop. They still need reasons beyond the appearance of the board. Same Flow, Different Ownership For this example, Listing owns the listing lifecycle, and Billing owns the fee lifecycle. A sold listing should no longer accrue the applicable recurring fee. The precise rule, especially when stopping accrual becomes effective, still needs clarification. For now, consider three ways to allocate responsibility for the response. Alternative A: Billing owns the reaction. Listing records the sale and makes a ListingSold event available across an agreed boundary. Billing receives that fact, interprets it using its own rules, and invokes the appropriate fee behavior. Listing does not need to name StopListingFee. Billing now depends on the meaning and structure of the event it consumes. The event publisher needs an explicit compatibility policy, although that does not require exposing Listing’s internal domain event unchanged. Billing keeps the relationship between sale facts and fee behavior within its own model. That is useful when the rule belongs to Billing and evolves with its charging policies. The design still needs to account for delivery failures, processing failures, and missed reactions. Suppose Listing records a sale but the corresponding fee remains active. Who detects that discrepancy, and who resolves it? Billing may own both the reaction and reconciliation, but that responsibility must be explicit. Successful publication alone does not establish that the business outcome is complete. Alternative B: Listing owns coordination. Listing requests StopListingFee from Billing. Listing therefore knows a capability Billing exposes. Billing remains responsible for validating the request and protecting its own rules. The request could be synchronous or queued. Either way, the coordinating component needs to distinguish acceptance from completion and decide how to handle rejection, an unknown outcome, or a response that does not arrive within the expected time. This option is justified only if Listing’s responsibility genuinely includes coordinating the fee change. Emitting the original event is not enough to establish that ownership. Nor does the statement “the sale is complete only when the fee stops” settle the question by itself. That requirement establishes a business outcome; deciding which component should be accountable for achieving it is still design work. Listing-owned coordination becomes more plausible when Listing already owns the broader completion process and can manage it without absorbing Billing’s fee rules. If it starts calculating charges or reproducing Billing’s eligibility logic, the coordination boundary is leaking. Alternative C: A process manager owns coordination. A separate component tracks the cross-component outcome and requests work from the responsible participants. Listing still owns the listing lifecycle. Billing still owns fee behavior. The process manager owns the progress of the interaction. This helps when a sale triggers several participants, steps have deadlines, or unresolved cases need escalation. The manager can record that the sale was observed, that a fee-stop request was issued, and whether completion was confirmed. That visibility has a cost. The manager needs durable state, recovery behavior, and a policy for its own failures. Its commands may use the same capabilities Billing already exposes; participants do not necessarily need a new interface simply because a process manager is calling them. For a single reaction, introducing this machinery may be harder to justify than making Billing’s existing responsibility explicit. DesignMessage crossing the boundaryContract knowledgeAccountability for completionBilling reactsListingSold eventBilling understands the published sale contractBilling can own reaction and reconciliation; this must be agreedListing coordinatesStopListingFee command and an appropriate completion signalListing understands Billing’s exposed capabilityListing tracks the outcome if coordination belongs within its responsibilityProcess manager coordinatesSale event, fee-stop command, and completion or failure signalsThe manager understands the contracts required by the processThe manager tracks progress and escalation; its own recovery must also be designed These alternatives allocate responsibility differently. They do not divide neatly into a coupled design and a decoupled one. Contract knowledge is one form of dependency. Billing understands the sale event in Alternative A. Listing understands Billing’s command contract in Alternative B. The process manager understands the contracts needed to coordinate Alternative C. Runtime availability is a separate concern. A synchronous call may require the responder to be available immediately. Buffered communication can allow the sender to proceed while the receiver is unavailable, provided the system can retain and eventually process the pending work. That changes the timing of the dependency rather than removing it. Release coordination is another concern. Consumers and publishers need compatible contracts, but they do not necessarily need simultaneous deployments. The relevant question is how those contracts can evolve without silently changing the business meaning. Moving the policy changes some of these dependencies. It does not automatically reverse all of them. Nor does its position prescribe a transport: an event does not require Kafka, and a command does not require HTTP. What would favor one option? If Billing independently owns the rule connecting a sale to fee accrual, Alternative A is a strong candidate. If Listing already owns a broader completion process that legitimately includes requesting the fee change, Alternative B may fit. If the interaction needs its own lifecycle, deadlines, and recovery across several participants, Alternative C becomes easier to justify. The business flow is an input to that decision. Ownership, contract obligations, and failure handling determine whether the chosen design can support it. The Missing Question Is Often About Time The sequence ListingSold → StopListingFee leaves an important question unresolved: when should accrual stop? The policy note alone does not specify that. It could be the effective sale time, when the sale is recognized by the business. It could be when Listing records the change, or when Billing processes it. Those moments can differ. If the contract carries timestamps, their meanings need to be explicit. For example, a published event might distinguish the effective time from the time the sale was recorded: JSON { "type": "ListingSold", "eventId": "...", "listingId": "...", "effectiveAt": "...", "recordedAt": "..." } These fields make the distinction visible. They do not establish which timestamp Billing should trust or what it should do with it. If Billing calculates accrual using an authoritative effective time, delayed delivery need not change the amount ultimately owed. But the system may already have issued an invoice, collected payment, or shown an outdated balance. It still needs rules for adjustment and for what the customer sees before the correction completes. A timestamp helps preserve the meaning of the fact. It does not remove the consequences of learning about it late. Suppose the business accepts a delay before the balance reflects a sale, provided the final calculation uses the correct effective date. An asynchronous reaction may satisfy that requirement. Suppose instead that no fee-related charge may be issued after the sale is accepted. That requires a stronger coordination or validation mechanism than receiving a sale event later. Merely replacing an event with a queued command does not provide that guarantee either. Corrections expose further decisions. If the sale was recorded incorrectly, should accrual resume? From which date? If an older sale notification arrives after a correction, how does Billing recognize that it no longer represents the applicable state? These questions concern the business meaning of the update as much as its delivery. This is where business outcomes beyond aggregate boundaries become relevant. A process spanning Listing and Billing does not automatically require one aggregate or one distributed transaction. Billing can protect a local invariant, such as refusing to calculate accrual beyond its recorded stop date, while the broader process still fails to establish that date correctly. The cross-component outcome needs explicit completion and recovery semantics. Compensation is another business operation, with its own rules and potential failures, rather than a database rollback. Eventual consistency does not by itself establish that the relevant rule was interpreted correctly or that unresolved work will be detected and repaired. Durable publication, retries, and idempotent handling support the design. They cannot supply a business rule the participants have not agreed on. A Boundary Still Needs a Reason An event on the board is not automatically an integration event. ListingSold may initially describe a fact inside the Listing model. Exposing that fact to another component creates a contract that needs deliberate semantics and an evolution policy. The published representation may differ from the internal event. Likewise, a cluster of activities does not establish an aggregate merely because the notes appear together. The grouping must be examined against the rules that must remain consistent. An aggregate does not prescribe a microservice, and a bounded context does not prescribe an independent deployment. These distinctions are part of why Domain-Driven Design does not select an architectural style. The ownership alternatives also exist inside a monolith. Listing and Billing could be modules in one application, with Billing reacting to an in-process event or receiving an explicit request through a module interface. The questions about rule ownership and contract knowledge remain. The execution guarantees may be quite different, however. A synchronous handler participating in the same database transaction can fail together with the originating operation. A background handler in the same application may run after that transaction commits and require durable delivery and recovery. Sharing a deployment does not settle those choices, just as separating deployments does not settle ownership. An independent deployment therefore needs its own justification. Billing might have a materially different scaling profile, need isolation from listing traffic, or require a separate security boundary. Its team might need genuine release autonomy. A runtime constraint might be difficult to satisfy within the existing application. These are reasons to evaluate separation, rather than automatic instructions to introduce it. The costs are concrete. An interaction that was previously local may now time out or complete without the caller receiving confirmation. Contracts need to evolve while different versions coexist. Observability must follow a business outcome across processes. Data previously updated within one transaction may require a different consistency strategy. Independent deployment can reduce some coordination costs while introducing others. I would not impose a fixed sequence of aggregates first, contexts second, services last. Domain learning and architectural constraints inform each other. A scaling or compliance requirement can change where responsibilities execute or force us to reconsider a proposed partition. It does not automatically change the meaning of the business model. When a boundary changes, the team should be able to explain which concern required the change. What I Would Record Beside the Wall I would capture a short decision record while discussing each significant policy, rather than leave all the reasoning for someone to reconstruct after the workshop. First, who owns the rule connecting the fact to the response? In this example, does Billing own the rule relating a sale to fee accrual, or does a broader business process require that outcome? Billing owns the fee mutation in either case. The separate question is who initiates the response and tracks its completion. Next, which contract crosses the boundary? Who maintains it, and what changes can its consumers tolerate? A note on the wall becomes an integration commitment only when the participants agree on the meaning and obligations it carries. The record should also identify what must remain consistent, the acceptable delay between steps, and the effective time used when information arrives late. It should say who detects an incomplete outcome, who resolves it, and what counts as evidence of completion. Acceptance scenarios make those decisions harder to leave vague. The official Extract Acceptance Tests pattern connects design sessions with more precise expectations about behavior. For this example, I would include a sale received after an invoice was issued, a correction received before an older sale event, and a fee-stop request whose completion cannot immediately be confirmed. The expected result in each case needs to come from the business rule. The record should remain short enough to maintain. Its purpose is to preserve the reasons behind the model, including decisions deliberately left open. Six months later, another engineer should be able to distinguish an agreed constraint from a convenient assumption. Closing The discussion began with ListingSold, a purple policy, and StopListingFee. Asking who owned the policy led us to contracts, coordination, consistency, and recovery. That was software design taking place through EventStorming. The workshop can help a team make those decisions, and the board can record them. What the event sequence cannot provide on its own is the justification for a particular allocation of responsibilities or a particular deployment boundary. Billing might own the reaction. Listing might coordinate it. A process manager might track a broader outcome. Choosing among them requires knowing the rule, the timing requirements, and who is accountable when the expected response does not happen. The wall can show the flow. Your design must explain who owns each decision and what happens when the flow breaks. References and Further Reading Introducing EventStormingEventstorming PatternsExtract Acceptance TestsWhere Does Software Architect Fit in Domain-Driven Design?Modern Software Architecture (Part 2): Coupling Is Where Architecture Actually HappensDomain-Driven Design Beyond Aggregate BoundariesDomain-Driven Design Is Not an Architecture
Executive Summary Offline-first mobile systems treat local storage as an active source of application state rather than a disposable cache. That design improves responsiveness and preserves core behavior during long network outages, but reconnection creates a distributed-systems problem as local mutations may now compete with server updates or changes from other devices. Android's official offline-first guidance recommends a local data source for repositories that use the network and describes queued synchronization with persistent work and retry behavior. SQLite write-ahead logging can provide efficient local transactional durability, but WAL itself does not resolve cross-device conflicts. A reliable synchronization design therefore requires explicit versioning, deterministic merge rules, idempotent delivery, and observable reconciliation rather than a simple "upload the latest row" strategy. Reconnection Is a Versioning Problem Consider a record edited locally while the device remains offline for 12 hours. During that interval, the server may accept another update to the same record. Both versions can be valid descendants of an earlier state, so network recovery does not establish which value is semantically correct. The central question is not which copy arrived last, but whether one update causally follows the other or whether both happened concurrently. A local mutation log makes that distinction manageable. Each mutation can carry an operation identifier, entity identifier, base version, logical metadata, and payload. The local state change and the queued operation should be committed atomically so a crash cannot preserve one without the other. A compact operation can resemble: Plain Text op = { id: "op-8f31", entityId: "task-42", baseVersion: 17, type: "SET_STATUS", value: "DONE", occurredAt: 1792449120000 } On synchronization, the server checks the base version before applying the operation. A mismatch indicates that another accepted change advanced the entity and that normal application should stop until a merge rule runs. Plain Text apply(op, current) { if (op.baseVersion != current.version) return conflict(op, current) current.status = op.value current.version++ return current } This resembles optimistic concurrency control, but the important mobile property is durability across process death, retries, and extended disconnection. Android guidance explicitly describes queued writes for offline-first applications, while SQLite WAL records committed transactions in an append-oriented log and permits readers to continue while writes are committed. WAL improves local persistence characteristics where the mutation log supplies synchronization semantics. Conflict Resolution Must Match Data Semantics Last-writer-wins is attractive because it is cheap and deterministic. Cloud Firestore documents use last-write-wins behavior for multiple offline changes to the same document. A comparable custom merge can select the value with the greater timestamp: Plain Text merge(local, remote) { if (local.updatedAt != remote.updatedAt) return local.updatedAt > remote.updatedAt ? local : remote return local.replicaId > remote.replicaId ? local : remote } The simplicity hides a correctness risk. Device clocks can drift, timestamps can tie, and a later timestamp does not necessarily represent a more important business action. LWW works best for fields where overwriting is acceptable, such as a display preference, and poorly for additive or irreversible business events. A production rule also needs a deterministic tie-breaker, such as a stable replica identifier, so every participant reaches the same result. Vector clocks solve a different problem by representing causality rather than physical time. Amazon's Dynamo paper associates versions with node-counter pairs and uses clock comparison to determine whether one version descends from another or whether branches are concurrent. A simplified comparison can flag concurrency when neither clock dominates: Plain Text dominates(a, b) { return everyKey(k -> a[k] >= b[k]) && anyKey(k -> a[k] > b[k]) } conflicts(a, b) { return !dominates(a.clock, b.clock) && !dominates(b.clock, a.clock) } This preserves information that LWW discards, but metadata grows with participating replicas unless compaction or bounded identity schemes are introduced. Dynamo explicitly treats concurrent branches as versions requiring reconciliation, illustrating an important principle stating causality detection and semantic merging are separate responsibilities. CRDTs move more reconciliation into the data type. Shapiro and coauthors formalized state-based and operation-based CRDTs that converge when their required algebraic and delivery conditions hold. A grow-only set is the simplest example because additions commute and state merge is set union: Plain Text applyAdd(state, op) { return union(state, setOf(op.value)) } mergeGrowOnlySet(a, b) { return union(a, b) } No accepted addition is lost, and merge is associative, commutative, and idempotent. Operation-based CRDTs can transmit smaller mutations instead of full state, but they require stronger delivery properties. Automerge demonstrates the broader local-first model by recording changes and merging concurrent edits without a central coordinator. CRDTs are powerful for collaborative or additive data, but metadata, tombstones, history retention, and domain constraints can increase storage and implementation cost. Server-authoritative reconciliation remains appropriate when the backend owns invariants that cannot be decided safely on a disconnected client. Inventory allocation, authorization state, settlement, and policy enforcement commonly require server validation. In that model, offline mutations express intent, while reconnection submits intent against the current authoritative state: Plain Text reconcile(op, current) { if (!policy.allows(op, current)) return rejected(current) return applyValidated(op, current) } The server may accept, transform, reject, or return a conflict representation that preserves enough context for deterministic recovery. Couchbase Lite exposes conflict-resolution mechanisms for replicated documents, reinforcing that replicated state needs an explicit policy rather than an accidental overwrite. Delivery Must Be Idempotent and Observable Retries are inevitable after ambiguous failures. A request may reach the server even when the response never reaches the device, so replaying the same mutation must not duplicate its effect. Stripe's API documentation describes idempotency keys specifically for safely retrying requests. A client-generated operation identifier can become that key: Plain Text op.idempotencyKey = uuid() save(op) sync(op) { existing = dedup.find(op.idempotencyKey) if (existing != null) return existing.response result = apply(op) dedup.save(op.idempotencyKey, result) return result } The deduplication record and business mutation should participate in the same transactional boundary where possible. On server systems that also publish downstream events, a transactional outbox can persist the state change and event record together, where Debezium documents the outbox pattern as a way to avoid inconsistencies between stored state and emitted events. Correctness still competes with product and operational costs. Automatic LWW minimizes interaction and metadata but may silently discard meaningful work. Vector clocks preserve concurrency information but add version metadata and reconciliation paths. CRDTs can improve convergence and offline collaboration but may increase stored history, bandwidth, and implementation complexity. Server-authoritative reconciliation protects invariants but can surface rejected offline work after a long delay, creating a difficult user experience. No single policy fits every field, so conflict behavior should be defined at the domain level rather than globally. Production controls should make synchronization behavior inspectable. Deterministic merge functions should produce the same output for the same inputs. Reconciliation endpoints should return authoritative state, accepted operation identifiers, and explicit conflict metadata. Unresolved conflicts should be observable through metrics and diagnostics rather than disappearing into retry loops. Automated tests should exercise duplicated operations, reordered delivery, clock skew, stale base versions, concurrent edits, deletion-versus-update races, process restarts, and repeated reconnects. Background synchronization should also use bounded retries and connectivity constraints where Android WorkManager supports network constraints and retry with backoff for persistent background work. Conclusion An application returning after 12 hours offline does not merely need to "sync the newest data." It needs to establish causality, preserve durable intent, prevent duplicate effects, and resolve concurrent state according to domain semantics. LWW is useful when overwrite is acceptable, vector clocks expose concurrency, CRDTs encode convergence into suitable data structures, and server-authoritative reconciliation protects centralized invariants. The strongest offline-first systems combine those techniques selectively, persist operations transactionally, attach stable idempotency keys, expose reconciliation outcomes, and test failure ordering as aggressively as ordinary business logic. Network recovery should trigger a deterministic protocol, not a race between copies. That distinction turns offline synchronization from a fragile retry mechanism into a controlled consistency model.
A transaction reads one row and then remains open. Later, a deployment adds a column using ALTER TABLE ... ALGORITHM=INSTANT. The migration waits. A new request issues an ordinary SELECT against the same table, and that request waits too. The original query has already finished. The schema change requires no table rebuild. The new query accesses a different row. Yet all three sessions participate in the same blocking sequence. The explanation sits above InnoDB’s row-lock layer. The open transaction retains a metadata lock, the schema change requests an incompatible lock, and that pending request can make later queries queue behind it. Instant DDL reduces the work required to change a table. It does not eliminate the coordination required to begin or complete that change. Reproduce the Queue With Three Connections Use a disposable database and three independent MySQL connections. A tiny table keeps data volume out of the explanation: MySQL CREATE DATABASE mdl_demo; CREATE TABLE mdl_demo.orders ( id BIGINT PRIMARY KEY, status VARCHAR(20) NOT NULL ) ENGINE=InnoDB; INSERT INTO mdl_demo.orders VALUES (1, 'NEW'), (2, 'NEW'), (3, 'DONE'); In Session 1, start a transaction and read one row: MySQL START TRANSACTION; SELECT * FROM mdl_demo.orders WHERE id = 1; -- Leave the transaction open. The query returns immediately. Do not commit yet. In Session 2, request the schema change: MySQL ALTER TABLE mdl_demo.orders ADD COLUMN source VARCHAR(32) NULL, ALGORITHM=INSTANT; After the ALTER begins waiting for a metadata lock, issue another read in Session 3: MySQL SELECT * FROM mdl_demo.orders WHERE id = 2; This later read can also wait, despite accessing a different row. Committing Session 1 allows the migration to proceed, followed by the queued read. MySQL’s verified Bug #106480 documents this three-connection behavior for MySQL 8.0. Test the sequence against your deployed version rather than assuming every DDL operation has identical locking details. Separate Execution Cost From Lock Admission For supported operations, ALGORITHM=INSTANT changes metadata without rebuilding the table. Whether a particular alteration qualifies depends on the operation, table characteristics, and server version. MySQL publishes these restrictions in its online DDL operation matrix. That optimization removes potentially expensive data movement. It does not promise a maximum elapsed time. The migration’s duration includes time spent acquiring required locks. A metadata change that executes quickly after admission can still wait behind an existing transaction for minutes. This distinction should influence deployment planning. Estimating migration risk from table size alone misses the failure demonstrated here. A three-row table can experience the same admission problem as a much larger one. The operational question is therefore twofold: how expensive is the change once admitted, and how long may it wait while affecting other requests? The Blocker Can Be Idle For tables used within a transaction, MySQL retains transactional metadata locks until the transaction ends. Finishing a SELECT does not necessarily release them. See MySQL’s metadata-lock lifetime documentation. Consequently, searching only for slow queries can miss the session responsible. Application code commonly stretches transactions beyond their SQL work. An endpoint reads data, calls a remote service, performs additional processing, and commits afterward. The database query takes milliseconds, but the transaction remains open throughout the external work. A connection can therefore appear idle while still preventing a schema change. Measure transaction age separately from query duration. Review transaction boundaries around network calls, user interaction, and background work. Moving unrelated operations outside a transaction can reduce exposure without changing the migration itself. Preflight checks help identify existing blockers, but they cannot guarantee that another transaction will not start immediately afterward. Why Later Readers Wait Without the pending ALTER, the two reads in the example could coexist. The DDL request changes the scheduling situation. MySQL normally prioritizes metadata write-lock requests over read-lock requests, with documented exceptions. That policy prevents a continuous stream of readers from indefinitely starving a writer. The priority rules appear in the metadata-lock acquisition documentation. On a busy table, this creates a widening queue. One open transaction delays the migration, and the waiting migration delays newly arriving work. Those requests can occupy application connections until the pool fills. Subsequent requests may then wait for a connection before reaching MySQL at all. This is why a migration incident can look like general database saturation even when CPU and storage activity remain modest. The database is coordinating access, not necessarily executing expensive queries. Inspect Ownership and Pending Requests Start with a diagnostic query that connects waiting sessions to potential blockers: MySQL SELECT object_schema, object_name, waiting_pid, waiting_lock_type, blocking_pid, blocking_lock_type FROM sys.schema_table_lock_waits WHERE object_schema = 'mdl_demo' AND object_name = 'orders'; The sys view documentation describes these session and lock relationships. Use the result alongside transaction age and application ownership. Identify which connection belongs to the migration runner and which belongs to the application. For lower-level investigation, inspect performance_schema.metadata_locks and correlate its owner thread identifiers with performance_schema.threads. Distinguish granted locks from pending requests rather than treating every displayed lock as an active blocker. Do not automatically terminate the oldest connection. It may hold legitimate work whose rollback is expensive. When the migration is deferrable, removing its waiting request may be the less disruptive intervention. Set the Correct Timeout The familiar innodb_lock_wait_timeout controls InnoDB row-lock waits. It does not bound the metadata-lock wait responsible for this incident. Configure the migration connection instead: MySQL SET SESSION lock_wait_timeout = 3; ALTER TABLE mdl_demo.orders ADD COLUMN source VARCHAR(32) NULL, ALGORITHM=INSTANT; In MySQL 8.4, lock_wait_timeout governs metadata-lock acquisition attempts and defaults to 31,536,000 seconds. The timeout applies separately to each acquisition attempt, so it is not a universal three-second statement deadline. See the server variable reference. Three seconds is illustrative. Choose a budget based on acceptable application disruption. Apply the setting on the actual connection executing the migration. Setting it on a separate administrative connection has no effect on the migration session. A dedicated connection also avoids leaving unexpected timeout settings in an application pool. Verify That Timeout Releases the Queue Extend the reproduction using a fresh table definition. Keep Session 1’s transaction open. Start the migration with a short metadata-lock timeout, then issue Session 3’s read while the migration is waiting. When the ALTER times out and its pending request is removed, the later read should proceed in this isolated scenario even though Session 1 remains open. That observation identifies the queue boundary. The two reads were compatible; the waiting schema change prevented the later one from advancing. A failed migration must remain visible to deployment automation. Do not mark it complete merely because application traffic recovered. Retry with backoff and a bounded attempt count rather than repeatedly recreating the same queue. After a client disconnect or ambiguous execution result, inspect the actual schema before retrying. Make Admission Part of the Deployment Contract A production migration policy should specify the permitted algorithm, metadata-lock wait budget, retry behavior, and conditions for postponement. Monitor pending DDL alongside transaction age, connection-pool usage, and request latency. Alerting only on migration duration can detect the problem after application capacity has already been consumed. Test both successful admission and blocked admission before deployment. The latter proves that the migration yields when production conditions are unfavorable. Instant DDL remains valuable because it avoids unnecessary table reconstruction. Its safety depends on the surrounding lifecycle: transactions must finish, lock requests must be admitted, and waiting migrations must have a bounded opportunity to interfere with traffic.
Principal PM, Azure Cosmos DB,
Microsoft
Cloud Ops DBA,
Allspring
Award-winning Software Engineer and Architect,
OS Expert