标签：#base

COUNT looks like the simplest function in SQL, and it is the one that quietly trips up the most people in interviews and on the job. The confusion is almost always the same: COUNT(*) , COUNT(column) , and COUNT(DISTINCT column) look nearly identical but count three different things. Once you can say out loud what each one counts, a lot opens up. You can verify a data migration, find duplicates, and measure how complete a column is, all with the same little function. This guide is that explanation, with lots of small examples you can copy. The one-sentence version. COUNT(*) counts rows . COUNT(column) counts rows where that column is not NULL . COUNT(DISTINCT column) counts how many different non-NULL values that column has. Everything below is just that sentence, slowed down. The three forms of COUNT and what each one counts Picture one small table, customers , with a region column where two rows were never filled in: id name region 1 Maya North 2 Jordan South 3 Alex North 4 Sam NULL 5 Taylor NULL Now run the three forms on it: SELECT COUNT(*) AS all_rows, COUNT(region) AS rows_with_region, COUNT(DISTINCT region) AS different_regions FROM customers; all_rows rows_with_region different_regions 5 3 2 COUNT(*) = 5. Every row, no exceptions. The * means "the row itself," so NULLs never matter. COUNT(region) = 3. Only the rows where region has a value. Sam and Taylor are skipped because their region is NULL. COUNT(DISTINCT region) = 2. The different values are just North and South . The two Norths collapse to one, and NULL is not counted. The NULL rule that makes them disagree Predict it first. A table has 100 rows. Twenty of them have no email address. What does counting the email column give you? Say the number before you read on. Here is the whole trick in one line: COUNT(*) counts rows. COUNT(something) counts non-NULL values of that something. So the moment a column has any NULLs, COUNT(column) comes back smaller than COUNT(*) . That gap is not a bug, it is informat

I have been building a thing that lets a language model propose an UPDATE , then executes it for real inside a transaction, measures the actual before and after values, and always rolls back. A human reads the measurement and decides. Only then does anything commit. The pitch is one sentence: what you approve is not the model's description of its SQL, it is what the database did when the SQL ran. Last week I found that the thing showing you that measurement was showing you a subset of it, and had been since the first release. The failure Real output, from @hyuga/llm-safe-sql@0.4.0 installed from npm. One row: name = 'Tanaka' , postcode = '00100' . UPDATE customers SET name='Sato', postcode='00100' WHERE id=1 What this touches customers — Customer records. The postcode is used for billing address and delivery. 1 row would change, across 1 column: name Measured by running the statement and rolling it back id = 1 name: 'Tanaka' -> 'Sato' One row, one column. postcode is not mentioned, and that is correct — it is being assigned the value it already holds, so nothing about it changes. The card is describing the diff accurately. Approve it. Then, before it is applied, somebody else notices the postcode is wrong and fixes it: UPDATE customers SET postcode = '90210' WHERE id = 1 ; Now apply the approved plan: Applied: UPDATE on customers, 1 row(s), at 2026-08-10T09:49:12.049Z. DB now: [{"name":"Sato","postcode":"00100"}] The fix is gone. Zero warnings. The word postcode never appeared on the approval card, never appeared in the audit record, and never appeared in the comparison the tool makes before it commits. One variable doing two jobs The diff was built like this: const changed : string [] = []; for ( const c of Object . keys ( before )) { if ( same ( before [ c ], after [ c ])) continue ; // drop what did not move if ( auto . has ( lower ( c ))) continue ; // drop what the DB maintains itself changed . push ( c ); } That is a correct answer to "what should the card sho

