Aerie Database Architecture

[Mythicaeda]

This issue lead to a discussion between @mattdailis, @camargo and myself about the architecture of our Postgres instance and why we chose to divide it into several databases as opposed to using several schemas within a single database. Since we will likely need to add schemas in order to have Hasura Authentication (or, further on, User Roles and Permissions), now is a good time to discuss if/how the current architecture should be changed.

Context regarding the initial discussion:
When a plan is created via the UI, a scheduling specification and an entry into simulation are created. When a plan is duplicated, a new entry into simulation is created as part of duplicate_plan, but no scheduling specification is created. Due to AerieMerlin and AerieScheduler being separate databases, it is impossible for duplicate_plan to create this scheduling specification or for scheduling_specification to "watch" plan and create a new specification upon an INSERT into plan without using additional Postgres modules, for example, Postgres Foreign Data Wrapper.

@Twisol and @patkenneally, if you could share any reasoning behind the current database architecture that you remember (or of any places where that reasoning was previously documented), that would be helpful.

[dyst5422]

This is something I've had conversations with a bunch of people on the team about.

Because this enforces all of the data be stored in the same PG instance, here are some thoughts to consider:

• Hosting environment of users
  • Cloud provider?
  • Local hardware?
  • Combination of the two (this is where it gets interesting)?
• Users may want to separate their hosting environment based on data ownership or performance needs for various elements of the system
• Its possible users may want to use Aerie, but add their own data to the picture using their own schemas where using SQL directly is a necessity (joins against additional information where the performance hit of joining outside of SQL is too great)

I don't think there's really a right or wrong way here, just need to identify and acknowledge the implicit/explicit tradeoffs.

(Personally very much in favor of a shared PG instance for all of the Aerie data)

[Twisol]

(Personally very much in favor of a shared PG instance for all of the Aerie data)

Agreed; the design we have now is physically colocated but logically separated. I think the topic is about weakening the logical separation more than any physical characteristics -- though, keeping a strong logical separation does, as you say, allow for a customer-directed strengthening of physical separation.

[dyst5422]

Agreed; the design we have now is physically colocated but logically separated.

The DEPLOYMENTS we have now are physically colocated but logically separated, but the design is not necessarily physically colocated. There is currently nothing that prevents users from deploying the scheduler data to a different host than the merlin data and the sequencing data.

And in fact, I could definitely see JPL missions being inclined to physically separate the sequencing from planning data because of the historical planning-sequencing separation and because of the sensitive nature of sequencing data vs planning data.

[Twisol]

The DEPLOYMENTS we have now are physically colocated but logically separated, but the design is not necessarily physically colocated. There is currently nothing that prevents users from deploying the scheduler data to a different host than the merlin data and the sequencing data.

You're right! I was thinking of our docker-compose.yml when I said that, because that's the first deployment most customers will use to evaluate Aerie. I think it's also the way most of us develop against Aerie, so I don't know if there are any places in the code that assume, say, that both the scheduler and the planning system are accessible at the same Hasura instance (since that assumption is almost always true). But there is indeed very little, if anything, that requires physical colocation -- it's just something customers wanted ("don't make us run multiple databases") and that we specifically wanted to accommodate.

[Twisol]

Two reasons for the current database architecture come strongly to mind.

1. Keeping the scope of mutable state as tight as possible makes it easier to reason about how that state will change, and what changes to that state will affect. A database is basically one big global variable, and coupling to that global from many disparate places makes it very difficult to understand the ramifications of any change. This eventually causes the database schema to ossify.

   Like with object-oriented encapsulation, instead of letting anybody just reach into the fields of an object and do what it likes, dependent peers need to interface with the system that owns that state. The interface for this system is the set of tables, views, and procedures we choose to expose through Hasura, plus any custom actions bound to Hasura.

   Also, just because we expose the scheduling system and the planning/simulation system through the same Hasura instance does not mean the systems are really one system with one big API. API colocation is as much a thing as database colocation; they're still separate APIs even if we colocate them in one Hasura instance. (It's really valuable to be able to see the logical and physical planes independently.) Convenience does not equal architecture!

2. The scheduler is not the scheduler; it is a scheduler. I don't believe we ever expected the current scheduler to be the end-all be-all; we always expected customers of various sizes to be able to interface with the system and implement their own scheduling logic. A pithy way to say it is that a scheduler is just an automated planner; whether human or computer, anybody who wishes to interact with a plan should be able to do so by the same interface. Another way to say it is that our services, including the scheduler, should not be unduly privileged relative to what somebody else could write.

   The upshot here is that, if you need a planner (again, human planner or computer scheduler) to know about a change to a plan, they should subscribe to the public interface of the planning system. In this case, that may mean using GraphQL/Hasura subscriptions (push-based), or simply querying the planning system regularly or whenever else it makes sense (pull-based).

A relatively minor additional point here (grouped with 1 above) is that customers may pick and choose pieces of the Aerie system to use, rather than taking it as one monolithic product. The UI has its own separate database (still, I hope) for the same reason -- it is a logically separate product that integrates with the core planning system.

[Mythicaeda]

Reviving this discussion as User Roles and Permissions and Tags has caused a need to reexamine the architecture of our database.

Current arguments in favor of the DBs being decoupled:

