1. Nested database transactions could exhaust the transaction pool and deadlock 2. Same as you described with doing eg HTTP during transactions

We now have a compile time guarantee that no IO can be done outside of whitelisted things, like logging or getting the current time. It’s worked great! Definitely a good amount of work though.

I had this realization while writing the rustc plugin that this is basically another shade of “function coloring”, but done intentionally. Now I wish I could have a language that lets me intentionally “color” my functions such that certain functions can only be called from certain blessed contexts… not unlike how async functions can only be awaited by other async functions, but for arbitrary domain-specific abstractions, in particular database connections in this case. I want to make it so HTTP calls are “purple”, and any function that gets a database connection is “pink”, and make it so purple can call pink but not vice-versa.

The rule I ended up with in the lint, is basically “if you have a connection in scope, you can only .await a function if you’re passing said connection to that function” (either by reference or by moving it.) It works with rust’s knowledge of lifetimes and drop semantics, so that if you call txn.commit() (which moves the connection out of scope, marking the storage as dead) you’re now free to do unrelated async calls after that line of code. It’s not perfect though… if you wrap the connection in a struct and hold that in your scope, the lint can’t see that you’re holding a connection. Luckily we’re not really doing that anywhere: connections are always passed around explicitly. But even if we did, you can also configure the lint with a list of “connection types” that will trigger the lint.

Also, your idea of using different domain specific colors is interesting. It might be possible to express this via some kind of effect system. I'm not aware of any popular Rust libraries for that, but it could be worth borrowing some ideas from Scala libraries.