In Blog 5, we built a SQL parser. It can take this: INSERT INTO payments VALUES ( 500 , payment_1 , pending , 1 ) and turn it into a struct: InsertIntoTable { TableName : "payments" , ColumnValues : [] string { "500" , "payment_1" , "pending" , "1" }, } But this is still not enough for the storage engine. Our storage engine only knows how to store key-value pairs. It does not know what a table is. It does not know what a column is. It does not know that 500 is an integer, pending is a string, and 1 is a boolean. So, in this post we solve the missing bridge of persisting these in our key-value store. CREATE and INSERT are PUT operations This is the first major realisation. A key-value store is extensible to store literally anything. This is what we have been saying from the first post itself. But now we will be taking actual examples to prove that. CREATE TABLE Example Let's start with the create table example and see what should be the key and the value. Serialisation The key should be something that uniquely identifies the table, which is straightforward enough in this case as the table name . The value becomes everything else except the key, which is the schema of the table. So, in order to store the table name, we can append a reserved keyword as prefix like schema as a unique identifier. The structure of the key becomes _schema:<table_name> . The next question to answer is: How do we store a struct like below into our key value store where the value is always string? CreateTable { TableName : "payments" , ColumnDetails : [] Column { { ColumnName : "amount" , DataType : Int }, { ColumnName : "id" , DataType : String }, { ColumnName : "status" , DataType : String }, { ColumnName : "captured" , DataType : Bool }, }, PrimaryKeyColumnPosition : 1 , } One way is to serialise the entire struct into a string and store that directly. But in that case, deserialisation is a complex logic. JSON or struct serialisation and deserialisation is both space-heavy and compute inte

Every API that sits behind an unreliable network eventually faces the same problem: a client sends a request, the connection drops before the response arrives, and the client has no idea whether the operation happened. Did the payment go through? Did the order get created twice? The client's only safe move is to retry — which means your server needs a story for what happens when the same "create this thing" request arrives more than once. That story is idempotency keys, and getting the details right is more subtle than it first looks. The core idea The client generates a unique token — typically a UUID — once per logical operation, and attaches it to every retry of that operation: POST /orders Idempotency-Key: 7c3fd9a2-df01-4b3e-9a55-1e5f9b6b6d55 {"sku": "WIDGET-1", "qty": 2} The server's job is to guarantee that no matter how many times a request with that key arrives, the side effect (charging a card, creating an order, sending an email) happens at most once, and every retry gets back the same response the original request would have produced. Note what this is not: it is not deduplicating by request body. Two requests with identical bodies but no key are legitimately two different orders for two widgets. The key is what marks them as "the same attempt," not the payload. The naive approach, and why it breaks A common first pass is a table like: CREATE TABLE idempotency_keys ( key TEXT PRIMARY KEY , response_body JSONB , status_code INT ); On each request: check if the key exists, and if so return the cached response; otherwise do the work and insert the result. This looks right and is wrong in a specific way: it has a race condition. Two retries can arrive concurrently (a client that timed out and fired a second attempt while the first was still in flight), both miss the cache check, and both execute the underlying operation. You've now charged the card twice. Making the check-and-do atomic The fix is to claim the key before doing the work, using the database's ow

Migrating from MongoDB Atlas to a self-hosted replica set bought us control and cut our bill. It also quietly removed something we had stopped thinking about: Atlas had been taking continuous backups for us the entire time. After the migration, production data for Prochesta lived in /var/db/mongo on a single VPS. No snapshots. No off-box copy. A rm -rf , a bad migration script, or a dead disk would have been the end of it. We had written "backups" as a follow-up task in the migration spec, which is the engineering equivalent of a sticky note on a bank vault. The requirement we actually cared about was narrower than "back up the database". Most real-world data loss at our scale isn't hardware failure — it's a deploy that writes garbage, or someone running an update without a filter. Recovering to last night doesn't help when the damage happened at 14:20 and you noticed at 14:50. We needed to recover to an arbitrary moment , not to a nightly snapshot. The constraint nobody mentions: Community has no $backupCursor We chose Percona Backup for MongoDB (PBM), and immediately hit the limitation that shapes every decision downstream. PBM offers physical backups — fast file-level copies that restore in minutes and barely touch the running server. They work by opening a backup cursor via the $backupCursor aggregation stage. That stage exists in Percona Server for MongoDB and in MongoDB Enterprise. It does not exist in MongoDB Community, which is what the official mongo:8.0 image ships. So on Community, PBM gives you logical backups only: every document read out through mongod , compressed, and shipped off-box. Two consequences, both accepted deliberately rather than discovered later: Backups cost CPU on the primary — and with a single-member replica set there's no secondary to offload the read to. Restores insert documents and rebuild indexes, so restore time grows with data size much faster than backup time does. At our current size that's minutes, not hours. It's also the t

The engineering team at Stripe recently described how they automated database incident recovery by modeling their global infrastructure as a graph. Using graph search algorithms together with state machines, the team computes and executes remediation plans automatically. By Renato Losio