1. It prevents us from potentially writing circular table dependencies
2. We didn't want the databases to share data.
3. "[N]ot dogfooding our design as an external scheduler inhibits our ability to support external schedulers, not that we couldn't do both anyway." (From this Slack Message from @Twisol)

Current arguments in favor of the DBs being joined:

1. It would not requires us to "keep in our heads" the data relationships (ie the plan-scheduling spec relationship in the original comment of this discussion)
2. It would allow the SQL code to manage database relationships, rather than Java/Typescript code (ie, plan scheduling specs are maintained via the UI).
3. It would allow us to express the cross-db permissions rules we want (ie, scheduling goals are maintained by the plan owners/collaborators--this is currently impossible to express in Hasura with our current schema, and since we want missions to be able to customize these permissions, hard-coding our default rules into the Java interface is not a good solution)
4. It would allow us to properly represent foreign keys on shared data (ie tags, users, which plans exist)
5. It would improve the API with regards to users/tags, as we could use the username/tagname, rather than an id
6. We would not need to come up with a new migration process to avoid missions losing data when migrating the tags/users table features (see this Slack thread for an example).

Current arguments against the DBs being decoupled (Going to skip the ones derived from Points 1-6 of arguments in favor of the DBs being joined):

1. We have data we would strongly prefer to share across the entire deployment. Postgres recommends using Schemas in these cases.
2. Future deployments with their own schedulers may not use our DB structure (ie @adrienmaillard's custom scheduler only inserts a finished scheduling request row into the DB so that the UI can display it, but otherwise does not use the Scheduler DB).
3. The database is difficult to work with due to the restrictions that the DBs being decoupled cause.

Current arguments against the DBs being joined:

1. Merging the DBs would be a bit of work.
2. We would need to update the Hasura metadata to preserve the GQL API.

[mattdailis]

Thank you for the write-up. I am sympathetic to the difficulties of working across multiple databases, and I'm weighing that against the architectural drivers that lead to this design in the first place. As far as I can tell, the addition of users or tags does not constitute an unforeseen need or a change to those architectural drivers

[mattdailis]

To elaborate a bit - in the spirit of Chesterton's Fence, we must truly understand a decision before we can reverse it. What I think is missing is a discussion of high-level architecture - "We didn't want the databases to share data." doesn't quite explain the rationale behind this design decision.

As others have noted above, the scheduler and sequencing services were envisioned as "a scheduler" and "a sequencing service", rather than "the scheduler", and "the sequencing service" - i.e. they were intended to be easy to disable or replace by a mission or company with a use case that required a different approach to scheduling or sequencing.

The separation of responsibilities would be as follows:

• Merlin provides activity planning, simulation, and constraints checking
• The scheduler service serves as an automated assistant to the otherwise labor-intensive process of activity planning
• The sequencing service provides sequence editing and command expansion facilities

The scheduler service is marketed as "unprivileged" in the same way that we market the UI as unprivileged - we claim that we make sure our API is expressive enough for external users by enforcing that our own services communicate with each other using this same API.

As you quote @Twisol - internalizing the scheduler service - which amounts to adding "scheduling" to the list of responsibilities of the merlin service - hurts our ability to claim that our API is powerful enough to support adding a new scheduling service. In the short term - we would likely leave the API unchanged - but a natural evolution would be for the scheduler service to start directly reading and writing from the merlin service's tables, bypassing the GraphQL API. This will naturally lead to the scheduler making use of capabilities that are not exposed via the API. The purpose of architecture is to choose a set of anti-patterns to make difficult to achieve. The anti-pattern chosen here was allowing the scheduler to directly access merlin's data without going through the API.

Hasura makes keeping these separate concerns straight a little difficult - because it makes it so easy to generate APIs from database schemas, it creates the feeling that all API clients have direct access to the database. Our choice to expose most of our tables as-is reinforces that feeling. It is important to remember that the merlin database belongs to the merlin service - that the merlin service reserves the right to store private data in that database, and not expose it via the API.

[dyst5422]

I love @mattdailis's explanation on this one and completely agree. I think what could use a little more clarification is HOW the database separation fulfills these requirements in a way that other solutions would NOT be able to - or rather an examination of those specific other proposed solutions in relation to these requirements.

If there were a way to have it both ways - preclude reading and writing to specific services such that we continue to dogfood the api design AND allow for some of the internal DB level enforcement + joining that provides performance and clarity benefit- that solution space might provide added value.

I'm unconvinced such a solution exists.

[skovati]

Based on our conversation yesterday, I see this issue like the following triangle, where we can only choose two of the three desirable properties (Developer eXperience, modularity (see dogfooding argument above), and performance).

If we prioritize DX and modularity by building some external webhook that handles cross-cutting concerns like user permissions, we might take a performance hit as we move more logic out of the fast in-process postgres triggers / functions and into external processes that communicate over the network.

If we prioritize modularity and performance, as is the status quo, i.e. keep databases separate, we take a hit on DX, which makes relatively simple relationships hard to express, since we lose the "relationship safety" that postgres would normally provide for us when we must go through the API for data from other components.

If we prioritize performance and DX, i.e. by combining databases into one database with multiple schemas, we lose the modularity that aerie originally strived for, i.e. that each component could be deployed separately or switched out for e.g. a custom UI or alternative scheduler.

I'm not sure what the best solution is for Aerie in general, but it does seem like each option has significant drawbacks in at least one category. Food for thought 🤔