Monitoring Airtable Schema Changes

[bfrench]

An investor in Airtable aftermarket products asked me …

“Is there a feature or process where solution builders can work together on schema changes and be informed of each other’s changes? There must be some good methodologies for scaling a tech team using Airtable.”

He’s so cute; he assumes " methodologies " must be available. He doesn’t know Airtable well.

Here’s my assessment, but I’m curious about what y’all do to mitigate the risks of concurrent schema editing, which can ultimately lead to catastrophic solution failures.

As I see it, there are three ways a design team can shit the bed:

1. Table Data - (ex: modification of the actual information of a table where that table is integral to the design of the application)

2. Table Fields - (ex: modification to field names that represent dependencies in the solution)

3. Table Metadata - (ex: changing a table name that is dependent on the solution)

Luckily, all three of these events can be caught and notified upon.

Unluckily, there are no webhooks into the Airtable UI such that we could prevent two users from editing the same schema concurrently and in separate ways. Furthermore, there’s no easy way to inform designer (a) that designer (b) has modified common table structure (c).

One remedy is to use a common push notifications approach that runs in the browser (like this one). Since Airtable designers are in the browser, we need only create a common toaster popup-like feature to keep the design team aware of changes as they happen.

Similar (even redundant) automated processes could be handled in Google Chat or Slack, thus providing the team with heads-up messages when changes have actually occurred. The Airtable events notification architecture is fast, and messaging is equally fast, so this could be implemented as a truly real-time watchdog to mitigate risk.

But even with this communications layer, there remain possible changes to schemas that impact dependencies which may not be obvious. That part requires a diligent change management process that uses the Schema API to create exception reports when dependencies are impacted.

It is possible, but non-trivial, to blend real-time notifications against the backdrop of automated processes that perform relatively fast impact assessments, which would do far more than notify designers that something important has been modified. Indeed, the popups could say what has changed and where this change impacts the entire solution.

Thoughts? Remedies? Best practices for defending solutions from those flash-bang grenades that light up your support lines?

[Kuovonne]

In my personal experience, schema changes that cause problems are rarely due to two people modifying schema simultaneously. When a system blows up due to a schema change, it is usually a single person acting alone, or a lack of communication over time.

@bfrench

Table Data - modification of the actual information of a table where that table is integral to the design of the application.

I try to limit situations where changing specific cell values can blow up an entire system. Messing up cell values might cause some problems, but shouldn’t cause system-wide failure. I’ve had to deal with legacy systems that depend on specific records having specific values in specific fields, but I don’t design systems this way. Since editors can change cell values, building a system based on specific cell values makes the system vulnerable to a lot more people than just creators.

@bfrench

Table Fields - modification to field names that represent dependencies in the solution.

It isn’t just field names that can cause problems. Changing single select choices also tends to break things when those choices are used in formula fields, scripts, or third party systems. Changing field types and deleting fields are also candidates for blowing up a system.

@bfrench

Table Metadata - changing a table name that is dependent on the solution.

Again, it isn’t just changing a table name that can cause problems. Changing view filters is a common method of blowing up a system. Other changes in permissions or deleting a table can also blow things up.

@bfrench

One remedy is to use a common push notifications approach

I don’t think that notifications of schema changes is the right approach, at least not by itself. It is re-active. If someone messed up the schema, the damage is already done, and a zillion automations have already run.

@bfrench

It is possible, but non-trivial, to blend real-time notifications against the backdrop of automated processes that perform relatively fast impact assessments, which would do far more than notify designers that something important has been modified. Indeed, the popups could say what has changed and where this change impacts the entire solution.

Five words into this paragraph is the word “non-trivial”. When Bill says that something is non-trivial, that gives me pause. Plenty of people have a hard time understanding and implementing things that Bill does not tag as “non-trivial”. Thus, I expect that very, very few people are able to implement something that Bill says is non-trivial.

---

My approach to defending against changes that can blow up a system, antiquated as it may be, is to

• limit the number of creators for a base, and make sure they all understand how to make schema changes safely and the potential impacts of changes
• investigate dependencies and possible side effects before implementing schema changes
• testing out high risk schema changes in a sandbox base before building in the production base

Slightly related, I am hoping to be selected as a speaker at the upcoming DareTable conference, and part of my proposed talk is related to my approach to defending a base’s schema. I don’t have notifications about schema changes and their potential impacts. I combine a low-tech formula and a workflow that I use when considering schema changes. This system isn’t nearly as capable as what Bill proposes; however, it is trivial to implement.

[bfrench]

@Kuovonne

Messing up cell values might cause some problems, but shouldn’t cause system-wide failure.

Unless it’s a configuration file of IP addresses that is integral to the app, this is not to be confused with operational data. Imagine a system that stores data dictionaries about other systems in Airtable itself.

@Kuovonne

I don’t think that notifications of schema changes is the right approach, at least not by itself. It is re-active. If someone messed up the schema, the damage is already done, and a zillion automations have already run.

Indeed. Notifications are like the cops showing up to call the coroner. But imagine if you could disable all automations when a schema change is detected.

[ScottWorld]

I agree that it is far too easy to make schema changes in Airtable. This has caused tremendous problems for many of my clients.