When building a nested reply system, most developers worry about deep tree complexity or messy data structures. For Vlox , I took a different approach: keeping things flat, fast, and secure by reusing a single Mongoose schema with smart atomic limits. Here is a deep dive into how I engineered a production-ready, race-condition-safe reply mechanism using MongoDB transactions, strict type sanitization, and automated limits. How It Works 🛠️ User Action: A user clicks the reply icon and submits their reply. The Payload: Vlox's system sends 3 fields via the endpoint /api/v1/reply/comment/post/:id : id : The post ID (passed as a URL parameter). rootCommentId : The ID of the root comment being replied to. reply : The raw text entered by the user. Sanitization & Validation: The incoming reply is instantly converted to a trimmed string. It then passes through two critical validation checks: Existence Check: The reply must exist. (If a malicious actor sends a payload without a body, the string literally evaluates to "undefined" and gets blocked). Length Limit: The reply must be under 201 characters, enforcing the standard comment limit. Atomic Transactions: If the validation checks pass, the system initiates a Mongoose transaction to execute the following steps safely: Permission Check: It verifies if the user has permission to reply by checking the post's status via await schemas.Posts.findOne(hotQueries.find_user_post(id, req.session.userId)); . Creation: If permissions are valid, it creates a new reply. (Fun fact: It reuses the exact same schema as standard comments!) The Reply Schema Structure: The reply object functions just like a normal comment, with two distinct exceptions: It does not contain a repliesCount field. It includes an extra rootId field, which explicitly points to the ID of the root comment being replied to. Concurrency & Caps: To guarantee that a single comment never receives more than 10 replies while simultaneously incrementing the counter, the system r

After months of development, I'm excited to share DBNavigator, a cross-platform database IDE that I've been building from scratch using Java and JavaFX. ✨ Current Features ✅ PostgreSQL support ✅ MySQL support ✅ Modern Datagrip-inspired UI ✅ Multi-tab SQL editor ✅ Syntax highlighting ✅ Schema explorer ✅ Query execution ✅ Professional dark theme ✅ Cross-platform (Windows, Linux & macOS) This project has been an incredible learning journey in desktop application development, JavaFX UI design, database connectivity, and IDE architecture. I'm sharing it with the developer community because I'd genuinely appreciate your honest feedback. I'd love to hear your thoughts on: UI/UX design Performance Missing features Overall developer experience Architecture and code quality Any bugs or improvements you notice Whether you're a Java developer, DBA, or someone who works with databases every day, your feedback would mean a lot and help shape the next version of the project. ⭐ If you find the project interesting, please consider giving it a star on GitHub. GitHub: DBNavigator Thank you for taking the time to review it. Every suggestion, issue report, and critique is greatly appreciated! 🙌

I have been building machin for a while — a Go-flavored, type-inferred language that compiles through C to a single native binary. It has grown a lot recently, and I wanted to answer the obvious question honestly: does it beat Rust and Zig at anything? It does, at two things, decisively. But the first thing I found was not a win. It was my own benchmark quietly lying to me, and the number it was lying about was the best one I had. The benchmark was measuring the order I ran things in machin's repo has had a bench/native-speed suite for months: four compute kernels — recursive fib, a mandelbrot, a sieve, a big integer loop — written in machin, Rust and Zig, producing byte-identical output, so the timing compares the same computation three ways. The published result claimed machin won the integer loop by 20-25% . That claim also shipped inside machin guide , which is what every coding agent reads to learn the language. When I re-ran it, the margin was gone. Not shrunk — gone. So I read the harness instead of the output: for kernel in kernels : for lang in [ machin , rust , zig ]: for _ in range ( 5 ): # all 5 machin, THEN all 5 rust, THEN all 5 zig time ( binary ) It ran every sample of one language before starting the next. On a laptop that heats up and down-clocks during a three-second kernel, that does not measure the languages. It measures who had the misfortune of running last . Zig always went last. Zig always looked slowest. The fix is four lines — interleave the rounds, rotate who starts each one. Here is what my headline number did: intsum 10^9 before (blocked) after (interleaved) machin 2832 ms 3079.7 ms rust 3764 ms 3223.8 ms zig 3556 ms 3189.7 ms "machin +20-25%" machin +3% = a TIE A 20-25% win became a tie. I deleted the claim from the README and from machin guide . The harness now also refuses to declare a winner inside a 3% band, because the worst run-to-run spread I measured was 41% of the min sample. Calling winners inside that is how benchmarks start

