PostgreSQL Schema

The Insight Service connects to the shared FloatMe RDS PostgreSQL cluster (ebdb) via two connection pools: a primary (main) instance for writes and a read replica for queries. Both are accessed through pgxpool connections managed at Lambda startup.

Connection Configuration

Pool Usage

rdsMain

Write operations: INSERT and DELETE on employment records

rdsReplica

Read operations: SELECT queries for payday prediction and income detection

Credentials are fetched from AWS Secrets Manager at Lambda startup:

  • Main: {environment}/rds/main

  • Replica: {environment}/rds/replica

Two Lambdas connect to RDS: prod-insight-api and prod-insight-institution-change-handler. Both are placed inside the floatme VPC (private subnets floatme Private Subnet 1 and floatme Private Subnet 2) with the floatme PrivateSG security group.

Employment Table

Table Name

"FloatMeAPI_employmentinfomodel"

This is a Django-managed table in the shared ebdb database. The table name follows Django’s default {app}_{model} convention.

Columns

Column Go Field Description

id

Employment.ID

Auto-incremented primary key (int). Used to select the most recent record when multiple employment rows exist for a user (ORDER BY id DESC LIMIT 1).

user_id_id

Employment.UserID

FloatMe user ID (string). Foreign key to the Django user model (hence the double _id suffix). Used as the primary query filter.

employer_name

Employment.Name

Employer name string. Used by payday predictors for Jaro-Winkler matching against Pave income source names.

pay_type

Employment.PayType

Pay type descriptor (e.g., hourly, salary). Read but not heavily used in prediction logic.

pay_frequency

Employment.PayFrequency

Pay frequency string (e.g., biweekly, weekly). Used by the FM Legacy payday predictor to calculate the next payday deterministically.

payday

Employment.InitialPayday

Initial payday date string. Used as the anchor date for the FM Legacy predictor’s cadence calculation.

Access Patterns

Operation Pool Query

GetPrimary(user_id)

Replica

SELECT …​ FROM "FloatMeAPI_employmentinfomodel" WHERE user_id_id = $1 ORDER BY id DESC LIMIT 1

List(user_id)

Replica

SELECT …​ FROM "FloatMeAPI_employmentinfomodel" WHERE user_id_id = $1

Add(employment)

Main

INSERT INTO "FloatMeAPI_employmentinfomodel" (employer_name, pay_type, pay_frequency, user_id_id, payday) VALUES ($1,$2,$3,$4,$5)

Delete(user_id)

Main

DELETE FROM "FloatMeAPI_employmentinfomodel" WHERE user_id_id = $1

Who Calls Each Operation

Operation Caller

GetPrimary

FM Legacy payday predictor (during float creation and GET /insights/employment/payday)

List

Income detection methods that scan all employment records for a user

Add

Income verification flow (when a user submits or updates their employment info)

Delete

Institution-change-handler (when a user connects a new bank institution)

The Delete operation is idempotent — deleting a non-existent user_id returns no error.

Related Pages