Databases

In high-volume data platforms, hardcoding validation logic into individual processing pipelines creates significant operational drag. As an enterprise data asset footprint grows, maintaining manual checks for hundreds of tables inevitably leads to mounting technical debt, silent schema drift, and a fragmented audit trail. To achieve data governance at scale, data architects must decouple validation rules from the execution engine. By utilizing a centralized metadata repository to dynamically generate validation suites, organizations can transform data quality from a reactive, script-based bottleneck into a configuration-driven infrastructure asset. The Metadata-Driven Architecture Instead of embedding validation constraints directly inside an ETL/ELT pipeline, this pattern isolates validation rules inside a centralized relational database schema. The orchestration engine programmatically queries this metadata at runtime, constructs the validation suites on the fly, executes them against target tables, and routes the evaluation metrics to an observability layer. This architecture provides three primary engineering advantages: Decoupled Governance: Data stewards can alter business rules or add expectations via simple DML updates without modifying or redeploying production application code.Schema Drift Resilience: The engine dynamically adapts to structural variations by programmatically evaluating target datasets against rules defined at the column level.Centralized Observability: Every rule execution generates a standardized, traceable metric payload, laying a consistent foundation for real-time data auditing and data lineage maps. 1. Defining the Metadata Schema (DDL) To implement this framework in an enterprise Lakehouse ecosystem, the metadata table must act as an immutable source of truth for constraints. Below is the production DDL required to initialize the control directory in Snowflake or Databricks: SQL CREATE TABLE data_quality_rules ( rule_id INT IDENTITY(1,1), table_name VARCHAR(255) NOT NULL, column_name VARCHAR(255) NOT NULL, expectation_type VARCHAR(255) NOT NULL, expectation_kwargs VARIANT NOT NULL, -- Stored as JSON object is_active BOOLEAN DEFAULT TRUE, updated_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(), CONSTRAINT pk_rule_id PRIMARY KEY (rule_id) ); -- Seed metadata rules for execution tracking INSERT INTO data_quality_rules (table_name, column_name, expectation_type, expectation_kwargs) VALUES ('CUSTOMERS', 'CUST_ID', 'expect_column_values_to_not_be_null', '{}'), ('CUSTOMERS', 'AGE', 'expect_column_values_to_be_between', '{"min_value": 18, "max_value": 60}'), ('ORDERS', 'ORDER_ID', 'expect_column_values_to_not_be_null', '{}'); 2. Implementation: The Programmatic Execution Engine The core execution wrapper leverages Python and Great Expectations (gx) to programmatically turn rows of metadata into active validation suites. This script establishes a secure database connection via SQLAlchemy, harvests active constraints, generates runtime batch requests, and triggers structured checkpoints. Python import os import json import logging from datetime import datetime import pandas as pd from sqlalchemy import create_engine import great_expectations as gx from great_expectations.core.batch import RuntimeBatchRequest # Configure structured logging for production auditing logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s') logger = logging.getLogger(__name__) class MetadataDataQualityEngine: def __init__(self, connection_string: str): self.engine = create_engine(connection_string) # Initialize Great Expectations ephemeral context for programmatic runtime control self.context = gx.get_context(context_root_dir=None) def fetch_active_metadata(self) -> pd.DataFrame: """Harvests active validation configurations from the centralized store.""" query = """ SELECT table_name, column_name, expectation_type, expectation_kwargs FROM data_quality_rules WHERE is_active = TRUE """ try: df = pd.read_sql(query, self.engine) logger.info(f"Successfully harvested {len(df)} active validation rules.") return df except Exception as e: logger.error(f"Failed to query metadata repository: {str(e)}") raise def compile_expectation_suite(self, table_name: str, rules_df: pd.DataFrame): """Assembles validation rules into a Great Expectations suite on the fly.""" suite_name = f"{table_name}_suite" suite = self.context.add_or_update_expectation_suite(expectation_suite_name=suite_name) # Filter metadata constraints for the specific target asset table_rules = rules_df[rules_df['table_name'] == table_name] for _, row in table_rules.iterrows(): # Parse JSON kwargs configuration gracefully kwargs = row['expectation_kwargs'] if isinstance(kwargs, str): kwargs = json.loads(kwargs) kwargs['column'] = row['column_name'] # Programmatically map string values to structured GX expectation objects expectation_config = gx.core.ExpectationConfiguration( expectation_type=row['expectation_type'], kwargs=kwargs, meta={"notes": f"Automated constraint enforcement for column: {row['column_name']}"} ) suite.add_expectation(expectation_config) self.context.add_or_update_expectation_suite(suite=suite) return suite def execute_quality_checkpoint(self, table_name: str, target_df: pd.DataFrame): """Builds a runtime batch request and evaluates data against the generated suite.""" timestamp = datetime.utcnow().strftime("%Y%m%dT%H%M%SZ") suite_name = f"{table_name}_suite" checkpoint_name = f"{table_name}_checkpoint" # Unique runtime composite signature prevents processing trace collisions batch_request = RuntimeBatchRequest( datasource_name="lakehouse_runtime_datasource", data_connector_name="runtime_data_connector", data_asset_name=f"{table_name}_{timestamp}", runtime_parameters={"batch_data": target_df}, batch_identifiers={"table_name": table_name, "execution_timestamp": timestamp} ) # Register and fire a dynamic checkpoint execution self.context.add_or_update_checkpoint( name=checkpoint_name, config_version=1, class_name="SimpleCheckpoint", validations=[{ "batch_request": batch_request, "expectation_suite_name": suite_name }] ) logger.info(f"Launching data quality checkpoint for table: {table_name}") return self.context.run_checkpoint(checkpoint_name=checkpoint_name) # Production Loop Execution Pattern if __name__ == "__main__": SF_CONN = "snowflake://<user>:<pass>@<account>/<db>/<schema>?warehouse=COMPUTE_WH&role=SYSADMIN" dq_engine = MetadataDataQualityEngine(connection_string=SF_CONN) metadata_rules = dq_engine.fetch_active_metadata() distinct_target_tables = metadata_rules['table_name'].unique() for current_table in distinct_target_tables: try: # Stage current batch dataset from target engine raw_data_df = pd.read_sql(f"SELECT * FROM {current_table}", dq_engine.engine) # Step 1: Build suite dynamically from relational rules dq_engine.compile_expectation_suite(table_name=current_table, rules_df=metadata_rules) # Step 2: Validate batch data and extract metrics payload eval_result = dq_engine.execute_quality_checkpoint(table_name=current_table, target_df=raw_data_df) if not eval_result["success"]: logger.warning(f"Data Quality anomalies detected on asset: {current_table}") else: logger.info(f"Asset {current_table} successfully cleared all metadata expectations.") except Exception as err: # Fault isolation ensures an asset failure never crashes cascading pipeline steps logger.error(f"Processing loop interrupted on asset {current_table}: {str(err)}") continue 3. Production-Grade Engineering Guardrails Building a dynamic system requires putting structural guardrails around the execution engine to prevent it from failing under enterprise pressures. Fault Isolation and Pipeline Resilience: Never let a validation failure on an upstream or non-critical business table halt your entire orchestration loop. Wrapping individual target assets in localized try-except blocks ensures that a failure on a secondary table (like CUSTOMERS) does not block downstream transactional tables (like ORDERS) from completing their validation lifecycles.Idempotency and Batching Identifiers: Every unique quality run must be traceable back to a specific moment in time to avoid overwriting or colliding results in your metadata tracking layer. Pair the table_name with an immutable execution_timestamp (such as a UTC ISO string) as a composite batch identifier. This guarantees an explicit audit trail across parallel streaming windows or backfilled data runs.Metadata-as-Code Frameworks: Treat the validation matrix table with the same operational rigor as production application code. Changes, additions, or deprecations of quality thresholds must follow a strict GitOps progression. Use schema migration version control tools (like Flyway or Liquibase) to manage, track, and deploy DML changes safely across staging and production clusters.Proactive Alerting Integration: Local HTML docs are insufficient for zero-downtime platforms. The metadata evaluation output dictionary must be integrated directly into cloud native alerting systems. Configure Webhook integrations or cloud alerting channels to route failed validation metrics directly to Slack channels or PagerDuty schedules. This ensures on-call engineers are proactively notified the moment a metric payload trends outside acceptable operational thresholds. Summary: The Architectural Impact Transitioning to a metadata-driven approach shifts data quality from a reactive "clean-up" task to an integrated, proactive engineering asset. By treating validation criteria as configurable metadata parameters rather than hardcoded script directives, architects eliminate technical debt and bridge the gap between business semantics and computing layers. This decoupled architecture provides the strict governance framework required to support high-stakes analytics and downstream machine learning layers, ensuring that every data element hitting your warehouse is automatically and transparently vetted before reaching production consumers.

When someone uploads an image to your application, it might look perfectly fine at first glance. It might open correctly, pass file validation, and avoid triggering any obvious red flags. But that still doesn’t necessarily mean the file is trustworthy. In modern systems, especially marketplaces, identity verification flows, insurance submissions, academic portals, and editorial pipelines, how an image was created can matter just as much as what that image shows. A synthetic, AI-generated image can be technically valid and "safe" while still being completely inappropriate for the context in which it’s used. AI image detection offers your system a way to make better decisions about the content it accepts. It's not a final authority; rather, it's a form of content moderation that ensures you have clear visibility into and governance over the content you process and share. In this article, we’ll walk through why AI detection is challenging, how to design a practical workflow around it, and how to implement AI image analysis in C# using an image recognition API. Why AI Image Detection is Difficult You might've noticed that the quality of AI-generated images has improved dramatically in recent years. The early giveaways (extra fingers, warped text, strange lighting, etc.) are becoming less common. If you ask strangers on the street to pick out AI-generated images from a lineup of otherwise authentic photos, you're unlikely to get a consistent answer. That's because today's generative models are designed to mimic real-world photography and illustration patterns. That means even a perfectly normal-looking image might still be synthetic, and a slightly odd-looking image might still be completely real. This creates a considerable challenge. We live in a world where there's no single visual feature that reliably, reproducibly separates real from synthetically generated content. A bit unnerving, right? Metadata doesn’t always solve the problem either. While images can contain useful provenance information, that often gets stripped out during editing, compression, or platform uploads. Even something as simple as re-saving an image can remove the original creation context entirely. And of course, it's trivial to change file names without affecting the underlying pixels, so you're unlikely to catch anyone red-handed with an AI platform staring you in the face. Because of this, modern AI detection systems rely instead on probabilistic models. Instead of saying “this image is AI-generated,” they try to estimate how likely that is to be the case based on learned patterns from large datasets of real and synthetic images. There's an important distinction to make here: in AI detection, we’re looking for confidence rather than absolute truth. If we want to benefit from AI detection services, it's essential that we embrace their uncertainty. Here's what that means in practice: high confidence scores justify content review or restriction, mid-range scores indicate uncertainty, and low scores reduce concern but do not guarantee authenticity. AI detection works best as one layer in a broader content validation strategy. Designing a Practical Detection Workflow Before thinking about how to invoke an AI detection service, which we'll look at later in this article, it’s useful to step back and consider where it fits within a broader image-handling workflow. In practice, AI detection is just one stage in a normal pipeline that begins the moment a user submits an image. As we'll see in our demonstration later on, the service handling AI detection might be exposed as an API, but it could just as easily be a background job, a message queue consumer, or even an internal library call if you're ambitious about building internal tools. The important idea isn't how you invoke that service, but when and why it gets called in your system. A typical content upload workflow starts with basic validation. That means checking whether a file is present and readable, whether the format is supported (such as JPEG or PNG), whether the file size falls within acceptable limits, and whether the actual file type matches its extension. These checks are important because they protect your system from malformed or malicious inputs before any deeper analysis happens. That's not unique to the AI detection topic, of course, but it's critical nonetheless. Once a file is validated, the next step is normalization. In this case, the idea isn't to alter the image content; rather, it's to standardize how the image is handled as it moves through the system. The goal here is to maintain consistency regardless of how the detection service is implemented. This includes keeping the original image data intact, passing it through a stream, buffer, or temporary storage layer, and ensuring the data is correctly positioned and accessible for downstream processing. This matters because even small transformations like resizing or re-encoding can change the very signals you’re trying to analyze. So, in most analysis workflows (that is, pipelines that prioritize preserving signal integrity), you should defer any modification unless it's explicitly required. At this point, the image is finally ready to be evaluated by an AI detection system. This evaluation can be triggered through your existing processing pipeline, using whichever execution model your system is already built around. Regardless of the mechanism, the role of the detection step is the same: produce a structured assessment of whether the image is likely to be AI-generated or manipulated. From there, your application can apply its own business rules. There isn't a "one size fits all" way to do this: the exact thresholds for AI detection should always reflect your use case. For example, a social media avatar and a legal document shouldn't necessarily be treated the same way; there's a bit more at stake if the latter is fabricated. Where AI Image Detection Fits in an Application AI image detection should run as early as possible in your content ingestion pipeline. That means at upload, submission, or intake; sometime before content is trusted or passed downstream. There are two general scanning approaches here that make sense in different contexts: synchronous and asynchronous detection. In synchronous detection, the system scans content right away and waits for a result before continuing. This approach is simple and provides immediate feedback, but it does add latency to the whole workflow. It’s usually best for controlled flows where users expect to get instant validation. In asynchronous detection, images are accepted first and then analyzed in the background. This approach generally scales better, and it avoids blocking users, which makes it the ideal choice for high-volume (and especially non-urgent) workflows. Most enterprise-scale workflows will probably be asynchronous. Ultimately, the core rule for both approaches is the same: don’t trust the image until it's been thoroughly evaluated. Detecting AI-Generated Images With C# Now that we've covered some of the biggest factors involved in detecting AI-generated images, we'll go ahead and explore one way to implement this functionality in C#. In this example, we'll use an image recognition API. The SDK boils the process down to two steps: submit an image and receive a structured detection result. If you’re considering other options for AI image detection, you might want to look into Hive AI Detector alternatives, CLIP-based classifiers, or locally hosted models from Hugging Face. First, we install the package: C# Install-Package Cloudmersive.APIClient.NETCore.ImageRecognition -Version 2.2.0 Next, we import the required namespaces: C# using System; using System.IO; using Cloudmersive.APIClient.NETCore.ImageRecognition.Api; using Cloudmersive.APIClient.NETCore.ImageRecognition.Client; using Cloudmersive.APIClient.NETCore.ImageRecognition.Model; Now we configure the API key and prepare the image stream: C# var configuration = new Configuration(); configuration.AddApiKey("Apikey", "YOUR_API_KEY"); var apiInstance = new AiImageDetectionApi(configuration); Now we can open the image stream and call the detection endpoint: C# try { using (var imageFile = new FileStream( @"C:\temp\input-image.png", FileMode.Open, FileAccess.Read)) { ImageAiDetectionResult result = apiInstance.AiImageDetectionDetectFile(imageFile); if (result == null || !result.AiGeneratedRiskScore.HasValue) { Console.WriteLine( "The image could not be conclusively evaluated."); } else { Console.WriteLine( $"Clean result: " + $"{result.CleanResult?.ToString() ?? "Unknown"}"); Console.WriteLine( $"AI risk score: " + $"{result.AiGeneratedRiskScore.Value}"); Console.WriteLine( $"Possible AI source: " + $"{result.AiSource ?? "Unknown"}"); } } } catch (Exception e) { Console.Error.WriteLine( "Exception when calling " + "AiImageDetectionApi.AiImageDetectionDetectFile: " + e.Message); } This example keeps error handling simple for clarity, but in a production system you’ll obviously want more granular handling for scenarios like invalid input, network failures, API rate limits, etc. Note that a failed detection should never silently pass the image through. It should instead result in a clear “unverified” or “pending review” state (or something similar). Interpreting the Detection Result You get three response fields: CleanResult is a quick yes/no check. true means no AI-generated content was detected; false means there might be a match. This result is based on the risk score. AiGeneratedRiskScore runs from 0.0 to 1.0. Higher scores mean a higher chance the content was AI-generated. Scores above 0.8 are high risk and trigger CleanResult: false. AiSource is the final field, and it may show which specific AI content generation tool likely generated the content. It’s intended to be useful context, but it won’t always be available, so it obviously shouldn't be relied on. Together, these give you a quick result, a risk score, and optional context. None of them is absolute proof, but they can help you make a more informed decision. Turning the Result into an Application Decision Once you have a risk score, you can map it to a simple decision model (that's what I would do). Here’s one quick example of that: C# public enum ImageDecision { Accept, ManualReview, Reject, Unverified } public static ImageDecision EvaluateImage( ImageAiDetectionResult result) { if (result == null || !result.AiGeneratedRiskScore.HasValue) { return ImageDecision.Unverified; } double riskScore = result.AiGeneratedRiskScore.Value; if (riskScore > 0.8) { return ImageDecision.Reject; } else if (riskScore > 0.5) { return ImageDecision.ManualReview; } else { return ImageDecision.Accept; } } This structure is (intentionally) simple, but the meaning behind each outcome is flexible. For example, in a lot of real-world systems, “Reject” might actually mean "hold for review". “ManualReview” might "trigger a human workflow", and “Accept” might still be logged for auditing. It's also worth noting that in this example code, we aren't directly using the CleanResult response when making the decision. We are bypassing that completely, only using the risk score from the broader detection result. If you wanted to, you could inspect CleanResult as part of your application logic; for example, to distinguish between a clean result, a flagged result, or an inconclusive response. You could then use that information alongside the risk score when deciding whether to accept, review, or reject an image. Conclusion AI image detection doesn’t give you certainty, but it does give you something extremely valuable in today's world of increasingly indistinguishable AI content: a structured way to reason about uncertainty. By combining file validation, careful input handling, and probabilistic AI detection, you can build workflows that are both practical and resilient. In C#, integrating an image recognition API gives you a straightforward way to evaluate images at the point of entry, interpret risk scores flexibly, and apply consistent business rules without over-relying on one individual signal. The API approach makes sense because it keeps the recognition logic focused, reusable, and easier to update as models and requirements change, while allowing the rest of the application to work with a clear, stable interface. The key takeaway is ultimately pretty simple: AI detection is necessary for modern content systems, but it should be treated as guidance rather than a judgment. When used judiciously, it will become a powerful part of a broader trust and verification strategy.