In modern Data Engineering, there is an unspoken fetish for "Real-Time." If you ask any business stakeholder how fast they need their dashboard to update, the default answer will always be: "As fast as possible." This drives well-intentioned engineers to design incredibly complex architectures. We spin up Kafka clusters, implement Flink, and wrestle with latency, late-arriving data, and tumbling windows. All to have data flowing in milliseconds. But the harsh reality is that the vast majority of companies are building Ferraris just to sit in rush-hour traffic. 1. The Actionability Gap (The Golden Question) The biggest mistake when choosing a streaming architecture isn't technical; it's a business mistake. Before implementing real-time pipelines, the only question that matters is: "Does the company have the operational capacity to make a decision in milliseconds?" If you are building a credit card fraud detection system or a live e-commerce recommendation engine, yes, every millisecond counts. But if the data is feeding a financial dashboard that the executive board only reviews during their Monday morning meeting, updating that screen every second is a colossal waste of money and effort. Real-time data has zero value if the human action is batch. 2. The Hidden Complexity and the Cloud Bill Batch processing is forgiving. If a pipeline fails at 3 AM, you trigger a rerun, and by 8 AM, everything is fine. Batch is cheap, predictable, and easy to debug. Streaming, on the other hand, is unforgiving. Handling application state, event duplication (exactly-once semantics), out-of-order events, and sudden traffic spikes requires a senior engineering team dedicated solely to keeping the infrastructure alive. Furthermore, the cloud bill for 24/7 continuous processing is orders of magnitude higher than spinning up your compute clusters on a schedule. 3. "Micro-Batch" Solves 99% of Your Problems There is a perfect middle ground that the hype industry tries to ignore: the micro-ba

GitHub malware advisories no longer stop at npm. Here's how we wired OpenSSF's malicious-packages data into the Advisory Database, and why we built the pipeline paranoid. The post How we took malware advisories beyond npm appeared first on The GitHub Blog .

If your files already live in Amazon S3, the pitch for storage that branches with your database is appealing but the word "migration" makes it sound like a project. It mostly is not. Neon's object storage speaks the S3 API, so the code you already wrote, the AWS SDK calls and presigned URLs, keeps working. What changes is how you point the client and where the bucket comes from, and that is a small, mechanical diff. The actual data move is a copy loop you can run once. The one thing to do up front is confirm the object operations your app actually relies on: the demo here exercises PutObject , GetObject , listing, and presigned URLs, and I flag the S3 features you should check for yourself further down. This post is the practical version: what stays identical, the exact config that changes, a script to copy the objects across, and an honest list of the S3 features that do not have an equivalent so you know what to check before you commit. The repo with the working client is at the end. TL;DR Neon object storage is S3-compatible. Your @aws-sdk/client-s3 code for the common operations, PutObject , GetObject , getSignedUrl , listing, works unchanged (these are what the demo verifies). Confirm anything beyond that, like multipart for large objects, against the current preview. The diff is the client config: point endpoint at the Neon storage endpoint, pin region: 'us-east-2' , set forcePathStyle: true . The bucket is declared in neon.ts instead of created in the console, and credentials are injected per branch. Move the data with a list-and-copy loop between two S3 clients (source AWS, destination Neon). What does not carry over: S3 bucket policies, event notifications and Lambda triggers, storage classes and Glacier transitions, and cross-region replication. Object CRUD and presigning do. The payoff is everything else in this series: once the files are on Neon, they branch with your database. Prerequisites An existing S3 bucket and credentials that can read it A Neon p

