DB Structure

This is the base schema of the database.

Base DB Schema

All users will have one or many integrations, and these integrations may have an id generated by the service system, or may generate an access token to validate each request (e.g. Plaid, Unit, Synapse).

User table fields

• Email: Email used by the user to register.

• External Id: Id generated by our system to identify the user in external systems.

Integration table fields

• Service: Refers to the type of service connected by the user (Unit, Auth0, Plaid).

• External Id: It refers to the id generated by the external system to identify the user.

• Authorization Id: This field stores an access token in an encrypted way if the integration needs one to send in each request.

• User Id: Refers to the user to which the integration belongs.

Example on src/features/enums/integration-services/enum.ts:

export enum IntegrationServices {
  AUTH0 = 'AUTH0',
  PLAID = 'PLAID',
}

There may be more fields in each table, as you can add more fields through migrations.

In addition, some services will need to associate an account and save the account id according to the log generated by the external server, so the following modification is proposed if you need to keep track of accounts.

Schema with related accounts.

This would be the migration to be able to add the account table to your database.

In this migration, the account table is created, and the reference for the users and integrations table is created.

import { MigrationInterface, QueryRunner } from 'typeorm';

export class createTableAccounts1659967614370 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `CREATE TABLE "public"."account" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "external_id" character varying NULL, "baas_id" character varying NULL, "integration_id" uuid NOT NULL, "user_id" uuid NOT NULL, "created_at" TIMESTAMP NOT NULL DEFAULT now(), "updated_at" TIMESTAMP NOT NULL DEFAULT now(), "deleted_at" TIMESTAMP , CONSTRAINT "PK_9162znh8ld8tylna9z6odivqt8u" PRIMARY KEY ("id"))`,
    );
    await queryRunner.query(
      `ALTER TABLE "public"."account" ADD CONSTRAINT "FK_5i6mtodoa8fcqzujy1qg53nvxlh" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE CASCADE`,
    );
    await queryRunner.query(
      `ALTER TABLE "public"."account" ADD CONSTRAINT "FK_ugmbyl1vy7py0xhyx6qdx46w8a3" FOREIGN KEY ("integration_id") REFERENCES "public"."integration"("id") ON DELETE CASCADE`,
    );

    await queryRunner.query(
      `CREATE INDEX "IDX_twrqghqf5afgc0u5ejz3pb1oco" ON "public"."account" ("user_id")`,
    );

    await queryRunner.query(
      `CREATE INDEX "IDX_oyv3ypxovks0kioee3tk4nyvzq" ON "public"."account" ("integration_id")`,
    );

    await queryRunner.query(
      `CREATE INDEX "IDX_ueqv91y92rs0stsvoyegrhue14" ON "public"."account" ("external_id")`,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "public"."account" DROP CONSTRAINT "FK_5i6mtodoa8fcqzujy1qg53nvxlh"`,
    );
    await queryRunner.query(
      `ALTER TABLE "public"."account" DROP CONSTRAINT "FK_ugmbyl1vy7py0xhyx6qdx46w8a3"`,
    );
    await queryRunner.query(
      `DROP INDEX IF EXISTS "public"."IDX_twrqghqf5afgc0u5ejz3pb1oco"`,
    );
    await queryRunner.query(
      `DROP INDEX IF EXISTS "public"."IDX_oyv3ypxovks0kioee3tk4nyvzq"`,
    );
    await queryRunner.query(
      `DROP INDEX IF EXISTS "public"."IDX_ueqv91y92rs0stsvoyegrhue14"`,
    );

    await queryRunner.query('DROP TABLE "public"."account"');
  }
}

Also, remember to create an account.entity.ts file, where you declare the account properties and you will make the relationship with the user table and integration.

Example:

import { Entity, Column, ManyToOne } from 'typeorm';
import { BaseModel } from 'src/common/database/base-model';
import { Integration } from 'src/features/integration/integration.entity';
import { User } from 'src/features/user/user.entity';
import { encryptionTransformer } from 'src/common/transformers/typeorm-encrypted.transformer';

@Entity()
export class Account extends BaseModel {
  @Column({
    nullable: true,
    transformer: encryptionTransformer,
  })
  public externalId!: string;

  @Column({
    nullable: true,
    transformer: encryptionTransformer,
  })
  public baasId!: string;

  @Column({ name: 'user_id' })
  public userId!: string;

  @Column({ name: 'integration_id' })
  public integrationId!: string;

  @ManyToOne(() => Integration, (integration) => integration.accounts, {
    nullable: true,
  })
  public integration?: Integration;

  @ManyToOne(() => User, (user) => user.accounts, { nullable: true })
  public user?: User;
}