By Brian O'Neill CORE

Hi everyone! This is Mikhail Polivakha, tech lead of the Axelix project (btw, give us a star!). In my experience consulting teams that build enterprise applications, I keep getting asked: What about natural keys in the database? Say I have a column that lets me explicitly identify a record, should I use it as the Primary Key? And over my years of designing enterprise systems, and over the time spent designing Axelix, I've come to a conclusion: just never use natural keys, ever. When you feel the urge to do it, step outside, take a walk, get some fresh air, and it'll pass. I understand this answer is categorical, so I'll add a couple of clarifications about what to do if you do happen to have a unique discriminating column that, as it seems to you, lets you uniquely identify a record in a database table. The full, nuanced answer is of course more complicated, but if I have to give you a straight TL;DR: When designing new systems, in my opinion, you should always use surrogate primary keys. Now let's get into why I think so. Rules Written in Blood Rules like the one above are usually born out of getting burned on real projects several times. I have an absolutely perfect story straight from Open Source Axelix. The source code is on GitHub, so if you feel like it, go and check for yourself. I won't go too deep into the details, but so that you grasp the depth of the problem, I'll give you a bit of context. In some places I'll also deliberately simplify the parts I consider non-essential to understanding the problem. At its core, Axelix consists of two components. The first is Master, a standalone application that acts as the "brain" of the system. It's deployed either in a K8S cluster, or launched as a separate Docker container, or even just run as a plain JAR. Master aggregates information from your Spring Boot services and stores it in its database. This information is later used to understand the "maturity" of your ecosystem, the distribution of versions of key components (for example, Spring Boot or Java versions), tracking known tech-debt issues, and so on. Axelix Architecture If we picture a typical company, they usually have a K8S/OpenShift cluster where their production runs. Almost always, a given application is deployed to production not as a single copy, but as a set of Instances (a K8S Deployment + a configured HPA, and so on). So in practice we have one logical application that is physically a set of different containers. Now I think we have enough context to discuss the problem. The Beginning. Natural Keys: Sure, Why Not! As I said, Axelix stores data in its database to understand the overall state of your application. Let's call this table "Application" (in reality, this abstraction is named differently in Axelix, but again, I'm simplifying heavily). This is where application-level data lives. Master can collect data from Spring Boot microservices via both a push and a pull model, but regardless of the model, it collects data at the Instance level, not at the Application level, i.e., not for the whole application. So Master polls each Instance, and it's then Master's job to somehow figure out that all those Instances belong to the same application. Understanding Instances The question is: How is Master supposed to do that? How does it figure out that these Instances belong to the same application? (Don't forget: Axelix isn't always deployed in K8S. Relying on ClusterIP services and the like is not an option.) Actually, if you think about it a little, the solution is right on the surface: we can just aggregate information at the level of the GroupID/ArtifactID pair from the GAV coordinates (the standard format of a Maven distribution). After all, all the Instances are required to have the same GroupID/ArtifactID, right? Aggregating Instances Information Broadly speaking, yes, that's true. Some might think we could key off other things, for example spring.application.name or similar, but unfortunately that won't work, for a number of reasons. That's another story, though, and it's not important right now. So imagine we're designing such a relation in the database. Here's my question for you: what primary key would you want for a table like this? When we designed the "Application" entity, it seemed right to make the {groupId/artifactId} pair the primary key, i.e., a Natural Composite Key. And it's so convenient! When information about some Instance arrives in Axelix Master (whether via the push or the pull model): We can update the data with a simple ANSI SQL MERGE or INSERT ... ON CONFLICT DO ..., because artifactId/groupId is the primary key! Spring Data JDBC (which we use as the ORM in Axelix Master) in 4.1 finally learned how to do UPSERTs on the primary key, and now we can just do this via JdbcAggregateTemplate: Java @Transactional public void reloadCurrentState(BasicRegistrationMetadata metadata) { Application application = converter.currentSnapshot(metadata); jdbcAggregateTemplate.upsert(application); } And how nicely it works out for the front-end! And here we arrive at the fact that a natural key carries business meaning by itself! That, by the way, is one of the genuinely nice properties of natural keys. What do I mean? For example, in a situation where we just need to display the name of our "Application", and the name alone is enough, we can use the artifactId, i.e., a part of the composite natural key. No need to "fetch anything extra", and so on. So where's the problem? Given everything I've said so far, is this problem really so critical that I claim you shouldn't use natural keys at all? Yes, it's that serious. And here's why. So What's the Deal? A Bit of Philosophy The older a person gets, the more prone they are to doubting various things (for example, my claim in this article! And that's okay!). This is because people accumulate experience. People who have been doing engineering for a good while accumulate experience and come to understand just how much everything changes, and how much they still don't know (experienced engineers understand me 100% right now). A vendor comes and goes. So does an employee. The uniqueness of a natural key... Ray Dalio (an amazing person and macro investor, I highly recommend reading him) wrote in his book "Principles:" Sincerely believe that you might not know the best possible path and recognize that your ability to deal well with "not knowing" is more important than whatever it is you do know. This is incredible wisdom. The idea is to accept the fact that your knowledge of the outside world is limited, and it will always be many times smaller than the set of things you don't know but which nonetheless affect your life/system/etc. And the most important thing in such a situation is to be able to work WITH YOUR OWN NOT-KNOWING of something, to hedge risks. How does this relate to natural keys? Very simply: if some discriminator seems like an obvious key in the moment, just remember that the scope of your knowledge is incomparably small next to what you don't know. And that "invariant" you're pinning your hopes on, the one you think will be unique: it can very easily stop being unique half a year later. What's more, the scope of your knowledge will keep growing. Over time, you (yes, you, my friend) grow as an engineer. After a while, you'll look at this code, or at the design of this system, and say: How on earth!? How could I have done this? This crap is just awful; it was obvious this key would break uniqueness in case X! And it'll be obvious to you. But later. When you become wiser. By the way, if you don't have these moments of "enlightenment" in your career, where you scold yourself for your own past decisions, that's a very strong warning sign that you've stopped growing as a specialist. Back to Engineering Let's get back a bit closer to the technical side. The main point of the previous section is that what seems like the uniqueness of a natural key today can easily stop being unique later. Now let's think like engineers: how bad is that, really? How bad is it that we'll be wrong about our natural key (composite or not, doesn't matter right now) turning out not to be unique? The truth is that a record's primary key must always (!) have (among others) the following two distinguishing properties: 1. It Must Be Immutable When we assign a record some key by which we identify it, we then have no right to change it. Why? Because the outside world that depends on our system stores exactly this ID, this primary key, to identify the record. It stores a reference, not the record itself. For example, imagine you have a third-party service that stores user profiles: user-service. And there they decided to use email as the natural key. You write a service that orchestrates users' subscriptions to various services within the ecosystem. And now you need to fetch a user's profile from that user-service system for your operations. How will you fetch it? By email, of course! It's the "unique key", after all. And now imagine that the Product Owner comes along and says: In our service we want to let a user change the email tied to their account. That is, effectively, an already-existing record in the database will have its identity changed. By changing a record's ID in this user-service, any other system, including yours, can no longer find the profile it needs. That would be a mass incident. That's why an ID must always be immutable. 2. It Must Uniquely Identify a Record at Any Moment in Time Now imagine that, all of a sudden, the folks from the team that develops user-service get a requirement. They're told: Hey, we sometimes run into a situation where a user once created an account and tied their email to it. And now they want to somehow delete the old account (which they created some 10 years ago) and create a new one, and attach the same email to it. We wouldn't want to delete the old account (in enterprise, for various reasons, hard deletes are rarely done). So, shall we do it? Here the problem is even more obvious. Not only can your system, which depends on user-service, no longer find the right user profile (there could be several of them now!), all existing contracts break, and to "fix" them you'll have to "re-define" the ID (it's no longer unique, and you can't rely on the ID alone anymore). And if we have to change the ID, then see the section above. Cause of Death: Natural ID Mistakes come in varying degrees of severity. There are mistakes that have a local effect and can be fixed relatively quickly and easily. But keys that identify data in distributed systems are something that spreads across the entire distributed system into its most varied corners. So the moment you suddenly realize with horror that the natural id is no longer unique, the so-called blast radius will be fantastic, especially in modern microservice architecture. So, friends, people die of different causes. Someone died of cancer, someone died of heart failure. And someone simply chose a Natural ID as their primary key, and then received an email in their inbox, or suddenly heard at a daily standup that the uniqueness assumption of this key was about to be shaken. I suggest a moment of silence before reading on, in memory of those engineers who paid the price for choosing Natural ID as their primary key… Thank you. The Axelix Case Let's get back to the real case we had at Axelix. We haven't hit GA yet (we're actively working on it), but we already have several Milestone releases. We embed with various companies to gather feedback, potential bugs, problems, and so on. And one company tells us: You know, it just so happens that we essentially have two services: service A and service B. They're basically identical, just deployed in different network segments. They have the same groupId and artifactId. Nevertheless, service A is maintained by this team, and service B by that team. I've simplified all the details, but this is the general message. So, we have a problem in this case - we can no longer identify an application the way we wanted, via the artifactId/groupId pair. This is exactly what typically happens after a while, when the system is already deployed in production. Remember Ray Dalio! ... What exists within the area of "not knowing" is so much greater and more exciting than anything any one of us knows. It's precisely because of situations like this that you need to ask users to provide Axelix with the information about what the unique ID of a given application is themselves (for example, in application.yaml). But Natural IDs Do Have Advantages... In my experience, the fact that a Natural ID carries business meaning that can be used somewhere (for example, displaying an application's name on the UI as the artifactId, as I already showed with the Axelix example) is solved simply by designing your API. In other words, even with surrogate keys, you can design your API so that you don't have to fetch extra data from the backend; it's not a big problem (for example, get some metadata, put it into the state manager on the front-end, and so on; there are plenty of ways). What's really important about natural keys is that they force you to think about the invariants of your data. That is, for example, logically, if your email is unique, then it makes sense to create an index on it (which is, for instance, what Postgres does when you ask it to create a Primary Key). And to avoid having two different indexes, why not make email the primary key, since in that case there would be just one index, only on email? That's a broadly valid argument, but I'll put it this way: it's not worth it. If you don't use email as a Natural ID, then whether or not to create a unique index on email is a decision to make case by case. I'd say that for 95%+ of cases the answer is definitely yes, and there won't be any problems with it. That said, for large write-heavy systems with a lot of data, this may create a certain overhead, but again, usually negligible at the scale of the system. And finally, regarding MERGE / INSERT ON CONFLICT operations. You can perfectly well do them not on the primary key, but on any constraint, for example, on a UNIQUE constraint that you explicitly define in a migration. Conclusions Based on my experience, I can tell you one thing: remember that the scope of your not-knowing is by nature far larger than the scope of your "knowing". That's why it's very dangerous to build an assumption that a Natural ID, which seems unique to you for a given record in the moment, will make a good Primary Key. That said, it's worth acknowledging that the main advantage of a Natural ID is that it forces you to think about what invariants your data has in general. And these invariants should give you insights into how to model your data access and storage patterns, for example, defining unique b+tree indexes for the email column. Remember: indexes and things like that can later be removed without consequences for the whole system. Changing primary keys, on the other hand, is a dead end.

Stolen credentials served as the entry point in 22% of breaches last year, and in attacks on basic web applications that figure climbs to 88%. Those numbers describe a password problem, and databases sit at the end of nearly every attack path. A username and password prove nothing about the machine presenting them. Mutual TLS closes that gap by requiring both sides of a connection to present certificates and prove who they are before a single query runs. Securing production database connections has convinced me that enterprises implement mutual TLS using readily available tools and established certificate management practices. What Certificate-Based Authentication Actually Closes Off A password travels, gets shared, gets phished, and gets left behind in a script. A certificate bound to a specific client does none of those things easily, which is why mutual authentication blunts three familiar attack patterns: stolen credentials replayed from an unfamiliar host, spoofed clients impersonating an application server, and lateral movement after an attacker gains an initial foothold. Enterprises usually maintain password rotation policies that are triggered after a set period or upon an employee's exit from the team. Certificate-based authentication safeguards the data in case a team misses rotating those credentials, because the password alone no longer grants entry. I ran into this while setting up an open-source alerting tool, where the password had to live in a config file or a session variable. The session variable fails when the tool auto-restarts during maintenance, leaving a hardcoded password or a decryption utility to mask it. A certificate addresses that problem because its lifetime can be governed by the organization's security policies. Once the certificate expires, a password alone is no longer sufficient to authenticate the client. Machine identities now outnumber human identities by more than 80 to 1 in the average organization, and each database connection string is one of them. The Rollout Decisions That Matter Most An mTLS program stands on three design choices. The first is the certificate authority, where an internal CA gives the team full control over issuance and revocation for database traffic that never leaves the estate. The second is cipher selection, which deserves more attention than it gets, because Oracle, MySQL, and MongoDB each negotiate TLS differently, and a cipher suite that works on one engine can fail the handshake on another. The third is rotation, and this is where programs die quietly. 81% of organizations have suffered at least two outages caused by expired certificates in a two-year window. Six months to a year is an ideal certificate lifetime across a polyglot estate, though the organization's security baselines govern, and estates handling critical PII or PCI data, or carrying past breach attempts, can justify a reduced lifetime. Renewing very frequently creates its own outages, because some systems require a reboot to bring new certificates into effect, a real challenge for heavily used 24/7 applications without a high availability solution ready. Keeping lifetimes consistent across Oracle, MongoDB, MySQL, and PostgreSQL helps manage the rotations, but databases are not all created in a single day, so expiry timelines differ and an inventory or dashboard tracking every expiry becomes essential. Above all, automating renewal wherever possible reduces the risk of downtime from an expired certificate. Securing the Monitoring Layer Itself Monitoring an encrypted estate raises a question teams often skip, which is how to keep the monitoring path from becoming the weak point. Many organizations use Prometheus to collect database metrics, with more than two-thirds of organizations running it in production, yet exporters may be deployed with unencrypted scrape endpoints if they are not configured to use TLS. One common deployment approach is to run the database exporter process on the database server itself. In Prometheus-based environments, configuring both the client connection (--config.my-cnf) and the exporter (--web.config.file) to use client certificates allows the monitoring pipeline to follow the same mutual authentication model as the database it monitors. This helps ensure that metrics are collected over authenticated, encrypted connections rather than introducing a weaker path into the environment. Choosing the Right Approach Organizations can implement mutual TLS using either commercial certificate management platforms or open-source tooling. The right approach depends on factors such as certificate volume, compliance requirements, auditing needs, and the operational resources available to manage the environment. Early in my career, I assumed enterprise-licensed products were the default choice for every deployment. Over time, I found that decision is more nuanced. Large environments managing tens of thousands of certificates may benefit from centralized lifecycle management, auditing, and governance features, while many organizations can successfully implement mTLS using open-source tools that meet their operational requirements. The priority should be selecting an approach that supports reliable certificate issuance, rotation, and revocation while integrating with existing security processes. Regardless of the tooling, a well-managed certificate lifecycle is what ultimately strengthens database authentication and reduces operational risk.