* It's obvious from the schema: If there's a `deleted_at` column, I know how to query the table correctly (vs thinking rows aren't DELETEd, or knowing where to look in another table)

* One way to do things: Analytics queries, admin pages, it all can look at the same set of data, vs having separate handling for historical data.

* DELETEs are likely fairly rare by volume for many use cases

* I haven't found soft-deleted rows to be a big performance issue. Intuitively this should be true, since queries should be O log(N)

* Undoing is really easy, because all the relationships stay in place, vs data already being moved elsewhere (In practice, I haven't found much need for this kind of undo).

In most cases, I've really enjoyed going even further and making rows fully immutable, using a new row to handle updates. This makes it really easy to reference historical data.

If I was doing the logging approach described in the article, I'd use database triggers that keep a copy of every INSERT/UPDATE/DELETEd row in a duplicate table. This way it all stays in the same database—easy to query and replicate elsewhere.

All your other points make sense, given this assumption.

I've seen tables where 50%-70% were soft-deleted, and it did affect the performance noticeably.

> Undoing is really easy

Depends on whether undoing even happens, and whether the act of deletion and undeletion require audit records anyway.

In short, there are cases when soft-deletion works well, and is a good approach. In other cases it does not, and is not. Analysis is needed before adopting it.

That said, we've had soft-deletes and during discussions of keeping it on one argument was that it was really only a half-assed measure (data lost due to updates rather than deletes aren't really saved)

I think we largely need support for "soft deletes" to be baked into SQL or its dialects directly and treated as something transparent (selecting soft deleted rows = special case, regular selects skip those rows; support for changing regular DELETE statements into doing soft deletes under the hood).

https://news.ycombinator.com/item?id=43781109

https://news.ycombinator.com/item?id=41272903

And then make dynamically sharding data by deleted/not deleted really easy to configure.

You soft deleted a few rows? They get moved to another DB instance, an archive/bin of sorts. Normal queries wouldn't even consider it, only when you explicitly try to select soft deleted rows would it be reached out to.

This has, at least with current MariaDB versions, the annoying property that you really cannot ever again modify the history without rewriting the whole table, which becomes a major pain in the ass if you ever need schema changes and history items block those.

Maria still has to find some proper balance here between change safety and developer experience.

So a widespread, common and valid practice shouldn't be made better supported and instead should rely on awkward hacks like "deleted_at" where sooner or later people or ORMs will forget about those semantics and will select the wrong thing? I don't think I agree. I also don't think that it has much to do with how or where you represent the data. Temporal tables already do something similar, just with slightly different semantics.

Making those custom semantics (enabled at per-schema/per-table level) take over what was already there previously: DELETE doing soft-deletes by default and SELECT only selecting the records that aren't soft deleted, for example.

Then making the unintended behavior (for 90% of normal operational cases) require special commands, be it a new keyword like DELETE HARD or SELECT ALL, or query hints (special comments like /*+DELETE_HARD*/).

Maybe some day I'll find a database that's simple and hackable enough to build it for my own amusement.

At that point you should probably investigate partitioning or data warehousing.

The correct thing to do if your retention policy is causing a performance problem is to sit down and actually decide what the data is truly needed for, and if you can make some transformations/projections to combine only the actual data you really use to a different location so you can discard the rest. That's just data warehousing.

Data warehouse doesn't only mean "cube tables". It also just means "a different location for data we rarely need, stored in a way that is only convenient for the old data needs". It doesn't need to be a different RDBMS or even a different database.

I've seen a number of apps that require audit histories work on a basis where they are archived at a particular time, and that's when the deletes occurred and indexes fully rebuilt. This is typically scheduled during the least busy time of the year as it's rather IO intensive.

FWIW, no "indexes fully rebuilt" upon "actual deletion" or anything like that. The regular tables were always just "current" tables. History was kept in archive tables that were always up-to-date via triggers. Essentially, current tables never suffered any performance issues and history was available whenever needed. If history access was needed for extensive querying, read replicas were able to provide this without any cost to the main database but if something required "up to the second" consistency, the historic tables were available on the main database of course with good performance (as you can tell from the timelines, this was pre-SSDs, so multi-path I/O over fibre was what they had at the time I worked with it with automatic hot-spare failover between database hosts - no clouds of any kind in sight). Replication was done through replicating the actual SQL queries modifying the data on each replica (multiple read replicas across the world) vs. replicating the data itself. Much speedier, so that the application itself was able to use read replicas around the globe, without requiring multi-master for consistency. Weekends used to "diff" in order to ensure there were no inconsistencies for whatever reason (as applying the modifying SQL queries to each replica does of course have the potential to have the data go out of sync - theoretically).

Gee, I'm old, lol!

Depending on your use-case, having soft-deletes doesn't mean you can't clean out old deleted data anyway. You may want a process that grabs all data soft-deleted X years ago and just hard-delete it.

> Depends on whether undoing even happens, and whether the act of deletion and undeletion require audit records anyway.

Yes but this is no more complex than the current situation, where you have to always create the audit records.

(Again, a lot is O(log n) right?)

Another great (and older) approach is adding temporal information do your traditional database, which gives immutability without the eventual consistency headaches that normally comes with event sourcing. Temporal SQL has their own set of challenges of course, but you get to keep 30+ years of relational DB tooling which is a boon. Event sourcing is great, but we shouldn't forget about other tools in our toolbelt as well!

Also, it doesn't support non-immutable use cases AFAIK, so if you need both you have to use two database technologies (interfaces?), which can add complexity.

> Also, it doesn't support non-immutable use cases AFAIK

What do you mean? It's append only but you can have CRUD operations on it. You get a view and of the db at any point in time if you so wish, but can support any CRUD use case. What is your concern there?

It will work well if you're read-heavy and the write throughput is not insanely high.

I wouldn't say it's internally more complex than your pg with whatever code you need to make it work for these scenarios like soft-delete.

From the DX perspective is incredibly simple to work on (see Simple Made Easy from Rich Hickey).

(timestamp, accountNumber, value, state)

And then you just

SELECT state FROM Table WHERE accountNumber = ... ORDER BY timestamp DESC LIMIT 1

right?

Even with indices, a table with, let's say, a billion rows can be annoying to traverse.

I think one of our problems is getting users to delete stuff they don’t need anymore.

I'm pretty sure it is possible, and it might even yield some performance improvements.

That way you wouldn't have to worry about deleted items impacting performance too much.

But HOT updates are a thing, too.

HOT updates write to the same tuple page and can avoid updating indexes, but it's still a write followed by marking the old tuple for deletion.

I assume they typo'd "partitions" as "positions", and thus the GP comment was the correct reply.

Memory >>>>> Disk in importance.

I think this is likely unnecessary for most use cases and is mostly a RAM saving measure, but could help in some cases.

I usually tell people to stop treating databases like firebase and wax on/wax off records and fields willy nilly. You need to treat the database as the store of your business process. And your business processes demand retention of all requests. You need to keep the request to soft delete a record. You need to keep a request to undelete a record.

Too much crap in the database, you need to create a field saying this record will be archived off by this date. On that date, you move that record off into another table or file that is only accessible to admins. And yes, you need to keep a record of that archival as well. Too much gunk in your request logs? Well then you need to create an archive process for that as well.

These principles are nothing new. They are in line with “Generally Accepted Record Keeping Principles” which are US oriented. Other countries have similar standards.

I think they were using Agda or something too for a while, but it appears I can't find what I'm thinking of on their site anymore. Really interesting guys if you're located in the Baltic states.

You shouldn’t get the device verification requirement if you’ve used the device before (we store a permanent cookie to check this) or for the same IP. Any chance your cookies are being cleared regularly?

We added this after attackers created clones of http://mercury.com and took out Google ads for it. When customers entered their password and TOTP on the phishing site, the phisher would use their credentials to login and create virtual cards and buy crypto/gold/etc. The phisher would also redirect the user to the real Mercury and hope they figured it was a blip.

This device verification link we send authorizes the IP/device you open it on, which has almost entirely defeated the phishers.

Since WebAuthn is immune to this style of phishing attack, we don’t require device verification if you use it. I highly recommend using TouchID/FaceID or your device’s flavor of WebAuthn if you can—it’s more convenient and more secure. You can add it here: https://app.mercury.com/settings/security

That said, we are talking internally about your post and we do recognize that as IPv6 gets more traction IPs will rotate much more regularly, so we’ll think if we should loosen restrictions on being a same-IP match.

I wasn't aware that WebAuthn didn't have this requirement. I prefer TOTP because I actually like having a second factor in addition to a credential stored on my computer's hard drive (whether a password or a private key in my password manager), but I might be willing to reduce my security posture to get rid of this annoyance.

One suggestion: the link would be half as annoying if it was easily cut-and-pasteable rather than a long email-open-tracking link spanning multiple lines. This is what it looks like when I copy it out of my email:

  https://email.mg.mercury.com/c/eJxMzs1u4jAUBeCncXZB9vVfvPACZshoWIwYoiasdgkra2KV_JCGqPTpK-imq7xxx40vlO9IKia6ggL6zUlQHObdF6\
  JI0alRHBWQvWKRuD4loLZxsJSRXZAwfNBQeQWozasdgeWsMyFZozE4RKZ4d151NOFtuq9w6IqLb-d5fGdyzaBmUIdx_NkzqBeacrqXkZaMxGSNQyQmf7_9GW7\
  Hf1cJ8zW9TshAwwba3ccLuN3u_r_PR9j_GkxxxmadDu32c59jMfkYFmKKP0baIT0vzP4ynHN_-yyhZOTy9jmPPQn6gL-VLMfvvIA_XxbywRYhUbZUp0RpVCUC\
  qDsbasJHeObFMZ4YrFw1cAAAD__4XPZXw

Edit to add: looks like email.mg.mercury.com is hosted by Mailgun. Are you intentionally sharing these authentication tokens with a third party by serving them through this redirect? Do your security auditors know about this?

I've seen passkeys support something like what you're after. The browser will produce a QR code you scan with your phone, and then you authenticate with the passkey via the phone, which then authorizes the original browser.

I'm not absolutely certain that this is part of the spec or how it actually works. I'd like to know. It solves a couple different usability issues.

You could always use something like a Yubikey.

This is the option I prefer, but only on sites that allow me to enroll more than one device (primary, and backup for if the primary gets lost or damaged). AFAICT, Mercury only allows a single security key.

I have an encrypted offline backup of my TOTP codes, so if I drop my phone on the ground, I don't get locked out of all my accounts. I keep this separate from the encrypted offline backup of the password manager on my computer, and as far as I know, neither has ever been uploaded to anyone else's "cloud." Malware would have to compromise two completely separate platforms to get into my accounts, rather than just iCloud or whatever credentials.

I understand the desire for phish-proof credentials, but—given that I don't click links in emails—my personal threat model ranks a compromised device (via attack against a cloud service provider, or software supply chain attack against a vendor with permission to "auto-update," or whatever) much higher likelihood than me personally falling victim to phishing. I readily admit that's not true for everyone.

I completely understand that. I'd actually be interested in reading anything practical you might have on that topic if you don't mind. I asked some experts who gave a talk on supply chain security last year ... they didn't have a lot of positive things to say. Developing software feels like playing with fire.

The development environment where I'm downloading random libraries is on a completely separate physical machine than my primary computer. I generally spin up a short-lived container for each new coding project, that gets deleted after the resulting code I produce is uploaded somewhere. This is completely separate from the work-supplied machine where I hack on my employer's code.

On my primary computer, my web browser runs in an ephemeral container that resets itself each time I shut it down. My password manager runs in a different, isolated, container. Zoom runs in a different, also isolated, container. And so on.

Wherever possible, I avoid letting my computer automatically sync with cloud services or my phone. If one is compromised, this avoids spreading the contagion. It also limits the amount of data that can be exfiltrated from any single device. Almost all of the persistent data I care about is in Git (I use git-annex for file sync), so there's an audit trail of changes.

My SSH and GPG keys are stored on a hardware key so they can't be easily copied. I set my Yubikey to require a touch each time I authenticate, so my ssh-agent isn't forwarding authentication without a physical action on my part. I cover my webcam when not in use and use an external microphone that requires turning on a preamp.

I try to host my own services using open source tools, rather than trust random SaaS vendors. Each internet-facing service runs in a dedicated container, isolated from the others. IoT devices each get their own VLAN. Most containers and VLANs have firewall rules that only allow outbound connections to whitelisted hosts. Where that's not possible due to the nature of the service (such as with email), I have alerting rules that notify me when they connect somewhere new. That's a "page" level notification if the new connection geolocates to China or Russia.

I take an old laptop with me when traveling, that gets wiped after the trip if I had to cross a border or leave it in a hotel safe.

I have good, frequent backups, on multiple media in multiple offline locations, that are tested regularly, so it's not the end of the world if I have to re-install a compromised device.

Something like VS Code remote dev with a container per project? Just plain docker/podman for containers?

> On my primary computer, my web browser runs in an ephemeral container that resets itself each time I shut it down. My password manager runs in a different, isolated, container. Zoom runs in a different, also isolated, container. And so on.

Qubes, or something else? I've been looking at switching to Linux for a while, but Apple Silicon being as good as it is has made making that leap extremely difficult.

I live inside Emacs for most things except browsing the web, either separate instances via SSH, or using TRAMP mode.

If you switch to Linux, I highly recommend configuring your browser with a fake Windows or MacOS user agent string. Our Cloudflare overlords really, really hate Linux users and it sucks to continually get stuck in endless CAPTCHAs. (And doing so probably doesn't hurt fighting against platform-specific attacks, either.)

We allow multiple security keys. You can add more here: https://app.mercury.com/settings/security

Not sure why we suddenly went from 2 factors (password + TOTP) to 1 factor (passkey), even if passkeys themselves are better.

TOTP should at least be an option for the users.

Authentication tokens (even tertiary ones) usually are supposed to have pretty strong secrecy guarantees. I've done multiple security audits for SOC, PCI, HIPAA, etc., and in every case the auditors would have balked if I told them signin tokens were being unnecessarily logged by a third-party service.

(Also: I strongly disagree that the only way to get reliable delivery is via a third-party email service, especially at Mercury's scale, but that's a digression from the topic at hand.)

That said, our security team and I agree there is no security issue here. Mailgun already can see the text of the emails we send.

Even if they were, almost all email goes through third parties which are trusted implicitly. That's not great, but email is the only federated system in existence capable of implementing this type of decentralized login at scale.

Maybe someday we'll be able to use something like Matrix, Fediverse OAuth, or ATProto OAuth instead, but those are all a ways off.

And again, I wasn't saying that you can't do all of this nonsense, but users who see it as nonsense should be able to turn it off.

The attack wasn't that the attacker has my second factor, the attack was that the attacker tricked me into verifying a single login/transaction using my two factors, on their behalf.

They probably judged that the inconvenience of the verification email affects few enough users that it is worth it. Most users don't switch IP addresses very often. And those that do, probably don't all clear their cookies after every session.

Adding SMS in addition to email would be obviously useless, as you point out.

The vendor might not be the only party to use an HTTP redirect service too! My email goes through a security screen by $EMPLOYER, which also rewrites links to get processed through their redirect service. Sure, it's for company-approved reasons, but it's still another party that has access to the login token.

    javascript:void(window.location.href = window.prompt().replace(/\\\n\s*/g, ''));

To be clear this is what we're trying to avoid. An easily typeable code like that can be typed into a phisher's website.

I appreciate you guys are trying to protect people, but no other financial institution I deal with requires this level of annoyance, and at some point I'd rather switch to a less "secure," but more usable service.

(I put secure in scare quotes, because some suggestions, like trading true 2FA, where I have two separate secrets on two separate devices, for a single WebAuthn factor, are actually accomplishing the opposite, at least for those of us who don't click links in emails and don't use ads on Google for navigation.)

Edit to add: or maybe save the third factor for suspicious activity, such as "new device adding a new payee," rather than every signin. It's been months since I onboarded a new vendor, and I'd be OK with only having to do the cut-and-paste-the-link dance a couple of times a year, rather than every single time I want to check my balance.

They could use a custom subdomain for this click tracking and "hide" the mailgun url from you, but we're finding that for some reason Mailgun doesn't just use a let's encrypt certificate, so some users will complain that the tracking links are "http" (and trigger a browser warning when clicked).

Anyway, even with click tracking disabled and links going straight to mercury.com, the security issue would remain the exact same (since Mailgun logs all outgoing email anyway).

But my understanding is that the contents of that email and its link do not provide "login" capability but "verification" capability. As such, a Mailgun employee accessing your data, or an attacker accessing your Mailgun logs, would only be able to "verify" a login that they had already initiated with your password AND your OTP —which means that's effectively a third hurdle for an attacker to breach, not a one-step jump into your account.

unfortunately, only few ISPs do IPv6 correctly by assigning a fixed prefix to customers. most of the ISPs apply the ipv4 logic when adding ipv6 planning hence this situation.

hopefully this will improve in the future and more stable prefixes will be given to users.

security = 1/convenience

but also vice versa

Lemme know if you have any questions!

Should this be “an” in the page header?

Then senders' incentive will become to make the subject line into clickbait for the content, so that you'll open the message. So instead of subjects like "Order placed", "Order paid", "Order shipped", "Order out for delivery" you'll get uniform subjects along the lines of "IMPORTANT UPDATE TO YOUR ORDER". You will lose efficiency getting through your emails, and over time the metric will lose its indicativeness. Everybody loses.

My gmail address received 35 emails yesterday (which didn't get spam filtered). All but 3 of those got auto-archived by the filters I have in gmail. I would love google to just do this automatically.

Practically I might need another message or two a week that didn't hit my inbox.... but that's fine as long as it's as it is still searchable.

But even for other types of transactional emails, like shipment confirmations, I would expect the open rate to be much higher and/or the complaint rate to be much lower than for marketing email.

(I helped make such a product 7 years ago)