As @Kuovonne mentioned above, one of the best ways that I can think of to prevent this from happening is by limiting the number of creators to as few people as possible… ideally just one or two people who are very well-versed in the system, and who are very well-trained in understanding the repercussions of making any changes.

[bfrench]

@ScottWorld

ideally just one or two people who are very well-versed in the system, and who are very well-trained in understanding the repercussions of making any changes.

Yeah - good advice. But, it tends to be impractical for enterprises. And larger companies have teams at all three corners of the time zones which need to build applications 24/7.

[Kuovonne]

@bfrench

Unless it’s a configuration file of IP addresses that is integral to the app, this is not to be confused with operational data. Imagine a system that stores data dictionaries about other systems in Airtable itself.

In this situation, I think I’d put the data in a table that has lots of restrictions on permissions. No-one is allowed to delete records or edit any cell values. When someone needs to edit cell values, it would take a creator to enable editing. Hopefully that extra friction would avoid accidental changes.

@bfrench

But imagine if you could disable all automations when a schema change is detected.

Lots of schema changes don’t call for disabling automations. Rather, I would prefer a system that monitors the queue and if the queue for an automation suddenly gets very long right after a schema change, I’d like a pause on the queue until someone investigate and decides if the items in the queue should be processed normally or if they should be aborted. So, don’t completely disable the automations, but also don’t execute the automations.

Of course, it would be better if the developer checked to see if the schema change would affect automations before performing the schema change.

[Kuovonne]

@bfrench

But, it tends to be impractical for enterprises

Can you explain this a bit more?
I understand that limiting the total number of creators is impractical for large enterprises.
However, limiting the number of creators per base seems reasonable to me.
Maybe some bases would have more than one or two creators, but I don’t understand why
each base couldn’t be limited to a handful of creators.

[ScottWorld]

@Kuovonne

I understand that limiting the total number of creators is impractical for large enterprises.
However, limiting the number of creators per base seems reasonable to me.

I agree with this assessment. For example, in one of my large enterprises, they have dozens (maybe hundreds) of bases, but each base is assigned to only one creator who is “in charge of” that base.

[bfrench]

@Kuovonne

Of course, it would be better if the developer checked to see if the schema change would affect automations before performing the schema change.

Yep - agree. The risk is what the investor is concerned with mitigating. And when humans must be depended on as the gatekeepers, the risks rise.

@Kuovonne

I’d like a pause on the queue until someone investigate and decides if the items in the queue should be processed normally or if they should be aborted

Yep, I like this as well. It’s possible to build all [critical] automations now with exactly this stop-gap measure. A base-wide flag (or classes of flags) should be set when a schema change event occurs that suspends related automations and it’s reset when the schema review has been completed.

@Kuovonne

I don’t understand why each base couldn’t be limited to a handful of creators.

Imagine a solution that supports all time zones on three continents with a team of 18 developers pre-production and 6 who assume control of the base post-production. This is not an uncommon in-house development scenario.

[bfrench]

@ScottWorld

each base is assigned to only one creator who is “in charge of” that base.

That’s ideal, but sometimes not practical in a global company where domain experts share bases for distributed teams of users, many of who may speak different languages but are certainly asleep when others are awake. An enterprise that wants to use Airtable in the truest sense of a “global solution” will ask about concurrent access across more than just one or two people.

[Kuovonne]

@bfrench

Imagine a solution that supports all time zones on three continents with a team of 18 developers pre-production and 6 who assume control of the base post-production. This is not an uncommon in-house development scenario.

I’m surprised that this is common for Airtable based systems. I could see this as common for traditional code-based systems. But given Airtable’s limitations and limited scale, I have a hard time picturing having 18 concurrent developers for a the same Airtable base as common.

@bfrench

I like this as well. It’s possible to build all [critical] automations now with exactly this stop-gap measure. A base-wide flag (or classes of flags) should be set when a schema change event occurs that suspends related automations and it’s reset when the schema review has been completed.

Possible. But not trivial.

@bfrench

An investor in Airtable aftermarket products asked me …

Now I’m curious about this investor.

[bfrench]

@Kuovonne

I have a hard time picturing having 18 concurrent developers for a the same Airtable base as common.

Lots of tasks - views, interfaces, tables, relationships, lookups, and all of the automation components. Then there’s React and plugins and custom apps. Lots of room for many specialities in an enterprise dev team. The CSO’s team alone might have six engineers probing to give the devs a bad time.

@Kuovonne

Now I’m curious about this investor.

I’ve made most of my money advising advisors and testifying.

[Greg_vonF]

What is

@bfrench

An investor in Airtable aftermarket products

?

“Airtable aftermarket products” - meaning software that focuses exclusively on extending Airtable features?

The said investor invests specifically in this niche?

[bfrench]

@Greg_vonF

meaning software that focuses exclusively on extending Airtable features?

It’s a bit bigger in scope. Extending a product with features is a difficult pathway to investment. These are micro-enhancements and risky for investors because, at the drop of a hat, Airtable could displace the extension. Investors I see often are macro-enhancements; using Airtable to rethink entire business models.

Imagine you created a recycling system based on Airtable. True story - LITTA - more than $5m invested. It all began with a simple Airtable → Google Docs invoicing system. I think they have ~31 people and cover all of the UK for trash and recycling.