Most developers I've worked with write SQL every day. Very few of them are DBAs. According to the 2024 Stack Overflow Developer Survey — 65,000 developers across 185 countries — database administrators make up just 0.3% of the developer population. The tools built for SQL performance were designed for that 0.3%. I built QueryTuner for everyone else. I've spent 13 years as an application architect. In that time, I've watched the same situation repeat itself across teams: a query is slow, the developer who wrote it has to fix it, and the tools available to them are either way too expensive or way too generic. Enterprise monitoring agents like pganalyze or Datadog Database Monitoring cost hundreds of dollars a month and require installing an agent with full database credentials. Generic AI LLMs don't know whether you're on Oracle or MySQL. There's nothing useful in between. That gap is what QueryTuner tries to fill. The Core Constraint: No Database Connection The first decision I made was also the most important one. QueryTuner would not connect to any database. Every enterprise SQL tool requires credentials. In most organizations, getting credentials approved takes longer than just fixing the query manually. I wanted something a developer could try in 30 seconds without asking anyone for permission. The tradeoff is real. Without connecting to your database, QueryTuner can't see actual row counts, current index usage, or live execution plans. But it can analyze the SQL text itself — and most slow query problems come from a small set of well-known patterns. You don't need to connect to a database to spot a function wrapped around a column in a WHERE clause. The Heuristic Engine QueryTuner runs 12 deterministic rules against every query before anything else happens. These rules catch the patterns that cause most slow query problems in production: Functions on indexed columns are the most common. If you write WHERE YEAR(created_at) = 2024, the database has to call YEAR() on every row before it can filter. The index on created_at becomes useless. The fix is a range condition: WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'. The index works again. Leading wildcard LIKE patterns are the second most common. LIKE '%value' can't use a B-tree index. The database reads every row. Most developers don't know this until they see it in an execution plan for the first time. Correlated subqueries in the SELECT clause are the most expensive. If you have a subquery inside your SELECT list, it runs once for every row in the outer query. On a table with 50,000 rows, that's 50,000 separate database lookups. A LEFT JOIN does the same work in a single pass. Cartesian JOINs are the most dangerous. A JOIN without an ON clause multiplies every row in table A by every row in table B. On production tables with millions of rows, this can crash your database server. QueryTuner marks these as critical severity — the only finding type at that level. The heuristic engine runs in under 200 milliseconds. It always runs, regardless of whether the LLM layer is enabled. This was a deliberate design choice. I wanted the tool to be useful even when the AI component is unavailable. The LLM Layer After the heuristics run, users can optionally enable an LLM layer — HuggingFace or OpenAI. The LLM adds plain-English narrative, a rewritten query using CTEs, and flags for assumptions it can't verify without knowing the actual schema. The key design principle here: the LLM is additive. If it fails — cold start on the free tier, rate limit, network timeout — the user still gets complete structured findings from the heuristic layer. The tool does not degrade to an empty screen when AI is unavailable. The Dialect Problem This was the hardest part to get right. SQL is not one language. The correct way to create an index in production differs significantly across databases. In PostgreSQL, you use CREATE INDEX CONCURRENTLY to avoid locking the table during index creation. Without CONCURRENTLY, all writes block until the index is built. On a busy production table, that can mean minutes of downtime. In MySQL, the idiomatic form is ALTER TABLE orders ADD INDEX idx_name (column). The CREATE INDEX syntax also works, but ALTER TABLE integrates better with InnoDB's internal operations. In Oracle, you add NOLOGGING to skip the redo log during index creation. This makes it significantly faster, but you can't recover the index from redo logs if something fails mid-creation. Use it during maintenance windows only. In SQL Server, CREATE NONCLUSTERED INDEX ... WITH (ONLINE=ON) allows reads and writes to continue during index creation. This is an Enterprise edition feature. FILLFACTOR=90 leaves 10% of each page free for future inserts, reducing page splits over time. In SQLite, there's no concurrent DDL. Index creation locks the entire database file. The only mitigation is scheduling it during low-traffic windows. Generic advice — "add an index on customer_id" — is not enough. The statement a developer runs in production depends entirely on which database they're on. Getting this wrong can cause downtime. I solved this by centralizing all dialect-specific logic in a single file: dialect_config.py. This is a dataclass-based config with one entry per database. Each entry holds the index DDL template, optimizer rewrite syntax, LLM system prompt context, and maintenance commands for that dialect. When the tool generates a recommendation, it calls get_dialect(db_type) and gets everything it needs from one place. The practical benefit: adding a sixth dialect means adding one dataclass entry. No other files change. Schema-Aware Confirmed Recommendations By default, every index recommendation carries a confirmed: false flag. The tool is analyzing SQL syntax, not your actual database. It doesn't know whether the column exists, whether an index already covers it, or what the real table name behind an alias is. If you paste your CREATE TABLE statements alongside the query, that changes. QueryTuner parses the DDL, builds a schema map, and cross-references every detected column against it. If the column exists and no index covers it, the recommendation flips to confirmed: true. The DDL it generates uses your real table name — not a placeholder like <o_table>. Suggestions for indexes that already exist in your DDL are suppressed entirely. For a developer who is about to run a CREATE INDEX on a production database, that distinction matters. confirmed: true means the recommendation was verified against their actual schema. confirmed: false means it's a pattern-based estimate worth investigating. What I'd Do Differently The alias resolution logic — matching o to orders — is the weakest part of the system. It works for common patterns (single-letter aliases, prefix matches) but fails for arbitrary aliases. This is the first thing I'd improve with more time. The LATERAL join gap is the other known limitation. Correlated columns inside LATERAL joins are not detected. It's documented as an intentional xfail in the test suite and will be addressed when the execution plan parsing layer is built. Try It QueryTuner is open source under the MIT license. Live: querytuner.comSource: github.com/AutoShiftOps/querytunerAPI: POST /analyze — accepts query, dialect, optional schema DDL Feedback is especially welcome from Oracle and SQL Server practitioners. Those are the dialects with the least real-world battle-testing, and the production edge cases are where the tool needs the most work.

Key Takeaways In regulated industries, cloud migration success is determined less by technology selection and more by how deliberately you decouple risk vectors — compliance risk, organizational hesitation, user adoption gaps, and integration changes — so no single failure can derail the whole program.You can successfully migrate an application to AWS while keeping data on-premises by routing through a REST API abstraction (e.g., IBM’s DB2 REST API layer) paired with dedicated AWS security groups controlling cloud-to-on-prem traffic, allowing the data migration to proceed on its own compliance and trust-building timeline.The most dangerous compliance gap in regulated applications isn’t declared sensitive fields — it’s free-form text fields where users may inadvertently type SSNs, credit cards, or other regulated identifiers; proactive tokenization in the application’s write path closes this gap before any audit finds it.Long-tenured business users carry a decade of UX muscle memory that QA testing cannot replicate; allocating real production validation time (such as a 15-day dark deployment cohort) is essential when migrating systems users have relied on daily for 10+ years.Before starting a regulated cloud migration, ask which risk vector each architectural decision is decoupling and whether your team is aligned on why — this single question reframes "cloud migration" from a technology project into a coordinated risk-management exercise. Introduction Most published writing on legacy-to-cloud migration treats it as a technical exercise: pick the stack, plan the cutover, flip the switch. In regulated industries, that framing fails — and the failure mode isn’t a missed deployment window. It’s a stalled program, a failed compliance audit, or a client who pulls back from the cloud strategy entirely. A cloud migration in healthcare insurance is as much about regulatory risk management, organizational trust-building, and user adoption as it is about microservices and Fargate. Get the technology right and miss the risk choreography, and the project doesn’t ship. I led the first WebSphere-to-AWS migration in the health division of a Fortune 50 insurer — a multi-year program touching PHI data, long-tenured business partners, and downstream services concurrently migrating to the cloud. Over that program, six architectural patterns emerged as decisive. Not for the technology they enabled, but for the risks they made manageable. None are individually novel. What’s distinctive is how they work together — as a coordinated set of risk-decoupling decisions in a first-of-its-kind regulated cloud migration. Pattern 1: Strangler Fig With Dark Deployment When migrating critical production systems to the cloud, the temptation is a hard cutover — flip the switch at 2 AM on a Sunday and hope for the best. We chose a different path: a 15-day dark deployment on AWS production, accessible only to a designated cohort of business partners. Three factors drove this decision. 1. First-mover risk in the department. This was the first WAS-to-AWS migration in this Fortune 50 insurer’s health division. There was no internal precedent to draw from — no playbook, no lessons learned from a prior AWS rollout. A "big bang" cutover would have exposed our full user base to whatever unknowns we hadn’t anticipated. Dark deployment let us pioneer the path with limited blast radius. 2. Regulatory exposure on PHI data. The application processes Protected Health Information. Any data integrity issue — a missed field, a misformatted record, a sync gap — could have triggered regulatory scrutiny. By exposing the new AWS environment to a small group of business partners first, we could validate end-to-end data flow in real production conditions without putting the full user base or compliance posture at risk. 3. UX learning curve. We had explicitly rejected a lift-and-shift approach. The new application wasn’t just re-hosted — the UI had been redesigned, the APIs restructured, and user workflows updated. Even excellent technical execution couldn’t eliminate the learning curve our users would face. Dark deployment gave us 15 days of real-world UX observation: where do users hesitate, what do they misunderstand, which workflows feel awkward? By the time we cut over publicly, we had already addressed the rough edges. The result: When we replaced the WAS production URL with the AWS production URL, end users perceived the change as a routine UI update, not a foundational technology migration. Pattern 2: Decouple Application Migration From Data Migration The default assumption in cloud migration is that application and data should move together. We made the opposite choice: migrate the application to AWS while keeping the underlying DB2 data on-premises. Three factors made this the right call. 1. PHI/HIPAA compliance complexity. The application processes Protected Health Information governed by HIPAA. Moving regulated healthcare data to a new environment raises a long list of compliance questions — encryption-at-rest configurations, audit logging, access control policies, business associate agreements with the cloud provider, breach notification readiness. None of these are insurmountable, but they take months of compliance review. Treating data migration as a separate workstream with its own compliance approval cycle was significantly less risky than bundling it into the application cutover. 2. Client comfort and trust-building. Cloud migration is as much a psychological transition for the client as a technical one. Moving an application to AWS is one decision; moving sensitive data off the client’s own infrastructure is a much larger one — it changes their security perimeter, their incident response posture, and in some cases their regulatory filings. Insisting on moving both at once would have either delayed the program waiting for full executive comfort, or risked a "no" on the entire initiative. Application-first let us demonstrate the new architecture working successfully before the data migration conversation began. 3. Parallel team enablement. Decoupling created room for a separate analytics team to independently assess which data could move to the cloud, on what timeline, and under what compliance framework. The application architecture was designed from day one to support a hybrid future — partial data on AWS, other data on-prem — so the analytics team’s work didn’t block application progress. How the technical decoupling works. The natural temptation when keeping data on-prem is to expose a direct database connection from the AWS application back to the on-prem DB2 instance. We rejected that — opening database ports across the cloud-to-on-prem boundary is a security liability, a latency problem, and a fragile dependency. Instead, we used IBM’s DB2 REST API layer to expose data access through authenticated HTTPS-based service calls. The AWS application talks to data through an API, not a database connection. This abstraction also positions the application to seamlessly switch to AWS-resident data later, without any application code change — only the API endpoint moves. Network-layer security follows the same decoupling principle. We provisioned dedicated AWS security groups on the Fargate side specifically for the IMS and DB2 connections back to the on-premises environment — only requests from those approved security groups can traverse the firewall to the on-prem data tier. Combined with the REST API abstraction, this gives us both application-layer (authenticated HTTPS) and network-layer (security-group-controlled) protection across the cloud-to-on-prem boundary. The result: A successful cloud migration with regulatory exposure isolated to a single workstream, and a forward path that doesn’t force the client into uncomfortable decisions before they’re ready. Pattern 3: EJB Monolith → Containerized Microservices on Fargate The original application was a Java EJB monolith running on WebSphere. The "lift-and-shift" temptation would have been to containerize the existing EJB code as-is into AWS Fargate — preserving the architecture, just moving the deployment substrate. We rejected that and instead decomposed the monolith into bounded REST microservices. Three reasons drove this decision. 1. Downstream services were also migrating. The application integrated with 5–7 SOAP-based services owned by adjacent teams — agreement service, customer service, sensitive data masking, and others. Those teams were simultaneously migrating their own services from WAS to AWS, which meant interface contracts, protocols, and endpoints would inevitably change. Inside an EJB monolith, every downstream integration change forces a recompile-redeploy-retest cycle of the entire application. Inside microservices, only the integration adapter for the affected service needs to change. With multiple active migration interfaces, the flexibility difference compounds quickly. 2. EJB development velocity is structurally slow. Even routine changes to EJB code require a full WAR/EAR build, redeployment to the WAS instance, and a heavy test cycle. The technology wasn’t designed for the iteration speed we needed to support a multi-year migration alongside actively changing downstream dependencies. Microservices on Fargate gave us a development model — fast container builds, independent deployments, isolated test environments — that matched the pace of the work. 3. Future data migration optionality. As noted in Pattern 2, the underlying data was kept on-premises for now, but a phased data migration to AWS was planned. By isolating database calls and IMS calls into dedicated microservices, the change required when the data eventually moves is localized — swap one service’s data access logic rather than reworking the monolith. The architecture is positioned for the data move whenever the client is ready. How we sized the decomposition. The boundaries followed natural integration points: each external SOAP integration became its own bounded microservice with a thin REST API. Data access calls (DB2 via REST, IMS) were isolated into dedicated services. The frontend talks to a coordination layer that orchestrates calls across these services. The result was a clean set of containerized microservices on AWS Fargate — each independently deployable, scalable, and testable. The result: A modernization that didn’t just relocate the code, but restructured it to absorb the inevitable changes coming from adjacent migrations across the organization — without recompile-redeploy-retest pain. Pattern 4: Frontend Decoupling via S3 + CloudFront The original WAS application followed the classic tightly-coupled pattern: JSP pages rendered server-side, deployed alongside the backend, scaling and updating as one unit. We made an architectural break in the migration — the frontend became a fully independent single-page React application hosted on Amazon S3 and served via CloudFront. Three factors made this the right call. 1. Independent deployment cadence. Frontend and backend evolve at different speeds. UI tweaks — copy changes, validation logic, visual updates — are frequent and low-risk. Backend API changes are slower and require careful coordination with downstream service migrations. Decoupling them means UI changes can be deployed instantly through a separate UI pipeline (different Git repository, different infrastructure, different release cadence) without touching the backend microservices. A small label change no longer requires a full backend deployment. 2. Adopting an accessibility-first enterprise UI library. Alongside our migration, an internal innovation track was building a shared component library to unify UX patterns across the organization’s applications — consistent typography, controls, brand elements, and critically, accessibility as a first-class concern: full screen reader support, keyboard navigation, sufficient color contrast, and ARIA-compliant semantics. JSP-based legacy pages couldn’t meaningfully integrate this kind of library. By rebuilding the frontend as a React single-page application, we adopted the library fully — and incorporated rigorous accessibility testing into every release cycle. Users who rely on assistive technologies (screen readers, alternative input devices, magnification) get full application access. For an application processing PHI in a regulated industry, this proactive accessibility-first approach is itself a substantial improvement over the legacy app. 3. Global performance through edge caching. S3 alone would have served the static assets, but we layered CloudFront on top to push content to edge locations closer to users. Business partners access the application from different geographic regions; CloudFront cuts load times by serving cached assets from the nearest edge, not the S3 origin in a single AWS region. This is a substantial UX improvement that simply wasn’t possible with WAS-hosted JSPs. How the architecture flows. User requests hit CloudFront, which serves cached React bundles, HTML shells, and static assets from the nearest edge. The React application then makes authenticated REST API calls back to the backend microservices on AWS Fargate. The frontend has no awareness of which microservice serves any particular request — it talks to a coordination API layer that handles orchestration. The result: A UI architecture that’s faster (edge-cached), cheaper (no application servers for the frontend), easier to update (independent pipeline), more inclusive (accessibility-first), and aligned with the broader enterprise UX modernization effort. Pattern 5: Business Partner Real-Production Validation Cohort Pattern 1 described the deployment mechanism — a 15-day dark deployment exposing AWS production to a limited cohort. Pattern 5 is about who was in that cohort and why we deliberately chose real business partners over our QA team for production validation. Two factors shaped this decision. 1. Decades of muscle memory in the existing UX. Our business partners — long-tenured users of the application — had been using the legacy UI for 10–15 years. They knew every workflow, every shortcut, every quirk. The new React application introduced not just a new visual style but new patterns from the organization’s modern component library. Even with rigorous accessibility and usability testing in QA, a brand-new UI in front of users with a decade of habits guaranteed friction. The 15-day validation cycle gave those users time to acclimate to the new patterns and surface UX issues that only show up at the speed of real daily work — keyboard shortcuts they used unconsciously, screens they navigated to multiple times an hour, validation logic that affected their flow. QA testers, by definition, don’t have that muscle memory. 2. First-of-its-kind migration with concurrent change. This was the first WAS-to-AWS migration in the health division, and we’d simultaneously re-architected the UI, the API layer, and incorporated changes from downstream services that were also mid-migration. With that many concurrent changes, even thorough QA can’t realistically simulate the full combinatorial space of real production usage — real customer data, real edge cases, real integration timing, real load patterns. Putting real business partners on the actual AWS production environment for 15 days was our safety net: anything QA missed, the cohort would surface, and we could fix it before broad cutover. Beyond the cohort: maturing the delivery pipeline. A secondary benefit of running an extended validation window was that it gave the engineering team time to mature the CI/CD pipeline alongside the application. By the second application in the migration program, we’d evolved the cohort approach into a full blue/green deployment model on AWS — building organizational learning alongside the application portfolio. The validation pattern isn’t static; it strengthens with each subsequent migration. The result: a validation approach that combined deep domain familiarity (real business partners) with controlled exposure (limited cohort, real production) — catching the issues QA can’t, well before public cutover. Pattern 6: Defensive Tokenization for Sensitive Data in Free-Form Fields In regulated industries, the obvious sensitive data — SSN fields, credit card fields, account number fields — gets protected automatically. The dangerous category is the unstructured data: a free-form text field where a user can type anything. In our application, users entered "health notes" — narrative text describing customer interactions. The risk: nothing in the application schema prevents a user from typing an SSN, a credit card number, a driver’s license, or other regulated identifiers directly into that note. Once stored, that PHI/PII data is sitting in a free-text column with no encryption-at-rest tailored to it, no masking on display, no controlled access — and our compliance posture changes accordingly. We addressed this proactively by integrating an internal sensitive-data-masking service into the application’s write path. Before any free-form text reaches the data layer, the masking service scans the input, identifies regulated identifiers (SSN-pattern strings, credit card numbers via Luhn check, driver’s license formats), and applies tokenization — replacing the identifier with a non-reversible token or masked representation. The original value never lands in the database in plaintext. Three things made this a deliberate architectural pattern, not an afterthought: 1. It was incorporated before the formal risk assessment, not in response to it. Risk assessment was a new exercise for the team — none of us had been through one for AWS-hosted PHI before. Rather than wait for the assessment to flag the free-form field as a finding, we performed our own data classification first, identified the free-form notes as a regulated-data risk vector, and integrated the masking service pre-emptively. When the formal risk assessment ran, this control was already in place. 2. We reused an existing internal service, not built a new one. The masking service already existed in another WAS-hosted application within the broader life/health portfolio. Instead of re-implementing tokenization logic, we adopted the existing service — saving development time and inheriting the existing security review and operational maturity of that service. Migrations are a good moment to identify reusable internal capabilities rather than reinvent them. 3. It addresses a class of risk most compliance reviews don’t anticipate. Compliance checklists focus on declared sensitive fields ("the SSN field," "the account number field"). They rarely interrogate free-form text fields, because those fields aren’t supposed to hold sensitive data. But in practice, users type whatever they need to type — and what they type is what your application stores. Proactive defensive tokenization closes that gap. The result: free-form notes that look normal to users, but whose backend storage is sanitized of any regulated identifiers the user may inadvertently include. The application’s compliance posture is robust to user behavior, not just to user intent. Conclusion: The Through-Line Is Decoupling Looking back across the six patterns, the through-line isn’t any specific technology — it’s a posture: deliberate decoupling of risk vectors so that no single failure, regulatory finding, organizational hesitation, or user adoption gap can derail the whole migration. Pattern 1 (Strangler Fig with Dark Deployment) decouples cutover risk from broader rollout.Pattern 2 (Decouple App from Data) decouples application migration from the data-and-compliance timeline.Pattern 3 (EJB → Microservices) decouples downstream integration changes from our own deployment cadence.Pattern 4 (Frontend on S3/CloudFront) decouples UI release cadence from backend release cadence.Pattern 5 (Business Partner Validation Cohort) decouples real-world UX surprises from public rollout.Pattern 6 (Defensive Tokenization) decouples user behavior risk from data-layer compliance posture. None of these patterns are individually novel. What’s distinctive is choosing them together, as a coordinated set of risk-decoupling decisions in a first-of-its-kind regulated cloud migration. The result was a migration that didn’t surprise our compliance team, didn’t surprise our users, and didn’t surprise our auditors — which, in a regulated industry, is the kind of unsexy outcome that defines success. If you’re starting a similar program, the question isn’t which of these patterns to adopt. It’s: which risk vector are you decoupling, and is your team aligned on why?

