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:
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.
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';exportclasscreateTableAccounts1659967614370implementsMigrationInterface {publicasyncup(queryRunner:QueryRunner):Promise<void> {awaitqueryRunner.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"))`,
);awaitqueryRunner.query( `ALTER TABLE "public"."account" ADD CONSTRAINT "FK_5i6mtodoa8fcqzujy1qg53nvxlh" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE CASCADE`,
);awaitqueryRunner.query( `ALTER TABLE "public"."account" ADD CONSTRAINT "FK_ugmbyl1vy7py0xhyx6qdx46w8a3" FOREIGN KEY ("integration_id") REFERENCES "public"."integration"("id") ON DELETE CASCADE`,
);awaitqueryRunner.query(`CREATE INDEX "IDX_twrqghqf5afgc0u5ejz3pb1oco" ON "public"."account" ("user_id")`, );awaitqueryRunner.query(`CREATE INDEX "IDX_oyv3ypxovks0kioee3tk4nyvzq" ON "public"."account" ("integration_id")`, );awaitqueryRunner.query(`CREATE INDEX "IDX_ueqv91y92rs0stsvoyegrhue14" ON "public"."account" ("external_id")`, ); }publicasyncdown(queryRunner:QueryRunner):Promise<void> {awaitqueryRunner.query(`ALTER TABLE "public"."account" DROP CONSTRAINT "FK_5i6mtodoa8fcqzujy1qg53nvxlh"`, );awaitqueryRunner.query(`ALTER TABLE "public"."account" DROP CONSTRAINT "FK_ugmbyl1vy7py0xhyx6qdx46w8a3"`, );awaitqueryRunner.query(`DROP INDEX IF EXISTS "public"."IDX_twrqghqf5afgc0u5ejz3pb1oco"`, );awaitqueryRunner.query(`DROP INDEX IF EXISTS "public"."IDX_oyv3ypxovks0kioee3tk4nyvzq"`, );awaitqueryRunner.query(`DROP INDEX IF EXISTS "public"."IDX_ueqv91y92rs0stsvoyegrhue14"`, );awaitqueryRunner.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.