If your app stores files and you want real preview environments, you eventually hit the same wall: each preview needs its own storage, so you start provisioning a bucket per environment. That sounds cheap until you write it down. For every ephemeral environment you create a bucket, attach a policy, mint an IAM role or access keys, set CORS, add a lifecycle rule so it eventually cleans up, wire the credentials into the preview's config, and register a teardown step for when the PR closes. Then you find the orphaned buckets the teardown missed, months later, still billing. The reason this is painful is that the bucket is a separate resource from the database, so it needs its own lifecycle. Neon collapses that: the bucket is declared as part of the branch, so it is created and destroyed with the branch and needs no per-environment provisioning at all. This post compares the two approaches and shows the branch version working with no bucket-management code in sight. The repo is at the end. TL;DR Isolated storage per preview usually means provisioning a bucket per environment: policy, IAM, CORS, lifecycle, credential wiring, teardown. It is slow, it drifts, and it leaves orphaned buckets that keep costing money. On Neon the bucket is declared once in neon.ts . Creating a branch brings the bucket (with a copy-on-write copy of the files) and injects scoped credentials; deleting the branch removes it. There is no per-environment bucket to create, no IAM role to mint, and nothing to orphan. Copy-on-write means fifty preview buckets do not cost fifty times the storage, only what each one changes. Prerequisites A Neon project on the platform preview (object storage, us-east-2 ) The Neon CLI, and a CI system that opens/closes preview environments Familiarity with S3 buckets and IAM if you have done the manual version The per-environment bucket, written out Here is what "just give the preview its own bucket" actually expands to, per environment: Create a bucket with a unique nam

The naive way to accept file uploads is to POST them to your API, let the server read the bytes, and write them to object storage. It works until the files get large or the traffic gets real. Now every upload crosses your infrastructure twice, once from the client to your server and once from your server to storage, and your server holds the whole file in memory or on disk while it does. On a serverless function it is worse, because functions have request-size and duration limits that a big upload runs straight into. Presigned URLs are the standard fix, and they predate serverless by a decade. Your server does not move the bytes; it hands the client a short-lived, pre-authorized URL and the client uploads directly to object storage. The server only issues permission and records metadata. On a Neon Function this is the same AWS S3 SDK you already use, pointed at the branch's storage endpoint. This post builds it and tests the whole round trip. The repo is at the end. TL;DR Proxying uploads through a function sends the bytes across it, burning bandwidth and memory and hitting request-size limits. A presigned URL is a time-limited, pre-authorized link to one object key. The client PUTs the bytes straight to storage; the function never touches them. On Neon Functions you generate it with getSignedUrl from @aws-sdk/s3-request-presigner , the same code as any S3-compatible store. I tested the full flow: presign, the client PUT straight to storage returned 200 , a metadata record was saved, and downloading the object returned the exact bytes. One gotcha to pin: the injected AWS_REGION is the storage-cell host, not a region, so set region: 'us-east-2' on the client. Prerequisites A Neon project on the platform preview with a declared bucket (object storage, us-east-2 ) The AWS SDK: @aws-sdk/client-s3 and @aws-sdk/s3-request-presigner Familiarity with S3-style object storage and HTTP PUT Why not just proxy the upload Sending the file through the function has three costs that

Wiz Research disclosed CosmosEscape, a chain that escaped Azure Cosmos DB's Gremlin sandbox and reached a platform-wide key granting read and write access to every database on the service. Microsoft blocked the entry point within two days but took until July 2026 to remove the key. Practitioners debated shared responsibility and what that rearchitecture actually cost. By Steef-Jan Wiggers

Baseboard management controllers from the world's biggest manufacturers are a security mess.

The query every backend developer has needed and nobody enjoys writing In March 2016, npm removed an 11-line package called left-pad. Within minutes, builds began failing across the JavaScript ecosystem. It broke thousands of projects, including tools like Babel. Many developers didn't choose left-pad directly; it was hidden in their dependencies and went unnoticed until it vanished. That incident points at a question you have probably asked about your own stack: what is actually in my dependency tree? Not just the 30 packages in your package.json . Everything they pull in, and everything those pull in, all the way down. In a relational database, dependencies live in a self-referencing join table. "Everything, all the way down" means a recursive CTE. Here is that query on a snapshot of the npm registry. It finds the full runtime dependency tree of express : WITH RECURSIVE closure ( name , depth ) AS ( SELECT depends_on_name , 1 FROM dependencies WHERE package_name = 'express' AND dep_type = 'runtime' UNION SELECT d . depends_on_name , c . depth + 1 FROM closure c JOIN dependencies d ON d . package_name = c . name AND d . dep_type = 'runtime' ) SELECT count ( DISTINCT name ) AS transitive_deps , max ( depth ) AS max_depth FROM closure ; Here is the result: It works. Here it shows 63 packages, 11 levels deep. But it is twelve lines, and every line matters. There is an anchor part, a recursive part, and a UNION doing quiet work to remove duplicates. A graph asks the same question in two lines: MATCH ( :Package { name: 'express' }) - [ :DEPENDS_ON * ] -> ( dep ) RETURN count ( DISTINCT dep ) AS transitive_deps That is not a shortened excerpt. That is the whole query. It returns the same 63 packages. Cypher is Neo4j's query language. For a SQL developer, it is less a new language than a new notation for questions you already know how to ask. This article proves that claim with ten translations. They run from "this is just SQL with arrows" up to the query above, plus one