Learn how attackers enumerated Salesforce Experience Cloud and ServiceNow portals — and how defenders can detect and prevent the same abuse. When Guest Access Becomes an Attack Surface Modern enterprise portals increasingly expose APIs to unauthenticated users. The problem is not necessarily that those APIs are vulnerable. The problem is that the anonymous identity behind them may have been granted more access than the organization realizes. By now, the existence of the campaign covered in this piece isn't news. SecurityWeek, BleepingComputer, Dark Reading, and Help Net Security have all reported on it in the last few days, drawing on research published by SaaS security firm Reco. What none of that coverage had room for is the protocol-level mechanics: exactly how the enumeration works against Salesforce's two different component frameworks, exactly where ServiceNow's authorization decision actually lives, and exactly what a defender should pull from logs to tell this apart from ordinary traffic. That's the gap this article fills. In an interview arranged through Reco, I spoke with security researcher Nitay Bachrach — one of the researchers behind the original investigation — about how his team built that distinction, endpoint by endpoint. What follows combines his answers with Reco's published indicators and current Salesforce and ServiceNow platform documentation. What the City-Forum Campaign Actually Found Reco calls the activity the City-Forum campaign, after a domain tied to the operator's infrastructure. A single source has been interacting with Salesforce Experience Cloud and ServiceNow Service Portal deployments through guest-accessible interfaces since at least March 2025 — over seventeen months of continuous activity, still climbing in volume as of Reco's publication. On Salesforce, the activity spans Aura enumeration, LWR UI-API and GraphQL requests, and self-registration probing. On ServiceNow, the same infrastructure repeatedly targets the native Service Portal search endpoint. Targets span telecommunications, banking and financial services, enterprise software vendors — including security and data-privacy companies — and public-sector portals; Reco has not named individual organizations. Critically, Reco is explicit that none of this exploits a platform vulnerability. Every record retrieved was something a site owner had already exposed to anonymous users, through sharing rules, permissions, or portal search-source configuration. One Infrastructure Source, Two Enterprise Platforms Everything traces to a single IP address: 158.220.87.79, on a Contabo VPS (ASN 51167, Germany). Passive DNS ties that IP to the domain city-forum.com, registered in 2002 and long abandoned before being repurposed for this infrastructure, resolving to the operator's server since at least March 12, 2025. That's an unusually long, unrotated run for this kind of activity. Campaigns like the previously reported ShinyHunters Experience Cloud campaign have typically drawn on multiple machines and rotating IP ranges. This one hasn't — the same box has carried the same domain for the entire observed window. Verifiable indicators, independently confirmable via dig: IP: 158.220.87.79 — ASN 51167 (Contabo GmbH), reverse DNS vmi2213719.contaboserver.netDomain: city-forum.com and active subdomains www.city-forum.com, server.city-forum.com, www.server.city-forum.com, mail.city-forum.com, www.mail.city-forum.comAn SPF record explicitly authorizing the IP to send mail as the domain Reco's own guidance is worth repeating for anyone hunting this: resolve the domain rather than browsing to it. There's no legitimate reason to load attacker-adjacent infrastructure in a browser. Every request across both platforms carries the same user-agent: Go-http-client/1.1, Go's default net/http string. On its own, that identifies a client library, not a threat actor — as Bachrach put it, "it doesn't say much, except that they wrote their tools in Golang. Go is one of the two 'go-to' languages hackers use for their toolset — the other one being Python." What makes it meaningful is context: Experience Cloud sites and ServiceNow portals are built to be driven by browsers. A guest session arriving via Go-http-client is unusual enough to warrant investigation. Salesforce Aura: Enumerating the Guest Context Every Experience Cloud site has a persistent Guest User — a real identity that unauthenticated visitors execute as. It cannot be deleted, and requiring login on the site doesn't remove the underlying profile, its sharing rules, or any code running in its context. Whatever the guest identity is authorized to read may be reachable by an unauthenticated internet caller. Aura, Salesforce's older Experience Cloud framework, has a single endpoint — /aura (also /s/sfsites/aura) — that accepts a POST containing a descriptor and parameters. Reco observed high-volume guest requests against two actions: HostConfigController/ACTION$getConfigData — enumerates the objects reachable from the guest context (Account, Contact, Case, Lead, and so on).SelectableListDataProviderController/ACTION$getItems — pages through records for each object surfaced by the first call. One target generated more than 560,000 events from the campaign IP across the observation window, almost entirely attributable to guest Aura enumeration via these two actions. At that volume, the activity is consistent with systematic enumeration and potential large-scale extraction rather than ordinary application use. LWR and GraphQL: The Surface Aura Tooling Misses Lightning Web Runtime is Salesforce's newer Experience Cloud framework, and its /aura endpoint is disabled entirely. Tooling built to detect Aura enumeration — which describes most public and open-source Experience Cloud scanners — finds nothing on a pure LWR site. Not because the site is safer. Because the tooling wasn't built to look at the surface LWR actually exposes. That surface is the UI-API, under /webruntime/api/services/data/{version}/, backing both REST and GraphQL. Guest access to the entire surface is governed by one Experience Builder preference — "Allow guest users to access public APIs" — distinct from both the guest profile's "API Enabled" permission and the site's general login-required visibility toggle. Confusing these three is a common misconfiguration; disabling the wrong one leaves the UI-API fully reachable while an admin believes the site is locked down. The chain: Plain Text Guest User → LWR site → /webruntime/api/services/data/{version}/ → GraphQL or REST UI-API → Object / Field-Level Security / Sharing Rules → Returned records Reco observed guest POST requests to /webruntime/api/services/data/vNN.0/graphql, with the operator's tool stepping through consecutive API versions — v56.0 through v66.0 — against every LWR site it discovered. A representative schema-enumeration query: Plain Text query { uiapi { query { EntityDefinition(first: 2000) { edges { node { QualifiedApiName { value } KeyPrefix { value } } } } } } } That returns every object name the guest context can query — the LWR equivalent of Aura's object map, but more complete. Record queries then follow the same authorization model as Aura: object permissions, field-level security, and sharing rules on the guest profile determine what comes back. Salesforce's own GraphQL documentation confirms this directly: queries are evaluated against the object- and field-level permissions of the executing user, which for a guest session means the guest profile. Proportionally, LWR traffic was lighter than the Aura flood — a handful of requests per version per subsite. Reco reads this as the operator treating LWR as a secondary technique, consistent with Aura sites still being more common across Experience Cloud generally. How to Distinguish Automation From Legitimate API Traffic I asked Bachrach how Reco distinguished this from a legitimate, if unusual, frontend implementation calling the UI-API directly. His answer is a detection principle worth generalizing: individual indicators are weak alone, but decisive in combination. First, GraphQL activity from a guest user is unusual to begin with — a frontend component could in theory call it directly, but it's rare enough to warrant a second look on its own. Second, the requests carried Go-http-client/1.1 throughout, never a browser string, across the entire campaign window. Third, the request stream lacked everything a browser normally generates alongside API calls — HTML page loads, JavaScript asset retrieval, the general traffic a human session produces. Fourth — what Bachrach called the "final nail" — the operator systematically walked API versions from v56.0 through v66.0, a sequence no legitimate client has a reason to produce. Individually, each observation is explainable in isolation. Together, on the same source, against the same endpoint, they leave little room for an innocent explanation. That's the model worth adopting for your own detection engineering: correlate client fingerprint, endpoint sensitivity, request sequence, and surrounding traffic pattern — don't let any single one carry the conclusion. Self-Registration as a Second-Stage Opportunity Alongside enumeration, the tool appended /SiteRegister and /CommunitiesSelfReg to nearly every Experience Cloud path it discovered — consistently, across most Salesforce targets, which is what makes it a deliberate part of the methodology rather than incidental noise. The objective: determine whether self-registration is enabled. If it is, an anonymous guest can promote itself into an authenticated external user, and external users routinely see meaningfully more than the guest profile does. The relevant defensive question isn't only whether self-registration exists — it's what a successfully registered identity actually gains. If registration unlocks additional records, search sources, files, or workflow access, the registration flow is part of the attack surface, not a separate concern. ServiceNow's Hidden Search Surface The second major surface is ServiceNow's Service Portal. The operator's tool first loads the portal landing page — GET /$sp.do?...&id=landing — then concentrates nearly all remaining volume against one endpoint: HTML POST /api/now/sp/search?sysparm_cancelable=true This is native platform Java. It doesn't appear in any customization table, isn't visible in Studio, and ServiceNow publishes no API reference for it. It is, however, exactly what the stock Service Portal typeahead widget calls. Reco reverse-engineered the request shape from that widget's client controller: JSON POST /api/now/sp/search?sysparm_cancelable=true Content-Type: application/json { "query": "password", "portal": "sp", "page": "homepage", "source": ["kb", "sc"], "include_facets": false, "searchType": "typeahead", "count": 5 } The source field determines which search sources are invoked and is required — omit it, and the endpoint returns zero results with no error explaining why. I asked Bachrach what initially drew Reco's attention to an endpoint this undocumented. The trigger was correlation, not the endpoint in isolation: "After discovering the Salesforce attack, we checked that IP and its activity. Seeing the same IP hammering a specific ServiceNow API was interesting, and we knew we had to investigate it." As with LWR, the endpoint can be used entirely legitimately in a normal browser session; the user-agent is what separated this traffic from that baseline. Why HTTP 201 Is Not an Access-Control Signal This is the finding I'd flag as most operationally important for ServiceNow admins. The endpoint does not gate on authentication at the transport layer. An authenticated request and a fully anonymous one both return HTTP 201. What differs is the response body and two headers — X-Is-Logged-In and X-Is-Visitor — not the status code — a distinction Reco's own captures, shown below, make directly. An authenticated request against a readable catalog source returns real results: JSON { "result": { "results": [ { "name": "Password Reset", "type": "sc", "table": "sc_cat_item", "sys_id": "29a39e830a0a0b27007d1e200ad52253", "short_description": "Request a reset of a password for a service or an application." } ], "total_number_results": 3 } } The identical request with no Authorization header and no session cookie also returns 201, with X-Is-Logged-In: false and X-Is-Visitor: false, and an empty result set: JSON { "result": { "results": [], "additionalResults": [], "facets": {}, "$$uiNotification": [], "total_number_results": 0 } } I asked Bachrach whether any telemetry resolves the resulting ambiguity — response time, payload size, anything deterministic separating "nothing matched" from "you were blocked." He was direct about the limit: "there's no deterministic way to conclude that except for checking the configuration of that instance or, better yet, running it yourself on that endpoint." The empty 201 is genuinely uninformative in both directions. To an operator sweeping the endpoint with varying query terms, an access-denied empty result and a genuinely-no-matches empty result look identical — so they learn what's exposed by watching which queries eventually come back non-empty. To a defender watching status codes alone, a portal returning 201 all day to anonymous callers looks the same whether it's leaking data or fully locked down. Where ServiceNow Authorization Actually Happens The access decision lives entirely behind the endpoint, in the search sources wired to a portal. Three tables matter: sp_portal – the Service Portals themselves; note which are reachable without login.m2m_sp_portal_search_source – the join between a portal and the search sources it actually exposes.sp_search_source – the source definitions, either table-backed or scripted (is_scripted_source). ServiceNow's current documentation confirms this architecture directly: search sources can be configured against tables or built with custom data-fetch scripts, and administrators can apply user criteria to control who is permitted to view a given search source. Reco's comparison of two stock sources illustrates the range of outcomes. The Catalog source (sc) opens with an unambiguous, code-level gate, then re-checks per item: JavaScript var results = []; if (!gs.isLoggedIn()) return results; // ... then, per candidate item: if (catalog_item.canViewOnSearch()) { /* include */ } The Knowledge Base source (kb) has no equivalent gs.isLoggedIn() check anywhere in its script. It calls directly into new KBPortalServiceImpl().getResultData(request), and the only control between an anonymous request and KB content is whatever "Can Read" user criteria are attached to that knowledge base — a data configuration decision, not a code-level gate, and the script gives no indication either way of whether that configuration is safe. The specific pattern Reco recommends hunting for in user_criteria: any record that is active = true, advanced = false, with every scoping field — role, user, group, company, department, location — left empty. That combination resolves to true for the guest identity exactly as if public access had been explicitly granted. The built-in Any User and Any user for KB seed records that ship on every instance, with the same fixed sys_id values across deployments, are precisely this pattern. One caveat from Reco's methodology: a criteria record with advanced = true and empty scoping fields is governed by its script rather than unconstrained, and shouldn't be flagged on the empty-fields heuristic alone. Correlating Activity Across Platforms I asked Bachrach how confidently Reco could tie Aura activity, LWR activity, and ServiceNow activity to a single operator and toolset. His answer was direct: "This one was actually very easy in this case — they all originated from the same IP, a VPS, which had no legitimate activity." That's the basis for treating this as one operation rather than three unrelated anomalies: one Go binary, from one box, hitting Salesforce over two distinct frameworks and ServiceNow over a third native endpoint. Public and open-source scanning tools — AuraInspector, S-RET, CirrusGo, including the modified AuraInspector variant used in the earlier ShinyHunters campaign — don't touch webruntime at all. Whoever built this evidently researched both platforms' guest-access surfaces independently rather than adapting an existing public tool. What the Evidence Says About Attribution Reco is explicit that it doesn't know who is behind this campaign and isn't ruling anyone in or out — a position echoed in the broader reporting on the campaign as well.[^1] That restraint is worth preserving rather than reading more into the pattern than the evidence supports. On the surface, the activity resembles the previously reported ShinyHunters Experience Cloud campaign — guest enumeration of Salesforce over Aura and GraphQL. It also diverges: this operator built custom tooling rather than running a modified public scanner, and ShinyHunters has not been publicly linked to ServiceNow targeting. The Contabo infrastructure itself is generic commodity hosting, tied to no named group and absent from public threat feeds. Neither similarity nor divergence settles the question. A campaign that doesn't match a group's last observed fingerprint tells you nothing on its own — actors rewrite tooling and rent new infrastructure constantly. Reasoning from "this doesn't resemble their previous campaign" to "this must be a different actor" is a common way confident, wrong attribution gets made. One operational detail is worth noting as a soft signal, not an attribution claim: this campaign's infrastructure hasn't rotated once across the entire seventeen-month window, a different pattern from the multi-machine, rotating-range approach typically reported for other groups. Passive scanning of the box shows only SSH and a CUPS print-sharing service — no web panel, nothing dashboard-like, consistent with the box functioning purely as a scanner. Its SSH build has sat unpatched across the observation window, roughly a year and a half behind current. That's poor hygiene on infrastructure the operator evidently isn't worried about protecting, though it says little about skill either way — there's limited reason to harden a box intended to eventually be burned. Building Detections From Behavior, Not IOCs No single indicator in this campaign is sufficient, and building detection around one — an IP, a domain, a user-agent string — is fragile by design. The IP can be replaced. The domain can change. The user-agent is one line of code away from a browser string. What's harder to hide is the underlying behavior pattern. Signals worth correlating, drawn directly from this campaign's request patterns: Guest identity combined with GraphQL access on SalesforceGuest identity combined with any /webruntime/api/services/data/ trafficNon-browser client fingerprints against /aura, the UI-API, or /api/now/sp/searchSequential API-version probing across consecutive vNN.0 valuesHigh-volume getItems/getConfigData activity from a single guest sessionRepeated /SiteRegister or /CommunitiesSelfReg probing across many subsitesGuest-attributed POST /api/now/sp/search activity at a cadence inconsistent with human typeahead behaviorRows in syslog_transaction where Created by is guest against /api/now/sp/search, grouped and trended over time For Salesforce, this requires Event Monitoring (Shield or the standalone add-on) to pull AuraRequest and Sites event log files: SQL SELECT Id, LogDate, Interval, LogFile, LogFileLength FROM EventLogFile WHERE EventType IN ('AuraRequest', 'Sites') Within those logs, the columns that matter are USER_AGENT, CLIENT_IP, ACTION_MESSAGE on AuraRequest rows, and the request URI on Sites rows — any guest URI containing /webruntime/api/services/data/v is the LWR tell that detection built around Aura alone will miss entirely. For ServiceNow, the relevant data lives in syslog_transaction. Filtering on IP Address is 158.220.87.79 and URL starts with /api/now/sp/search, combined with AND or OR depending on whether you're isolating this actor or surveying all guest traffic against the endpoint, surfaces the pattern directly. Created by reading guest, Type as REST, and request volume climbing from tens per day into the hundreds are the markers Reco's investigation used. Output length is a useful secondary signal — rows returning meaningfully more than the empty-result baseline are the searches that returned content, worth investigating first. The One-Hour Exposure Assessment I asked Bachrach what he'd check first with limited time and nothing else to go on. Salesforce: Pull every guest-user sharing rule, list them, and check the conditions on each individually. Justify each one on its own merits, and assume by default that any share makes the underlying data public — even on a site believed to be configured securely. ServiceNow: Review Knowledge Base user criteria and scripted search sources specifically. Confirm every scripted source gates on gs.isLoggedIn() before touching data and uses GlideRecordSecure rather than a bare GlideRecord, and check whether any unscoped "Any User"-pattern criteria record is attached to a knowledge base that shouldn't be public. Neither check requires reproducing the campaign's traffic. Both require someone actually reading configuration that, in most organizations, hasn't been reviewed since the site or portal went live. As Bachrach told Dark Reading separately, "seeing an indicator does not mean sensitive data was stolen... that being said, whether it shows up or not, it's crucial to audit the environment." Remediation Salesforce. Work the guest profile down to least privilege: audit and strip guest sharing rules to the minimum the site genuinely needs to serve to anonymous visitors; remove object- and field-level access on anything the site doesn't render publicly; remove "Access Activities" from the guest profile; disable self-registration unless the site requires it; disable guest file access and member visibility. On LWR specifically, disable "Allow guest users to access public APIs" under Experience Builder → Workspaces → Administration → Preferences — a single toggle that closes both GraphQL and REST UI-API access at once, distinct from the guest's "API Enabled" permission (also worth disabling, but insufficient alone) and from the site's login-required visibility setting (which governs page access, not API access). ServiceNow. Map every guest-facing portal in sp_portal to its search sources via m2m_sp_portal_search_source, and detach anything a public portal doesn't need. For every remaining scripted source, read the actual data_fetch_script: confirm it gates on login state and uses GlideRecordSecure. For table-backed sources, check source_table, condition, and roles — a source pointing at a sensitive table with no role requirement is directly reachable by the guest. Audit kb_uc_can_read_mtom for unscoped grants, and when found, detach the specific join record rather than editing the shared user_criteria record — that record is reused across the instance, and direct edits carry blast radius well beyond the one knowledge base being fixed. What AI Agents Change I asked Bachrach whether the growing use of AI agents against Salesforce, ServiceNow, MCP servers, CI/CD systems, and internal workflows could turn these guest-accessible surfaces into an indirect attack path for autonomous systems never intended to go looking for exposed data. "This is almost guaranteed," he said. "AI agents often try anything they can. They see a Salesforce site or a ServiceNow portal — they will try to scan it using the relevant tools or methods." That's an expert assessment of emerging risk, not a claim that agents are currently exploiting this specific campaign's exposure — worth being precise about. An agent given a browsing tool, an HTTP client, and a task doesn't inherently understand an organization's intended boundary between "guest" and "authenticated" — it understands what a given request returns. The same access model becomes more significant as organizations deploy autonomous agents capable of discovering and interacting with enterprise applications on their own initiative, without a human deciding in advance which endpoints are safe to query. That's a meaningful shift in the threat model, even though it's forward-looking rather than something this campaign's evidence directly demonstrates. A guest misconfiguration that today requires a deliberately built Go tool and seventeen months of patient infrastructure could, going forward, be discovered incidentally by an agent doing something entirely unrelated to reconnaissance. Conclusion Nothing in the City-Forum campaign broke either platform. Every request behaved exactly as Salesforce's and ServiceNow's own documentation describes — GraphQL and UI-API calls evaluated against the executing user's object and field permissions, search sources returning whatever their configured user criteria allow. That's precisely what makes the finding worth taking seriously rather than filing away as a routine scanning report. The question defenders need to keep asking isn't "is this endpoint vulnerable?" It's "what is the guest identity behind this endpoint actually authorized to do, as configured today" — and that answer needs to be re-verified on a schedule, not assumed once at launch and left alone. An attacker with a single Go binary and over a year of undisturbed infrastructure found the answer to that question across a wide range of organizations before those organizations found it themselves. As guest-accessible interfaces become a surface that autonomous agents may reach independently, closing that gap stops being a lower-priority audit item. IOCs/Defensive References IP: 158.220.87.79 (ASN 51167, Contabo GmbH; rDNS vmi2213719.contaboserver.net)Domain: city-forum.com (resolving to the above IP since at least 2025-03-12; registered 2002, since abandoned)Active subdomains: city-forum.com, www.city-forum.com, server.city-forum.com, www.server.city-forum.com, mail.city-forum.com, www.mail.city-forum.comUser-agent: Go-http-client/1.1Salesforce: guest /aura calls to getItems/getConfigData; guest requests to /webruntime/api/services/data/vNN.0/graphql sweeping v56.0–v66.0; guest hits on /SiteRegister and /CommunitiesSelfRegServiceNow: guest POST /api/now/sp/search?sysparm_cancelable=true at escalating volume, Created by = guest Research and indicators referenced in this piece are drawn from Reco's City-Forum campaign investigation. Interview quotes from Nitay Bachrach were obtained in an interview arranged through Reco's PR representative. Sources: Long-running Data Theft Campaign Targeting Salesforce, ServiceNow — Dark Reading"City-Forum" data-theft attacks target Salesforce, ServiceNow portals — BleepingComputerThe "City-Forum" Campaign — Reco (original research)A stranger has been reading Salesforce and ServiceNow portals worldwide for 17 months — Help Net SecurityStealthy 'City-Forum' Attacks Target Salesforce and ServiceNow With Custom Toolset — SecurityWeekQuery Objects | Query Records | GraphQL API — Salesforce DevelopersDefine a search source — ServiceNow DocumentationApply user criteria to a search source — ServiceNow Documentation

