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.
Apply migrations
Section titled “Apply 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:
postgres://user:password@host:port/dbname?sslmode=verify-full&sslrootcert=/path/to/ca.pemThe 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.
Run request queries with an identity
Section titled “Run request queries with an identity”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.