Exactly a year ago we released SurrealMCP, a server you had to install and run in order to connect...

Step-by-step Xero API integration: OAuth 2.0, tenant routing, paging, rate limits, the 2026 scope and pricing changes, plus a no-code path to PostgreSQL. By Ilshaad Kheerdali · 4 August 2026 The Xero API is well documented and pleasant to work with once it clicks, but the first integration always takes longer than people expect. There is an extra discovery step that most accounting APIs don't have, tokens expire faster than you'd guess, and 2026 brought two changes that alter how you scope and budget an integration. This guide walks the whole flow: creating an app, running OAuth 2.0, resolving which organisation you're actually talking to, making your first call, paging through results, staying inside the rate limits, and pulling incremental updates. At the end it covers what changed in 2026 and the shortcut if the plumbing isn't the part you want to own. Everything below targets the Xero Accounting API over OAuth 2.0. Xero retired OAuth 1.0a some years ago, so any tutorial you find that mentions consumer keys and signed requests is out of date. What the Xero API Is The Xero Accounting API is a REST API that returns XML by default and JSON if you ask for it. You read and write accounting entities: Invoices , Contacts , Payments , BankTransactions , Accounts , CreditNotes , Items , PurchaseOrders , ManualJournals and a few dozen more, plus a set of report endpoints. The thing that surprises most developers coming from Stripe or QuickBooks is the tenant model . A single Xero login can have access to many organisations: an accountant might be connected to two hundred client orgs. So authorisation and targeting are two separate concerns. Your token proves the user said yes, and a separate header tells Xero which organisation the call is for. That means every integration has a step that a Stripe integration simply doesn't: after you get a token, you have to ask Xero which tenants that token can reach. Step 1: Create a Xero App Sign in at the Xero Developer portal and cre

A generated image looks finished until review starts. Someone approves the first version. Someone else crops it. A branded copy goes out. Another edit changes the prompt. A week later, the useful question is simple: which prompt, model, seed, size, parent image, and publishing settings produced the version on screen? In a content studio, I put those answers in the PostgreSQL row that stores the image. Logs explain what happened during a run, then rotate away. Object storage keeps the bytes and forgets why they exist. The row is the only one of the three that survives edits, review, and publishing. 1. The row is the receipt The table in apps/api/src/database/init-ai-images-table.js treats generated and edited images as one record type. An original image gets its own row. An edit gets another row, with original_image_id pointing back to the parent. CREATE TABLE IF NOT EXISTS ai_generated_images ( id SERIAL PRIMARY KEY , image_url TEXT NOT NULL , -- what produced it prompt TEXT NOT NULL , model VARCHAR ( 100 ) DEFAULT 'fal-ai/imagen4' , model_version VARCHAR ( 100 ), seed BIGINT , width INTEGER , height INTEGER , -- how it derives from another row is_edited BOOLEAN DEFAULT FALSE , original_image_id INTEGER REFERENCES ai_generated_images ( id ), edit_prompt TEXT , edit_strength DECIMAL ( 3 , 2 ), -- what actually shipped branded_url TEXT , branding_options JSONB , metadata JSONB , tags TEXT [], created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); That self-reference is the design choice. It makes the image table append-only-ish: new variants are inserted as new rows instead of overwriting the earlier state. The cost is more rows and more discipline at write time. The benefit is editable history that product screens and debugging queries can follow. flowchart TD original["Original row: prompt, model, seed, dimensions"] editA["Edited child: edit_prompt, edit_strength, edit_steps"] editB["Edited child: edit_prompt, edit_guidance_scale"] brandedA["Branded output: branded_url,