By Igboanugo David Ugochukwu CORE

In a chapter of The SingleStore Cookbook, there is a complete sentiment analysis pipeline using Rust compiled to WebAssembly and loaded directly into SingleStore via its Code Engine. The result was clean: one CLI command to deploy, sentiment scoring running inside the database engine alongside the data and a full stock-price-plus-headlines analytical pipeline built on top of it. Can we do the same thing in Neo4j? Neo4j has a fully documented, officially supported extensibility model that lets us write custom functions and procedures in Java and register them directly with the database engine. Java also has a port of Valence Aware Dictionary and sEntiment Reasoner (VADER), the same lexicon-based sentiment analyzer used in the SingleStore Rust implementation. The pieces are all there. The question is how well they would fit together and what the resulting pipeline would look like compared to the SingleStore Wasm approach. This article documents an experiment from start to finish: the UDF implementation, the graph schema, a complete data loading and scoring pipeline, and a full set of analytical queries. Along the way, we also discovered that Neo4j has a second path to sentiment analysis via NLP procedures, and the choice between the two turns out to be an interesting engineering decision in its own right. The goal here isn't to claim a new sentiment-analysis technique. It's to explore what Neo4j's extension model makes possible and how the result compares with the equivalent SingleStore implementation. The full source code is available on GitHub. What We Are Building Figure 1 shows how data moves through the pipeline. CSV files are loaded into Neo4j via LOAD CSV or the Python loader. As each Headline node is created, sentiment.score() is called inline in the same Cypher statement — scoring happens inside the database at ingestion time, not in a separate application step. The resulting graph is then available for the analytical queries covered later in the article. Figure 1. Pipeline data flow The pipeline mirrors the one in the SingleStore book chapter: A VADER-based sentiment function registered with the system and callable from queriesA graph containing synthetic stock price ticks and news headlinesA set of analytical queries: per-headline scoring, daily aggregation, sentiment-vs-price joins, most positive and most negative ranking, and a live consistency check For the example in this article, we'll need a local install of Neo4j, a Docker container, or a server where we can place files and restart the process. How Neo4j Extensibility Works Neo4j lets us extend Cypher with custom Java code packaged as a .jar file. This is a fully documented and supported extensibility path. Neo4j publishes official guidance on setting up a plugin project and maintains a Neo4j Procedure Template on GitHub. Neo4j provides this extensibility model for building custom extensions. There are several extension types: User-defined functions (UDFs) – take inputs, return a single value, called inline in a query like a built-in functionUser-defined aggregation functions (UDAs) – group-level aggregation, analogous to SUM or COLLECTProcedures – more flexible, can return multiple rows and perform side effects, called with CALL For our sentiment use case, a UDF is the right fit. We pass in a string and get back a map of polarity scores. In SingleStore, the equivalent was a Table-Valued Function (TVF) that returned a row set. A Neo4j UDF returning a Map<String, Double> is the closest structural equivalent. One practical note on naming is that Neo4j maintains a list of reserved and deprecated procedure namespaces, such as db.*, dbms.*, graph.* and others. These are off-limits. The sentiment.* namespace is not reserved or deprecated, so it's a safe choice. Check User-defined procedures before choosing a namespace for any new plugin to confirm it doesn't conflict with a built-in namespace. What to Know Before We Build Because a Neo4j UDF runs inside the same JVM as the database engine, it's worth understanding a few practical considerations before diving in. These are the same considerations that apply to any extension of a running JVM process — Neo4j's own plugin authors deal with them too — and being aware of them upfront makes for a smoother build experience. Memory. If a plugin allocates more memory than the JVM has available — for example, loading a very large model file or accumulating state across calls — it can trigger an OutOfMemoryError. The VADER UDF we build here loads a compact lexicon and holds no state, so this is not a concern in practice. For more complex plugins that allocate significant heap memory, Neo4j provides a preview ProcedureMemory API where we can register allocations against the configured transaction memory limits, which prevents uncapped growth from causing database restarts. Uncaught exceptions. An unhandled RuntimeException in a UDF propagates up through the Neo4j query execution engine. Good error handling in the UDF code keeps this from becoming a problem. Infinite loops and thread starvation. A UDF that hangs — waiting on a network call, deadlocked or stuck in a loop — ties up a JVM thread from Neo4j's shared pool. The VADER UDF makes no network calls, holds no state and performs a relatively small amount of computation per call, so this is not a concern here, but it matters for more complex plugins. Dependency conflicts. Because the plugin jar shares the classpath with the database engine, any library bundled into the fat jar must not conflict with libraries Neo4j already ships. This problem was encountered during development and more on that in the build section below, including a straightforward fix. Startup failures. A jar that fails to load prevents the system from starting. The solution is always to test in a development environment first, such as Neo4j Desktop or a local Docker container, before deploying anywhere more critical. Security. A Java plugin has full access to the JVM, filesystem and network. This is the same trust model as Neo4j's own plugins and is appropriate for code we've written and reviewed. For third-party plugins from untrusted sources, the same caution applies as for any third-party code running inside a critical process. AuraDB. AuraDB supports plugins provided and certified by Neo4j, such as APOC, GDS and GenAI, but not arbitrary third-party or custom jars. The Java UDF approach in this article requires self-managed Neo4j, such as Desktop, Docker or a server install. If AuraDB is the target, the Java UDF approach described here is not available; the GenAI plugin or an external service are the alternatives. None of this should discourage us from building a Java UDF. The VADER UDF we build here is small, does one thing, makes no network calls, holds no state and uses a well-tested library. The sensible approach, which applies to any plugin development, is to build and test on a local development instance first, then deploy with confidence. In Neo4j, the steps to deploy our UDF are: Build a fat jarStop the serverCopy the jar file to the server's plugins directoryAdd an allowlist entry to neo4j.confRestart the server The deployment model differs from the Wasm approach — more on that in the build and deploy section below. Setting Up the Project Prerequisites We'll need the following before starting: Java 21 – check with java -version. Java 21 is the version used by the official Neo4j plugin template and by this articleMaven 3.8+ – check with mvn -versionNeo4j 2026.06.0 – the version used for this article, running in one of the ways described below Choosing a Neo4j Install For this experiment, we'll use either Neo4j Desktop or Docker. Neo4j also supports server installs on Linux and Windows — the plugin mechanism is the same — but we did not test that path and don't provide instructions for it here. Neo4j Desktop is the easiest starting point. Download it from Neo4j for Desktop, create a new project and start a local database server. Find the exact path to the plugins directory by clicking Open folder > plugins. Docker is convenient for a clean, throwaway environment. The command below starts Neo4j 2026.06.0 with a plugins volume mounted to a local directory, which is where we'll drop the jar: Shell mkdir -p ~/neo4j/plugins ~/neo4j/data docker run \ --name neo4j-sentiment \ -p 7474:7474 -p 7687:7687 \ -v ~/neo4j/plugins:/plugins \ -v ~/neo4j/data:/data \ -e NEO4J_AUTH=neo4j/password \ -e NEO4J_dbms_security_procedures_allowlist="sentiment.*" \ neo4j:2026.06.0 With Docker we pass the allowlist as an environment variable rather than editing neo4j.conf directly. The jar goes into ~/neo4j/plugins/ on the host. Creating the Project Structure Create a new Maven project directory: Shell mkdir neo4j-sentiment-udf cd neo4j-sentiment-udf The full directory tree should look like this when finished: Plain Text neo4j-sentiment-udf/ ├── pom.xml └── src/ ├── main/ │ └── java/ │ └── sentiment/ │ └── Sentimentable.java └── test/ └── java/ └── sentiment/ └── SentimentableTest.java The sections below cover each part in turn. Next, we'll create both source directories: Shell mkdir -p src/main/java/sentiment mkdir -p src/test/java/sentiment Maven Dependencies We'll create a pom.xml file in the project root. The structure follows the official Neo4j procedure template at Neo4j Procedure Template, with three adjustments specific to this project that are explained below. XML <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.neo4j.example</groupId> <artifactId>sentimentable</artifactId> <version>1.0.0-SNAPSHOT</version> <packaging>jar</packaging> <name>Neo4j Sentiment UDF</name> <description>VADER sentiment analysis as a Neo4j user-defined function</description> <properties> <java.version>21</java.version> <maven.compiler.release>${java.version}</maven.compiler.release> <neo4j.version>2026.06.0</neo4j.version> </properties> <!-- ADJUSTMENT 1: JitPack required for VaderSentimentJava --> <repositories> <repository> <id>jitpack.io</id> <url>https://jitpack.io</url> </repository> </repositories> <dependencies> <dependency> <groupId>org.neo4j</groupId> <artifactId>neo4j</artifactId> <version>${neo4j.version}</version> <scope>provided</scope> </dependency> <!-- ADJUSTMENT 2: VaderSentimentJava runtime dependency --> <dependency> <groupId>com.github.apanimesh061</groupId> <artifactId>VaderSentimentJava</artifactId> <version>v1.1.1</version> </dependency> <!-- Test dependencies — let neo4j-harness manage JUnit version --> <dependency> <groupId>org.neo4j.test</groupId> <artifactId>neo4j-harness</artifactId> <version>${neo4j.version}</version> <scope>test</scope> </dependency> <dependency> <groupId>org.neo4j.driver</groupId> <artifactId>neo4j-java-driver</artifactId> <version>6.0.2</version> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>21</source> <target>21</target> </configuration> </plugin> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-surefire-plugin</artifactId> <version>3.5.4</version> </plugin> <plugin> <artifactId>maven-shade-plugin</artifactId> <version>3.5.1</version> <executions> <execution> <phase>package</phase> <goals><goal>shade</goal></goals> <configuration> <!-- ADJUSTMENT 3: relocate commons-lang3 to avoid version conflict with Neo4j's internal copy --> <relocations> <relocation> <pattern>org.apache.commons.lang3</pattern> <shadedPattern>sentiment.shaded.org.apache.commons.lang3</shadedPattern> </relocation> </relocations> <artifactSet> <excludes> <exclude>org.neo4j:*</exclude> </excludes> </artifactSet> <shadedArtifactAttached>false</shadedArtifactAttached> </configuration> </execution> </executions> </plugin> </plugins> </build> </project> The three adjustments from the official template are called out inline as comments. Everything else — groupId convention, provided scope for the Neo4j dependency, the shade plugin structure and the test dependency pattern — follows the official guidance. Writing the UDF We'll create the file src/main/java/sentiment/Sentimentable.java and paste in the following: Java package sentiment; import com.vader.sentiment.analyzer.SentimentAnalyzer; import com.vader.sentiment.analyzer.SentimentPolarities; import org.neo4j.procedure.Description; import org.neo4j.procedure.Name; import org.neo4j.procedure.UserFunction; import java.util.Map; public class Sentimentable { @UserFunction("sentiment.score") @Description("Score a string with VADER. Returns compound, positive, negative, neutral.") public Map<String, Double> score(@Name("text") String text) { if (text == null || text.isBlank()) { return Map.of("compound", 0.0, "positive", 0.0, "negative", 0.0, "neutral", 1.0); } final SentimentPolarities polarities = SentimentAnalyzer.getScoresFor(text); return Map.of( "compound", (double) polarities.getCompoundPolarity(), "positive", (double) polarities.getPositivePolarity(), "negative", (double) polarities.getNegativePolarity(), "neutral", (double) polarities.getNeutralPolarity() ); } } The following implementation details are worth highlighting. The v1.1.1 API uses a static method — SentimentAnalyzer.getScoresFor(text) — rather than a mutable instance. This means there is no shared state between calls, which is what we want in a Neo4j UDF where multiple Cypher queries may invoke the function concurrently. The VADER lexicon is loaded internally by the library on first call and cached for subsequent calls. The @UserFunction("sentiment.score") annotation registers the method as callable from Cypher under that name. The @Name annotation on the parameter provides the argument name for Neo4j's function metadata and documentation — UDFs are always called with positional arguments in Cypher, as shown throughout this article: sentiment.score(row.headline). The return type is Map<String, Double>. In Cypher, this surfaces as a map literal, so callers can destructure it with dot notation: sc.compound, sc.positive and so on. In the SingleStore version, the TVF returned a row set and was used in a FROM clause. Here the UDF is called inline in a WITH or RETURN clause instead. Writing the Tests Following the official Neo4j procedure template pattern, we'll use neo4j-harness to spin up a lightweight embedded Neo4j instance in JUnit, register our UDF with it and run Cypher queries against it — all without deploying to a running database. This is the recommended testing approach in Neo4j's own documentation. We'll create the file src/test/java/sentiment/SentimentableTest.java and paste in the following: Java package sentiment; import org.junit.jupiter.api.AfterAll; import org.junit.jupiter.api.BeforeAll; import org.junit.jupiter.api.Test; import org.junit.jupiter.api.TestInstance; import org.neo4j.driver.Driver; import org.neo4j.driver.GraphDatabase; import org.neo4j.driver.Session; import org.neo4j.harness.Neo4j; import org.neo4j.harness.Neo4jBuilders; import static org.junit.jupiter.api.Assertions.assertEquals; import static org.junit.jupiter.api.Assertions.assertTrue; @TestInstance(TestInstance.Lifecycle.PER_CLASS) public class SentimentableTest { private Neo4j embeddedDatabaseServer; private Driver driver; @BeforeAll void initializeNeo4j() { this.embeddedDatabaseServer = Neo4jBuilders.newInProcessBuilder() .withDisabledServer() .withFunction(Sentimentable.class) .build(); this.driver = GraphDatabase.driver(embeddedDatabaseServer.boltURI()); } @AfterAll void closeNeo4j() { this.driver.close(); this.embeddedDatabaseServer.close(); } @Test void scorePositiveSentence() { try (Session session = driver.session()) { var scores = session.run( "RETURN sentiment.score('The movie was great') AS scores" ).single().get("scores").asMap(); assertTrue((Double) scores.get("compound") > 0.5); assertTrue((Double) scores.get("positive") > 0.0); assertEquals(0.0, (Double) scores.get("negative")); } } @Test void capitalizationIncreasesScore() { try (Session session = driver.session()) { var normal = session.run( "RETURN sentiment.score('The movie was great') AS scores" ).single().get("scores").asMap(); var caps = session.run( "RETURN sentiment.score('The movie was GREAT!') AS scores" ).single().get("scores").asMap(); assertTrue((Double) caps.get("compound") > (Double) normal.get("compound")); } } @Test void emptyStringReturnsNeutral() { try (Session session = driver.session()) { var scores = session.run( "RETURN sentiment.score('') AS scores" ).single().get("scores").asMap(); assertEquals(0.0, (Double) scores.get("compound")); assertEquals(1.0, (Double) scores.get("neutral")); } } @Test void nullStringReturnsNeutral() { try (Session session = driver.session()) { var scores = session.run( "RETURN sentiment.score(null) AS scores" ).single().get("scores").asMap(); assertEquals(0.0, (Double) scores.get("compound")); assertEquals(1.0, (Double) scores.get("neutral")); } } } The four tests mirror the tests we'll run manually in Neo4j Browser, but now they run automatically as part of the build. Neo4jBuilders.newInProcessBuilder() starts a lightweight embedded instance with the Sentimentable function registered; .withDisabledServer() skips the HTTP server since we only need the Bolt connection. The structure follows the official JoinTest.java pattern. Building and Deploying Step 1: Install the Maven Wrapper and build The official Neo4j procedure template uses the Maven Wrapper (mvnw), which means we only need Java installed, not a separate Maven installation. To add the wrapper to the project: Shell mvn wrapper:wrapper Then build and run the tests: Shell ./mvnw clean package Or to skip the tests during development: Shell ./mvnw clean package -DskipTests To use a globally installed Maven directly, mvn clean package -DskipTests works equally well — the wrapper is a convenience, not a requirement. Maven compiles the Java source, runs the Shade plugin and writes two jar files to target/. The one we want is sentimentable-1.0.0-SNAPSHOT.jar — the fat jar with VADER bundled inside. The original-sentimentable-1.0.0-SNAPSHOT.jar is the plain jar without dependencies, so we'll ignore it. If the build fails with a package org.neo4j.procedure does not exist error, check that the pom.xml has <scope>provided</scope> on the Neo4j dependency and that the version matches the running Neo4j instance. Step 2: Copy the Jar to the Plugins Directory Neo4j Desktop: Stop the serverOpen folder > plugins and copy sentimentable-1.0.0-SNAPSHOT.jar into that folderOpen folder > conf > neo4j.conf, find dbms.security.procedures.allowlist= and uncomment the line if it is commented outAdd sentiment.* to the end of the line Docker: Copy to the host directory mounted as /plugins: Shell cp target/sentimentable-1.0.0-SNAPSHOT.jar ~/neo4j/plugins/ Step 3: Whitelist the Function Namespace Neo4j's default dbms.security.procedures.allowlist is *, which loads all plugins. If an allowlist is configured with specific entries, any custom namespace must be included or the function will silently be unavailable — no error on startup, it simply won't exist. It's good practice to configure an explicit allowlist following the principle of least privilege. Our UDF uses only the public Neo4j procedure API, which means it doesn't require the separate dbms.security.procedures.unrestricted setting — that's only needed for extensions that access internal APIs. Step 4: Restart Neo4j Neo4j Desktop: Restart the server using the button in the Desktop UI. If Desktop shows "stopped" immediately after starting, open http://localhost:7474 directly — the server may be running before the UI reflects it. Docker: If this is the initial launch, no restart is needed — the docker run command in the Choosing a Neo4j Install section already starts Neo4j with the jar in place from the mounted plugins directory. If updating the jar after the container is already running, stop the container, replace the jar in ~/neo4j/plugins/ and then restart: Shell docker stop neo4j-sentiment cp target/sentimentable-1.0.0-SNAPSHOT.jar ~/neo4j/plugins/ docker start neo4j-sentiment The clearest confirmation that the plugin loaded correctly is to run the verification queries in step 5 below — if sentiment.score() is visible and returns results, the jar was picked up successfully. Verifying the Function We can interact with Neo4j by entering http://localhost:7474 in the browser. Step 5: Confirm the Function Loaded First, we'll check that Neo4j can see the function at all: Cypher SHOW FUNCTIONS YIELD name WHERE name STARTS WITH 'sentiment' RETURN name; Expected output: Plain Text +-----------------+ | name | +-----------------+ | sentiment.score | +-----------------+ If this returns zero rows, the jar is either not in the plugins directory, the allowlist entry is missing or misspelled or Neo4j was not fully restarted. Step 6: Run the Tests Run the following tests: Cypher RETURN sentiment.score('The movie was great') AS scores; Expected output: JSON { neutral: 0.4230000078678131, negative: 0.0, positive: 0.5770000219345093, compound: 0.6248999834060669 } Now we'll test that VADER's capitalization awareness is working: Cypher RETURN sentiment.score('The movie was GREAT!') AS scores; Expected output: JSON { neutral: 0.36899998784065247, negative: 0.0, positive: 0.6309999823570251, compound: 0.7289999723434448 } The compound score rises with the capitalized GREAT!, exactly as in the Wasm version. For the examples we tested, the Java port produces scores consistent with the Rust crate used in the book chapter. Now, we'll test the null guard. Passing an empty string should return a neutral result rather than an exception: Cypher RETURN sentiment.score('') AS scores; Expected output: JSON { neutral: 1.0, negative: 0.0, positive: 0.0, compound: 0.0 } If all three return the expected values, the UDF is working and we're ready to build the graph schema and load data. Designing the Graph Schema The graph model for this pipeline has three node labels, as shown in Figure 2. A central Stock node connects to Tick nodes via HAS_TICK relationships and to Headline nodes via HAS_HEADLINE relationships. VADER polarity scores are stored directly on each Headline node at ingestion time, making them available to any Cypher query without recomputing. Figure 2. Graph data model Plain Text (:Stock {symbol}) -[:HAS_TICK]-> (:Tick {symbol, ts, open, high, low, close, volume}) -[:HAS_HEADLINE]->(:Headline {id, symbol, ts, headline, url, publisher, compound, positive, negative, neutral}) The Stock node acts as the join key. In SingleStore the queries join tick and stock_sentiment on (symbol, DATE(ts)); in Neo4j that same co-reference is expressed by traversing from a shared Stock node to both Tick and Headline nodes with a date predicate. The relationship replaces the foreign key. Let's now run these commands to create constraints and indexes: Cypher CREATE CONSTRAINT tick_pk IF NOT EXISTS FOR (t:Tick) REQUIRE (t.symbol, t.ts) IS NODE KEY; CREATE CONSTRAINT headline_id IF NOT EXISTS FOR (h:Headline) REQUIRE h.id IS UNIQUE; CREATE CONSTRAINT stock_id IF NOT EXISTS FOR (s:Stock) REQUIRE s.symbol IS UNIQUE; CREATE INDEX tick_symbol_ts IF NOT EXISTS FOR (t:Tick) ON (t.symbol, t.ts); CREATE INDEX headline_symbol_ts IF NOT EXISTS FOR (h:Headline) ON (h.symbol, h.ts); Loading Data and Scoring Headlines Getting the Datasets The datasets, notebook and SQL files for the original SingleStore book chapter are all publicly available in the book's GitHub repository. The two CSV files we need are in the datasets subdirectory: fictitious_stocks.csv – synthetic daily OHLCV stock prices (random-walk model, fictitious symbols)raw_fictitious_headlines.csv – programmatically generated news headlines (templates + ticker symbols + financial events) We'll download both files into our local working directory. Dataset Format fictitious_stocks.csv has seven columns. The date and Name columns are renamed to ts and symbol, respectively, to match the graph schema: Plain Text date,open,high,low,close,volume,Name 2013-01-02,743.98,756.93,736.15,745.68,9142645,BBRQ-FX 2013-01-03,764.41,779.16,757.72,765.16,1208771,BBRQ-FX ... raw_fictitious_headlines.csv has five columns that map directly to the Headline node properties: Plain Text headline,url,publisher,ts,symbol BBRQ-FX stock record revenues after analyst update,http://www.hill.net/,The Stock Chronicle,2014-10-22,BBRQ-FX ... No preprocessing is needed beyond what the loader already does, such as dropping nulls, filtering the one extreme volume outlier and sorting by date. The Python Loader The data_loader.py below reads the two CSV files and writes them into Neo4j via the Python driver. Install the dependencies first if not already done so: Shell pip install -r requirements.txt Then run the loader, substituting the actual paths to the downloaded CSV files. Also replace your_password_here with your actual password. Python # data_loader.py import pandas as pd from neo4j import GraphDatabase from tqdm import tqdm URI = "bolt://localhost:7687" AUTH = ("neo4j", "your_password_here") TICK_CSV = "fictitious_stocks.csv" RAW_CSV = "raw_fictitious_headlines.csv" driver = GraphDatabase.driver(URI, auth=AUTH) def chunks(df, size): for i in range(0, len(df), size): yield df.iloc[i:i+size].to_dict("records") # load tick data tick_df = (pd.read_csv(TICK_CSV) .dropna() .query("volume <= 2_147_483_647") .rename(columns={"date": "ts", "Name": "symbol"}) .sort_values(["ts", "symbol"])) tick_batches = list(chunks(tick_df, 1000)) print(f"Loading {len(tick_df):,} tick rows in {len(tick_batches)} batches...") with driver.session() as session: for batch in tqdm(tick_batches, desc="Ticks", unit="batch"): session.run(""" UNWIND $rows AS row MERGE (s:Stock {symbol: row.symbol}) CREATE (t:Tick {symbol: row.symbol, ts: date(row.ts), open: row.open, high: row.high, low: row.low, close: row.close, volume: toInteger(row.volume)}) CREATE (s)-[:HAS_TICK]->(t) """, rows=batch) # load headlines and score at ingestion time raw_df = pd.read_csv(RAW_CSV) raw_batches = list(chunks(raw_df, 1000)) print(f"Loading {len(raw_df):,} headline rows in {len(raw_batches)} batches...") with driver.session() as session: for batch in tqdm(raw_batches, desc="Headlines", unit="batch"): session.run(""" UNWIND $rows AS row MATCH (s:Stock {symbol: row.symbol}) WITH s, row, sentiment.score(row.headline) AS sc CREATE (h:Headline { id: randomUUID(), symbol: row.symbol, ts: datetime(row.ts), headline: row.headline, url: row.url, publisher: row.publisher, compound: sc.compound, positive: sc.positive, negative: sc.negative, neutral: sc.neutral }) CREATE (s)-[:HAS_HEADLINE]->(h) """, rows=batch) print("Done.") driver.close() Run the Python program: Shell python data_loader.py The key line is sentiment.score(row.headline) AS sc inside the Cypher. This is doing what the sentimentable(i.headline) TVF call does in the SingleStore INSERT ... SELECT — computing scores at the database level in the same operation that writes the record, with no round-trip to the application layer. One important note if we need to re-run the loader is that the script uses CREATE for Tick and Headline nodes, so running it a second time without clearing the database will create duplicates rather than overwriting. Clear the database first with the following Cypher, using the Query tab: Cypher MATCH (n) CALL { WITH n DETACH DELETE n } IN TRANSACTIONS OF 100 ROWS; The batch size of 100 is deliberate — larger values can exceed the default transaction memory limit and fail. After clearing, re-run the schema constraints and indexes before running the loader again. Alternative Loading Directly From GitHub With LOAD CSV To stay entirely within Cypher and avoid Python, Neo4j's LOAD CSV command can fetch the files directly from GitHub over HTTPS. No file copying, no import directory, no Python dependencies. Run both queries using the Query tab in order — ticks first, then headlines, since the headlines query does a MATCH on Stock nodes created by the tick query. Cypher LOAD CSV WITH HEADERS FROM 'https://...' AS row CALL { WITH row MERGE (s:Stock {symbol: row.Name}) CREATE (t:Tick { symbol: row.Name, ts: date(row.date), open: toFloat(row.open), high: toFloat(row.high), low: toFloat(row.low), close: toFloat(row.close), volume: toInteger(row.volume) }) CREATE (s)-[:HAS_TICK]->(t) } IN TRANSACTIONS OF 1000 ROWS; LOAD CSV WITH HEADERS FROM 'https://...' AS row CALL { WITH row MATCH (s:Stock {symbol: row.symbol}) WITH s, row, sentiment.score(row.headline) AS sc CREATE (h:Headline { id: randomUUID(), symbol: row.symbol, ts: datetime(row.ts), headline: row.headline, url: row.url, publisher: row.publisher, compound: sc.compound, positive: sc.positive, negative: sc.negative, neutral: sc.neutral }) CREATE (s)-[:HAS_HEADLINE]->(h) } IN TRANSACTIONS OF 1000 ROWS; LOAD CSV WITH HEADERS reads the first row as column names, so the original names (row.Name, row.date) are mapped directly to the graph property names inline — the same column renaming the Python loader does with rename(). The IN TRANSACTIONS OF 1000 ROWS batching is required for the tick file at ~600,000 rows to avoid the transaction memory limit. The same delete-before-reload rule applies here: re-running either query without clearing the database first will create duplicates. The only requirement is that Neo4j has outbound HTTPS access to reach GitHub, which is the case for Desktop and local Docker. In a network-restricted server environment the Python loader with local files is the safer fallback. Next, some example queries to test using the Query tab. Headline-Level Sentiment Cypher MATCH (h:Headline) RETURN h.symbol AS symbol, date(h.ts) AS ts, left(h.headline, 30) AS headline, round(h.positive, 3) AS positive, round(h.negative, 3) AS negative, round(h.neutral, 3) AS neutral ORDER BY h.symbol, h.ts LIMIT 10; Aggregate Sentiment by Stock and Day Cypher MATCH (h:Headline) WITH h.symbol AS symbol, date(h.ts) AS ts, avg(h.positive) AS avg_positive, avg(h.negative) AS avg_negative, avg(h.neutral) AS avg_neutral, count(h) AS num_headlines RETURN symbol, ts, round(avg_positive, 3) AS avg_positive, round(avg_negative, 3) AS avg_negative, round(avg_neutral, 3) AS avg_neutral, num_headlines ORDER BY symbol, ts LIMIT 10; Join Sentiment With Closing Price In Cypher, the shared Stock node makes the symbol join implicit and we only need a date predicate. Cypher MATCH (t:Tick)<-[:HAS_TICK]-(s:Stock)-[:HAS_HEADLINE]->(h:Headline) WHERE date(t.ts) = date(h.ts) RETURN t.symbol AS symbol, date(t.ts) AS ts, round(t.close, 2) AS close, round(h.positive, 3) AS positive, round(h.negative, 3) AS negative, round(h.neutral, 3) AS neutral ORDER BY t.symbol, t.ts LIMIT 10; Most Positive Headlines Cypher MATCH (h:Headline) RETURN h.symbol, date(h.ts) AS ts, left(h.headline, 30) AS headline, round(h.positive, 3) AS positive ORDER BY h.positive DESC LIMIT 10; Most Negative Headlines Cypher MATCH (h:Headline) RETURN h.symbol, date(h.ts) AS ts, left(h.headline, 30) AS headline, round(h.negative, 3) AS negative ORDER BY h.negative DESC LIMIT 10; In the SingleStore book, CEO scandal headlines dominated the negative ranking across multiple stocks. We see the same pattern here because the underlying VADER lexicon is identical. Validate Stored Scores Against Live UDF Calls This mirrors the consistency check from the SingleStore book, where stored stock_sentiment values were compared against a fresh JOIN LATERAL sentimentable(...) call to confirm the ingestion pipeline was deterministic. Cypher MATCH (h:Headline {symbol: 'BBRQ-FX'}) WITH h, sentiment.score(h.headline) AS live RETURN h.symbol AS symbol, date(h.ts) AS ts, left(h.headline, 30) AS headline, CASE WHEN round(h.positive, 3) = round(live.positive, 3) AND round(h.negative, 3) = round(live.negative, 3) AND round(h.neutral, 3) = round(live.neutral, 3) THEN 'match' ELSE 'not match' END AS comparison LIMIT 10; Daily Average Sentiment vs. Closing Price The CTE-style aggregation from the book translates naturally to Cypher's WITH chaining. Cypher MATCH (h:Headline) WITH h.symbol AS symbol, date(h.ts) AS ts, avg(h.positive) AS avg_positive, avg(h.negative) AS avg_negative, avg(h.neutral) AS avg_neutral MATCH (t:Tick {symbol: symbol}) WHERE date(t.ts) = ts RETURN symbol, ts, round(t.close, 2) AS daily_close, round(avg_positive, 3) AS avg_positive, round(avg_negative, 3) AS avg_negative, round(avg_neutral, 3) AS avg_neutral ORDER BY symbol, ts LIMIT 10; What We Learned The experiment was a clear success. VADER runs inside Neo4j, scores headlines at ingestion time via a simple Cypher call and all the analytical queries from the SingleStore book have direct equivalents in Cypher. For the examples we tested, the Java port produces scores consistent with the Rust crate used in the SingleStore book — although independent language ports may differ in edge cases due to differences in tokenization or floating-point handling. The graph model handles the stock-tick-plus-headlines domain naturally and in several respects the Cypher queries are more expressive than their SQL counterparts — the relationship traversal from a shared Stock node replaces a keyed SQL join in a way that reflects the actual structure of the domain rather than just being an implementation detail. The graph model is a genuine advantage for the join queries. Replacing JOIN tick ON (symbol, DATE(ts)) with a graph traversal through a shared Stock node is not just syntactic preference — it reflects the actual structure of the domain. A stock symbol connects ticks and headlines naturally as a graph entity and Cypher expresses that more directly than a keyed SQL join. In-database scoring works. Calling sentiment.score(row.headline) inside the Cypher CREATE statement means scoring and ingestion happen in the same operation, with no round-trip to an application layer. This is the same goal the SingleStore Wasm pipeline achieves and the Java UDF delivers it cleanly. The dependency conflict is a one-time fix. We hit the commons-lang3 version conflict during development and it stopped the server from starting. The fix — relocating the bundled classes to a private namespace using the Maven Shade plugin — is straightforward once we know what to look for and the solution is baked into the pom.xml in this article. There are also honest differences from the SingleStore Wasm approach. Deployment requires a restart. SingleStore uses a tool that loads a function into a live database with no downtime. Neo4j requires a jar build, a file copy, a config edit and a restart. For an initial Docker launch, the jar is picked up automatically — but any subsequent update to the jar requires a container restart. The Maven Wrapper and the clear deployment steps in this article make the process repeatable. No execution sandbox. SingleStore runs each Wasm function instance in its own isolated process with a hard memory boundary. The Neo4j UDF runs in the same JVM as the server. For a small, well-behaved plugin like the VADER UDF this makes no practical difference, but it's a meaningful architectural distinction for more complex or heavyweight plugins. Language is JVM-based. The Wasm approach accepts any language that compiles to the Wasm core spec. Neo4j's extensibility model is JVM-only. For teams that want to bring existing Python or Rust models into the database, that is worth knowing about upfront. Alternative Approaches The Java UDF is the focus of this article, but it's not the only way to bring sentiment scoring close to Neo4j data. We considered several alternatives during the experiment. Some are compelling for specific use cases and others less so. Knowing the options helps us choose the right tool for our situation. Pre-scoring outside the database. Score all headlines before loading. Add the polarity scores as columns in the CSV and load everything with LOAD CSV. Nothing custom runs inside Neo4j at all. For a batch pipeline like this one, where data are loaded once and queried many times, this is entirely practical and requires no Java knowledge. The only thing we give up is the ability to call sentiment.score() inline in Cypher at query time. For many teams this will be the right answer and it's the simplest path to a working pipeline. External microservice. Deploy a small Python or Rust service that runs VADER and exposes an HTTP endpoint. An external microservice can expose VADER through an HTTP API, with the application layer calling the service before or during ingestion. This gives us complete process isolation — a crash in the sentiment service cannot touch the database — and works with AuraDB. The tradeoff is network latency on every call and the operational overhead of running a separate service. For lower-volume or interactive use cases it's a clean, flexible pattern. Neo4j GenAI plugin. Neo4j's GenAI plugin supports calling embedding and LLM APIs — OpenAI, Azure OpenAI and compatible endpoints — directly from Cypher. It's fully managed by Neo4j, works on AuraDB and requires no Java. To use a cloud LLM for sentiment classification rather than VADER’s lexicon is a well-supported, low-friction path. The tradeoff is API cost and the opacity of a large language model compared to VADER's fully transparent, inspectable lexicon — which matters in regulated domains where we need to explain a score. GraalVM native compilation. GraalVM can ahead-of-time compile Java UDFs to native binaries, reducing JVM startup overhead and memory footprint. This is a performance optimization rather than an architectural change — the code still runs inside the Neo4j process — and adds significant build complexity for modest gain in this use case. It is worth knowing about for larger, more heavyweight plugins, but not the right choice here. Wasm runtime embedded inside a Java UDF. Theoretically, we could embed a Wasm runtime such as wasmtime inside a Java UDF and execute the VADER Wasm module from within Neo4j, getting Wasm's sandbox guarantees inside Neo4j's plugin model. It's technically feasible but no published working example appears to exist and the complexity cost is high relative to the alternatives. An interesting idea to watch, but not practical today. The table below shows how these approaches compare on the dimensions that matter most. ApproachCompute locationAuraDBLanguage choiceOperational complexityPre-score outside DBCompleteYesAnyLowExternal microserviceCompleteYes (via APOC)AnyMediumAPOC NLP (cloud API)Remote serviceNo (APOC Extended required)N/ALowGenAI pluginRemote serviceYesN/ALowJava UDF (this article)Shared JVMNoJVM-basedMediumWasm-in-Java (theoretical)Wasm sandboxNoAny (via Wasm)Very high The Java UDF sits in the middle of this table — it's uniquely capable of calling sentiment.score() inline from any Cypher query without application-layer involvement and it runs entirely within the system without external API calls or network latency. Whether that inline, self-contained capability is what our use case needs is the key question. For development, experimentation and pipelines where the data and team are well understood, it's a compelling and practical approach. For other situations, the alternatives above offer different but equally valid tradeoffs. A Second Path Is APOC NLP Procedures The two approaches differ in where the computation happens, as shown in Figure 3. With the Java UDF, the VADER lexicon is bundled in the jar and scoring runs inside the Neo4j JVM — no network call, no external dependency, no per-call cost. With APOC NLP, Neo4j orchestrates calls to an external cloud API and receives scores back over the network. That single architectural difference drives most of the tradeoffs covered in this section. Figure 3. Java UDF vs. APOC NLP Neo4j already has sentiment analysis capability — it just works quite differently and it lives not in GDS but in APOC Extended, a separate component from APOC Core. APOC's NLP procedures act as wrappers around cloud-based Natural Language APIs. The supported providers are AWS Comprehend, Azure Cognitive Services and Google Cloud Natural Language. The calling pattern is straightforward. With AWS, for example: Cypher MATCH (h:Headline {symbol: 'BBRQ-FX'}) CALL apoc.nlp.aws.sentiment.stream(h, { key: $apiKey, secret: $apiSecret, nodeProperty: 'headline' }) YIELD value RETURN h.headline, value.sentiment, value.sentimentScore; And with Azure: Cypher MATCH (h:Headline {symbol: 'BBRQ-FX'}) CALL apoc.nlp.azure.sentiment.stream(h, { key: $apiKey, url: $apiUrl, nodeProperty: 'headline' }) YIELD value RETURN h.headline, value.sentiment, value.sentimentScore; The graph variant goes one step further and writes the sentiment result back as a node property automatically, with write: true in the config map. Choosing Between the Two Java VADER UDFAPOC NLP (AWS / Azure / GCP)Where scoring runsInside Neo4j JVMExternal cloud APINetwork call per batchNoYesCost per callNo API chargeAPI pricing appliesModel qualityLexicon-based (VADER)Cloud NLP / ML modelsAuraDB compatibleNoNo (APOC Extended not available in AuraDB)Java knowledge neededYesNoOffline / air-gappedYesNoDeterministic resultsYesProvider-dependentDomain tuningLimited (lexicon)Better (ML models handle context) The Java UDF is the stronger choice when scoring volume is high, API costs matter, the text is short social-media-style content that VADER was designed for, or an offline/air-gapped environment is required. The VADER lexicon is fully transparent — we can inspect why a string received a given score, which matters in regulated domains. APOC NLP is the stronger choice when Java knowledge is limited, the text requires linguistic nuance beyond VADER’s lexicon (negation, sarcasm, domain-specific vocabulary), or cloud NLP APIs are already in use for other workloads. One important constraint applies to both: APOC NLP is part of APOC Extended, not APOC Core. AuraDB includes APOC Core by default, but APOC Extended is not available in AuraDB — so neither the Java UDF nor APOC NLP works there. The GenAI plugin or an external microservice are the practical AuraDB paths. GDS, Neo4j's Graph Data Science library, does not include text-level sentiment analysis — it's graph-algorithm-oriented. Text scoring in Neo4j is either in-database via a Java UDF or delegated to a cloud NLP service via APOC. Summary The experiment confirms that Neo4j's Java extensibility model is a capable platform for in-database compute. The VADER UDF works, the graph model is a natural fit for the stock-tick-plus-headlines domain and the analytical queries translate cleanly from SQL to Cypher — in some cases more expressively, because the relationship between prices and headlines is explicit in the graph schema rather than inferred at query time through a join predicate. The more interesting engineering question is when to use a Java UDF versus the alternatives. The answer depends primarily on four factors: Deployment model (self-managed Neo4j only for UDFs)Latency and network requirements (the UDF has none; APOC NLP and external microservices introduce both)Model sophistication (VADER's lexicon is transparent and fast but limited; cloud NLP APIs offer better linguistic coverage)Operational constraints (Java knowledge, plugin management and the restart-on-update requirement all have a cost) There is no universally correct choice — the table in the APOC NLP section lays out the tradeoffs and reasonable teams will land in different places depending on their priorities. What the article does establish is that the approach works and is officially supported. Building a plugin is documented and templated. For development, experimentation and well-understood production pipelines, it's a practical and interesting path. To go further, the official Neo4j Procedure Template is an excellent starting point, neo4j-harness makes unit testing UDFs straightforward without needing a running database instance and the full Neo4j Java Reference covers procedures, aggregation functions and the complete extensibility API in depth. The full source code is available on GitHub.

