Skip to content

Database, migrations, and RLS

Tandem stores its tables in a private tandem schema in your Postgres database. The package ships SQL migrations and a runner that records every applied migration in tandem.schema_migrations.

Run the migration runner during deploy or application startup. It applies only new files. Each migration runs in its own transaction, so a failed migration rolls back as one unit.

import { applyTandemMigrations, createTandemPool } from "tandem-crm/db";
const pool = createTandemPool(process.env.DATABASE_URL!);
const { applied } = await applyTandemMigrations(pool);
console.log("Applied Tandem migrations:", applied);

Use a connection string for a role that can create the tandem schema and apply migrations. Keep that connection string on the server.

Hosts with their own certificate authority

Section titled “Hosts with their own certificate authority”

Neon, Supabase and several other hosts use certificates that Node already trusts. Some hosts, including Aiven, DigitalOcean and Amazon RDS, secure the connection with their own certificate authority. Node refuses those by default and you will see the error SELF_SIGNED_CERT_IN_CHAIN. npx tandem-crm init explains this when it happens.

The fix is to tell Node which certificate to trust. Download the CA certificate from your host’s dashboard, then add two parameters to your connection string:

Terminal window
postgres://user:password@host:port/dbname?sslmode=verify-full&sslrootcert=/path/to/ca.pem

The connection stays fully verified. Make sure the certificate file is available wherever your app runs, not only on your laptop.

For a throwaway test database you can use sslmode=no-verify instead. It switches certificate checking off, so do not use it with real data.

Tandem uses Postgres row-level security to limit records to workspace members. Wrap request-scoped database work in withTandemSession. It opens a transaction, sets the current user ID only for that transaction, and runs the query as the authenticated role.

import { withTandemSession } from "tandem-crm/db";
const leads = await withTandemSession(pool, currentUserId, (client) =>
client.query("select * from tandem.leads"),
);

The database role used by DATABASE_URL needs membership in authenticated once. Replace your_connection_role with the role behind your connection string.

grant authenticated to your_connection_role;

This grant matters because a table owner or superuser bypasses row-level security. withTandemSession uses set local role authenticated, so a missing grant fails loudly instead of returning data under an unenforced policy.

Use a trusted scheduler for due commissions and disputes

Section titled “Use a trusted scheduler for due commissions and disputes”

Tandem provides database functions for due commission releases and overdue dispute resolution. Your deployment chooses the scheduler and grants it only the permissions it needs. This can be a Postgres scheduler, a serverless scheduled job, or your own backend job runner.

Keep raw provider payloads and database credentials on the server. Raw payloads can contain personal data, so give them an appropriate retention and access policy.