RFC: Abstracting Providers

[tmyracle]

RFC: Abstracting data providers from models

Before diving into this I wanted to put together a proposal of how we might go about this to think through the approach and help make it as solid as possible. There are currently 4 open issues related to moving vendor specific columns from different tables and abstracting them into something that would support future additions in a more scalable way:

• Abstract data provider away from Users table #147
• Abstract data provider away from Accounts table #148
• Abstract data provider away from AccountConnections table #149
• Abstract data provider away from Transactions table #150

I'll use the Accounts task as an example.

In the Account table there's currently the following vendor specific columns:

• teller_account_id
• teller_type
• teller_subtype

The general approach I'm thinking about is for each of these base models (Account, AccountConnection, Transaction, User) we create a new table that holds the provider (Teller, etc) information that can represent a sort of polymorphic association with the base model (in this case Account).

For example, Account goes from:

enum AccountProvider {
  user
  plaid
  teller
}


model Account {
  id                       Int                    @id @default(autoincrement())
  createdAt                DateTime               @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt                DateTime               @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)
  startDate                DateTime?              @map("start_date") @db.Date
  type                     AccountType
  provider                 AccountProvider
  /... rest of Account fields
  availableBalanceStrategy AccountBalanceStrategy @default(available) @map("available_balance_strategy")
  
  // plaid data
  plaidAccountId String? @map("plaid_account_id")
  plaidType      String? @map("plaid_type")
  plaidSubtype   String? @map("plaid_subtype")
  plaidLiability Json?   @map("plaid_liability") @db.JsonB

  // teller data
  tellerAccountId String? @map("teller_account_id")
  tellerType      String? @map("teller_type")
  tellerSubtype   String? @map("teller_subtype")
  
  /... rest of account fields  

  @@unique([accountConnectionId, plaidAccountId])
  @@unique([accountConnectionId, tellerAccountId])
  @@index([accountConnectionId])
  @@index([userId])
  @@map("account")
}

to something like this:

enum AccountProviderType {
  user
  plaid
  teller
}

model AccountProvider {
  id                     Int                 @id @default(autoincrement())
  createdAt              DateTime            @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt              DateTime            @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)
  type                   AccountProviderType @default(user)
  name                   String?
  accountProviderId      String?             @unique @map("account_provider_id")
  accountProviderType    String?             @map("account_provider_type")
  accountProviderSubtype String?             @map("account_provider_subtype")
  metadata               Json?               @db.JsonB

  account   Account?
  accountId Int?     @map("account_id")

  @@map("account_provider")
}

model Account {
  id                       Int                    @id @default(autoincrement())
  createdAt                DateTime               @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt                DateTime               @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)
  startDate                DateTime?              @map("start_date") @db.Date
  type                     AccountType
  provider                 AccountProvider        @relation(fields: [accountProviderId], references: [id], onDelete: Cascade)
  accountProviderId        Int                    @map("account_provider_id")

  /... rest of Account fields

  @@unique([accountProviderId])
  @@index([accountConnectionId])
  @@index([userId])
  @@map("account")
}

Key changes:

• New AccountProvider table that stores provider specific info along with JsonB column for any metadata that isn't common amongst providers. This table can be extended in the future as needed.
• 1:1 relationship between Accounts and AccountProvider
• Moving AccountType to the new AccountProvider table and renaming

Note

Along with this move comes a lot of code that needs to be refactored of course and one of the more annoying parts will be replacing all of the places we create Accounts to also create the associated AccountProvider and associate them.

There's also the need to ensure the migration creates the new table, goes through existing accounts and populates rows in the account_provider table including creating the association, and then proceeds with any changes to the accounts table.

So a lot of stuff going on and the schema updates and migration are the easiest part, I started tackling this and then saw the refactor work required downstream of those schema changes and paused to make sure we're aligned on the general direction before going into the trenches on that.

I'd love to hear everyones thoughts and suggestions on the approach so we can have a set methodology for abstracting vendor information from each of the tables and do it in a consistent way. Thanks!