By Akmal Chaudhri CORE

Model Context Protocol (MCP) servers that work perfectly in development can fail intermittently once they are deployed across multiple replicas behind a load balancer. The failure mode is a stream of "session not found" errors that appear at random, and the cause is a mismatch between how certain MCP transports hold session state and how load balancers distribute requests. This article explains why the problem occurs, when it applies, and a concrete pattern for solving it using a shared session store. The problem is easy to miss in early development because it only appears once there is more than one server instance. A single-instance deployment holds every session in local memory, so every request naturally finds its session. Add replicas, and that assumption quietly breaks. The Failure Pattern Consider a deployment with four MCP server replicas behind a round-robin load balancer, serving agents that connect over the Server-Sent Events (SSE) transport. In this configuration, roughly three out of four follow-up requests fail with a "session not found" error. That ratio is not random. With four replicas and round-robin distribution, a follow-up request has only a one-in-four chance of returning to the replica that created the session. The other three times it lands on a replica that has no record of that session. The reason the failures look random at first is that success depends entirely on which replica the load balancer happens to select. The distribution of failures tracks the replica count directly, which is the clearest signal that the load balancer, not application logic, is the source of the problem. Why MCP Sessions and Load Balancers Conflict Not every MCP deployment has this problem, so it helps to be precise about when it applies. A tools-only MCP server can be stateless. Under the streamable HTTP transport, the client caches tool schemas after discovery, and each tool call is a self-contained request that carries everything the server needs to process it. Any replica can handle any request, and load balancing works without special handling. Two situations make a deployment session-bound. The first is the SSE transport. SSE was the only remote transport available for a long time and remains widely deployed. It is stateful by design: the client opens a long-lived connection that the server holds open as a stream, and the server delivers responses back through that open stream rather than through the response to each individual request. The stream physically lives on one replica. When a follow-up request is routed to a different replica, that replica is not holding the stream and cannot associate the request with the session. The result is the "session not found" error. The second is stateful MCP features. Even on a transport that supports stateless operation, an MCP server that must retain per-client state needs sessions. MCP resource subscriptions that push updates when server-side data changes, long-running operations where a client may disconnect and reconnect expecting to resume, and per-client authorization context established at initialization all require the server to hold state across requests. That state must be reachable regardless of which replica receives the next request. The conflict reduces to a single sentence, which is that the session lives on one replica, but the load balancer distributes requests across all of them. How the Connection Is Established The session originates at connection time. The following example uses the Koog framework to connect an agent to an MCP server over SSE, which illustrates where the session comes from: Kotlin import ai.koog.agents.core.agent.AIAgent import ai.koog.agents.mcp.McpToolRegistryProvider import ai.koog.prompt.executor.llms.all.simpleAnthropicAIExecutor import ai.koog.prompt.llm.AnthropicModels import kotlinx.coroutines.runBlocking fun main() = runBlocking { // Open an SSE transport to the MCP server val transport = McpToolRegistryProvider.defaultSseTransport("http://mcp-server:3000/sse") // Build a tool registry from the tools the MCP server val mcpRegistry = McpToolRegistryProvider.fromTransport( transport = transport, name = "records-client", version = "1.0.0" ) val agent = AIAgent( executor = simpleAnthropicAIExecutor(), llmModel = AnthropicModels.Claude.SONNET, toolRegistry = mcpRegistry ) val result = agent.run("Look up the status of record 12345") println(result) } The relevant detail is the transport and the roles it establishes. The client, Koog in this case, opens the SSE connection, and the session lives on the MCP server. Opening an SSE transport creates a stateful connection: the MCP server creates a session bound to that open stream, and from that point the client and server communicate through a channel anchored to one specific server instance. With a single instance, this is invisible. Behind a load balancer, it is the entire problem. The fix belongs on the server side, not in the client. The Fix: A Shared Session Store The solution is to stop storing session state in a replica's local memory and move it to a shared store that every replica can reach. This is an addition to the MCP server implementation. Neither the MCP specification nor the client library provides a distributed session store; the specification defines that sessions exist but does not prescribe how to persist them across instances, so the server-side session handling is the implementer's responsibility. Redis is a natural fit for this role because the access pattern is a simple keyed lookup and the added latency is negligible relative to the rest of an agent request. The mechanism is straightforward. When any replica creates a session, it writes the session record to the shared store rather than to local memory. When any replica receives a request, it reads the session from the shared store before processing. The session no longer belongs to a replica; it belongs to the store, and every replica can reach it. The session record contains what the server would otherwise hold in memory - the session identifier, the negotiated capabilities, any accumulated per-client state, and timestamps for expiry. Assigning each entry a time-to-live allows idle sessions to expire automatically rather than accumulating. The change in the server's request handling can be reduced to the difference between a local map and a shared lookup: Kotlin // Before: the session lives in this replica's memory. // Other replicas have no record of it. val localSessions = mutableMapOf<String, McpSession>() fun handleRequest(sessionId: String, request: McpRequest): McpResponse { val session = localSessions[sessionId] ?: error("session not found") // fails on any other replica return session.process(request) } Kotlin // After: the session lives in a shared store every replica can read. suspend fun handleRequest(sessionId: String, request: McpRequest): McpResponse { val session = sessionStore.get(sessionId) // shared lookup ?: error("session expired or unknown") val response = session.process(request) sessionStore.put(sessionId, session) // persist any state change return response } The SSE transport adds one further requirement. Because the response must travel back through the stream held by a specific replica, the shared store also records which replica holds the stream, and a publish-subscribe channel routes the response to that replica when a request is handled elsewhere: Kotlin // The replica holding the SSE stream subscribes for its sessions sessionBus.subscribe("mcp:response:$sessionId") { payload -> sseStream.send(payload) } // Any replica that processes a request publishes the response sessionBus.publish("mcp:response:$sessionId", response) In this arrangement, the shared store serves two purposes. It is the session store that allows any replica to handle a request, and it is the message bus that routes each response to the replica holding the open stream. A request may arrive at any replica, while the response is delivered to the connection the client is actually listening on. Why Not Sticky Sessions The most immediate alternative is sticky sessions: configuring the load balancer to pin each client to the replica that created its session. This works and is a reasonable temporary measure, but it carries three drawbacks that make it unsuitable as a durable solution. Sticky sessions undermine load distribution, because a high-volume client is concentrated on a single replica while others remain underused. They reintroduce the single point of failure that multiple replicas were intended to eliminate: if the pinned replica fails, every session on it is lost. And they complicate scaling, because newly added replicas receive no existing traffic and take on load only gradually. A shared session store avoids all three. The load balancer can use plain round-robin distribution. Any replica can fail without affecting sessions held by the others. A new replica can serve existing sessions immediately, because it reads them from the same shared store as every other replica. Results With the shared session store in place, the "session not found" errors are eliminated for active sessions, and requests distribute evenly across replicas. Deliberately terminating a replica no longer interrupts active agents, and their requests are absorbed by the remaining replicas. Adding a replica requires no special handling. The shared lookup adds a small step to each request, but the cost is minor in context. A session read is well under a millisecond, while an agent request already spends hundreds of milliseconds or more on model inference and downstream calls. The overhead is not observable in practice. Summary For teams deploying MCP servers at scale, three points are worth carrying forward. Keep the MCP server stateless where possible. A tools-only server on the streamable HTTP transport scales horizontally without any of this complexity. Sessions should be introduced only when genuinely required, for subscriptions, resumable operations, or server-held per-client context. When sessions are required, do not store them on the replica. Move them to a shared store so that any replica can serve any request. This mirrors the lesson web applications settled on years ago for HTTP session state, now recurring in the context of MCP. Account for the SSE response-routing requirement. A shared session store resolves request handling, but the response must still reach the replica holding the open stream, which a publish-subscribe channel provides. Session persistence behind a load balancer is a common example of the operational gaps teams encounter when deploying MCP in production, and the shared-store pattern described here is a direct and durable solution.

In a previous article, we built a static supply chain graph in Neo4j using Apache Spark, with suppliers, warehouses, distribution centers, and retailers connected by shipping routes. That gave us a snapshot of the network at a point in time. In this article, we'll add the streaming layer: shipment events flow through Confluent Cloud Kafka in real time, land in Neo4j as enriched graph properties, and a live dashboard shows network health updating as events arrive. The full source code is available on GitHub. The Stack Each tool in the stack does what it does best: ToolRoleConfluent Cloud (free tier)Managed Kafka cluster and topicPython producer (Jupyter)Generates and publishes synthetic shipment eventsPython consumer (Jupyter)Consumes events and writes them into Neo4jNeo4j AuraDBGraph database storing the supply chain and shipment eventsPlotlyLive dashboard visualization One deliberate omission is that we aren't using the Neo4j Kafka Sink Connector, which is available as a managed connector on Confluent Cloud. That connector handles the consumer side automatically but carries a per-task hourly charge. For this article, we'll keep everything free by writing a Python consumer that does the same job. This also has a practical benefit: all the pipeline logic is visible in Python rather than hidden inside a managed connector configuration, which makes it easier to understand and adapt. The managed connector is a natural next step for production workloads. Setting Up Confluent Cloud Sign up at confluent.io and create a free cluster.Once the cluster is running, create a topic named shipment-events with 1 partition and default settings.Create an API key and secret under API Keys.Note the bootstrap server address from the cluster settings. Export these as environment variables in your shell: Shell export CONFLUENT_BOOTSTRAP_SERVERS=your_cluster.confluent.cloud:9092 export CONFLUENT_API_KEY=your_api_key export CONFLUENT_API_SECRET=your_api_secret Setting Up Neo4j AuraDB AuraDB is Neo4j's fully managed cloud database. A free tier is available with no credit card required. Sign up at console.neo4j.io/graphacademy.Create a new AuraDB Free instance.When the instance is created, download or note the credentials — the connection URI, username, and password. Neo4j only shows the password once, so save it somewhere safe.Once the instance is running, open the built-in Query tab and verify connectivity: MATCH (n) RETURN count(n). This should return 0. We are ready to load data. 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_here export NEO4J_PASSWORD=your_password_here export NEO4J_DATABASE=your_database_name_here The Data Model Each shipment event represents a single status update for a shipment at a point in time. A shipment does not generate a sequence of events as it progresses — each event is an independent snapshot, which keeps the producer simple and the consumer stateless. The event structure is: JSON { "shipment_id": "c60eb761-f153-4840-8427-17fa9e34c56c", "supplier_id": "S013", "warehouse_id": "W005", "dist_center_id": "DC004", "retailer_id": "R025", "status": "delayed", "timestamp": "2026-08-04T12:57:15Z", "delay_minutes": 34 } Status follows one of four values — departed, in_transit, delayed or delivered, with a configurable delay probability. We use 15% delayed to make the dashboard interesting without overwhelming it. When the consumer writes an event into Neo4j, it creates a Shipment node and links it to the existing supply chain nodes via four relationship types: Cypher MERGE (sh:Shipment {shipment_id: $shipment_id}) SET sh.status = $status, sh.timestamp = $timestamp, sh.delay_minutes = $delay_minutes WITH sh MATCH (s:Supplier {id: $supplier_id}) MATCH (w:Warehouse {id: $warehouse_id}) MATCH (dc:DistributionCenter {id: $dist_center_id}) MATCH (r:Retailer {id: $retailer_id}) MERGE (s)-[:HAS_SHIPMENT]->(sh) MERGE (sh)-[:VIA_WAREHOUSE]->(w) MERGE (sh)-[:VIA_DIST_CENTER]->(dc) MERGE (sh)-[:DESTINED_FOR]->(r) MERGE on shipment_id means re-running the consumer never creates duplicate nodes. The Producer The producer notebook uses a fixed random seed to generate reproducible shipment events using IDs drawn from the existing supply chain and publishes them to Confluent Cloud via the confluent-kafka library: Python producer = Producer({ "bootstrap.servers": BOOTSTRAP_SERVERS, "security.protocol": "SASL_SSL", "sasl.mechanisms": "PLAIN", "sasl.username": API_KEY, "sasl.password": API_SECRET, "log_level": 0, }) Setting "log_level": 0 suppresses the librdkafka telemetry messages that appear otherwise. The producer supports both batch and continuous modes. For example: Python produce_events(num_events = -1) # stream continuously produce_events(num_events = 100) # publish exactly 100 events The display refreshes every PRINT_EVERY events using clear_output, showing the latest event and a running status breakdown — so the cell output stays manageable even when streaming thousands of events. The Consumer and Live Dashboard Rather than two separate notebooks, we combine the consumer and dashboard into a single pipeline. On each cycle, the loop: Polls Kafka for up to POLL_BATCH events and writes them to Neo4jQueries Neo4j for the current graph stateRebuilds and redraws the dashboardSleeps for REFRESH_INTERVAL seconds before repeating Rebuilding the full dashboard on every cycle is straightforward and works well at demo event rates. At higher throughput, a more efficient approach would be to update only the changed data rather than redrawing all eight panels on each refresh. The consumer uses its own Kafka group ID (supply-chain-dashboard) so it reads the topic independently, catching up on all existing events first before staying live: Python consumer = Consumer({ "bootstrap.servers": BOOTSTRAP_SERVERS, "security.protocol": "SASL_SSL", "sasl.mechanisms": "PLAIN", "sasl.username": API_KEY, "sasl.password": API_SECRET, "group.id": "supply-chain-dashboard", "auto.offset.reset": "earliest", "log_level": 0, }) The Live Dashboard The dashboard uses Plotly's make_subplots in a 4x2 grid, rebuilt on every refresh cycle using clear_output. Eight panels give a complete picture of network health: Row 1 – Overall Health Network status table: Total shipments, delayed count, delay rate, Kafka events consumed, refresh count, and any disabled nodesShipment status distribution: Donut chart showing the split between departed, in transit, delayed, and delivered, as shown in Figure 1 Figure 1. Shipment Status Distribution Row 2 – Warehouse View Delayed shipments by warehouse: Which warehouses are handling the most delayed shipments right nowWarehouse health score: A heatmap scoring each warehouse from 0.0 (everything delayed) to 1.0 (fully healthy), colored red through orange to green, as shown in Figure 2 Figure 2. Warehouse Health Score Row 3 – Origin and Destination Supplier performance: Which suppliers are generating the most delayed shipmentsRetailer impact: Which retailers are receiving the most delayed shipments — the downstream effect of any disruption Row 4 – Mid-Network and Flow Average delay by distribution center: Where in the middle layer delays are accumulatingShipment flow: A Sankey diagram (Figure 3) showing which suppliers are routing through which warehouses Figure 3. Shipment Flow - Suppliers to Warehouses The warehouse health score is the most immediately readable panel. The Cypher behind it computes the score directly in the graph: Cypher MATCH (sh:Shipment)-[:VIA_WAREHOUSE]->(w:Warehouse) WHERE w.active IS NULL OR w.active <> false WITH w.id AS warehouse, count(sh) AS total, count(CASE WHEN sh.status = 'delayed' THEN 1 END) AS delayed RETURN warehouse, round(1.0 - toFloat(delayed) / total, 3) AS health_score ORDER BY warehouse Simulating a Network Disruption One of the more compelling features of the graph model is how easy it is to simulate and visualize a disruption. Setting active = false on any node excludes it from the dashboard queries and the dashboard immediately reflects the simulated disruption on the next refresh cycle. We can do this before the dashboard starts: Python REMOVE_NODE = "W007" # mark this warehouse as inactive Or live, while the dashboard is running, using the Neo4j AuraDB Query tab: Cypher // Disable a node MATCH (n {id: "W007"}) SET n.active = false // Re-enable a node MATCH (n {id: "W007"}) REMOVE n.active // Check what is currently disabled MATCH (n) WHERE n.active = false RETURN labels(n)[0] AS label, n.id AS id Within 5 seconds, the dashboard reflects the change. The warehouse health heatmap shows the gap, the delayed shipments bar shifts to other warehouses as traffic reroutes, and the network status table shows the node as disabled. Re-enabling it and watching the metrics recover completes the disruption and recovery story. Standalone Operation At startup, the consumer notebook creates the supply chain nodes using MERGE. This operation is idempotent, so any existing nodes from the previous article are left unchanged. Note that this step creates nodes only — the relationships between supply chain nodes (supplier -> warehouse -> distribution center -> retailer) are assumed to exist from the previous article, or can be added separately if running this notebook in isolation. Python with driver.session(database = NEO4J_DATABASE) as session: for i in range(20): session.run("MERGE (:Supplier {id: $id})", id = f"S{i:03d}") for i in range(12): session.run("MERGE (:Warehouse {id: $id})", id = f"W{i:03d}") for i in range(10): session.run("MERGE (:DistributionCenter {id: $id})", id = f"DC{i:03d}") for i in range(30): session.run("MERGE (:Retailer {id: $id})", id = f"R{i:03d}") Gotchas and Lessons Learned Suppress librdkafka Logging Without "log_level": 0 in the producer and consumer config, Confluent's underlying librdkafka library prints telemetry messages to the cell output every time a connection is established. The messages are harmless. Suppress Neo4j Property Warnings Querying a property that does not yet exist on any node produces a GqlStatusObject warning from Neo4j for every query that references it. The active property falls into this category when no node has been disabled. The fix is one line to set notifications to "OFF" on the driver, as follows: Python driver = GraphDatabase.driver( NEO4J_URI, auth = (NEO4J_USERNAME, NEO4J_PASSWORD), notifications_min_severity = "OFF", ) Consumer Group Isolation Kafka distributes partitions across consumers in the same group, so each consumer processes only its assigned partitions. If we run multiple consumers using the same group ID against the same topic, each will only process a subset of the events. The dashboard uses supply-chain-dashboard as its group ID, and the tip is to run only one instance of this notebook at a time against the same topic and cluster. auto.offset.reset = earliest Without this setting, a consumer that starts after events have been published will miss everything that arrived before it connected. Setting earliest means the consumer always catches up on the full history of the topic before going live, which is essential if we stop and restart the dashboard mid-session. Clear Shipment Nodes Between Runs Each run of the consumer creates new Shipment nodes. Since the producer generates synthetic demo data, it's safe to clear these between runs; otherwise, successive runs would accumulate all historical shipments, and the dashboard counts would grow unbounded. The notebook clears all Shipment nodes at startup: Cypher MATCH (sh:Shipment) CALL (sh) { DETACH DELETE sh } IN TRANSACTIONS OF 10000 ROWS Summary We've built a real-time supply chain event streaming pipeline using Confluent Cloud Kafka and Neo4j. The producer generates synthetic shipment events continuously, the consumer writes them into the graph, and a live dashboard shows network health updating in near real-time. The disruption simulation — marking a node inactive mid-run and watching the dashboard respond — demonstrates one of the most compelling aspects of the graph model: the ability to ask structural questions about a network as it evolves. The same architecture adapts naturally to real logistics, IoT, or manufacturing event streams where understanding network structure matters as much as raw throughput. The full source code is available on GitHub.

By Akmal Chaudhri CORE