Databases and Postgres

Three ways to see it

1. 1

   A database is a structured store of data, and Postgres is the most respected open-source relational database in the world. It runs Apple, Reddit, Instagram, and most fintechs in Pakistan that take their stack seriously. The model is simple: data lives in tables, each row is a record, each column is a field, and tables connect through foreign keys. SQL is the language you use to ask the database questions. Learning Postgres well is a lifetime skill; the syntax has barely changed in twenty years.

2. 2

   Way one, design the schema. For our remittance app, three tables: customers (id, full_name, cnic, phone, created_at), beneficiaries (id, customer_id, full_name, country, iban, currency), transfers (id, customer_id, beneficiary_id, amount_pkr, amount_dest, fx_rate, status, created_at). The customer_id column in transfers points to a row in customers, that is a foreign key. The id columns are primary keys, usually UUIDs or bigints. Get the schema right on paper before writing one line of code; a wrong schema in production costs a hundred times what it costs at design time.

3. 3

   Way two, write SQL. CREATE TABLE transfers (id uuid primary key default gen_random_uuid(), customer_id uuid references customers(id) not null, beneficiary_id uuid references beneficiaries(id) not null, amount_pkr numeric(14,2) not null check (amount_pkr > 0), amount_dest numeric(14,2) not null, fx_rate numeric(10,4) not null, status text not null default 'pending', created_at timestamptz default now());. To find all pending transfers above PKR 100,000 from a specific customer: SELECT id, amount_pkr, created_at FROM transfers WHERE customer_id = $1 AND status = 'pending' AND amount_pkr > 100000 ORDER BY created_at DESC. To join with beneficiary names: SELECT t.id, t.amount_pkr, b.full_name FROM transfers t JOIN beneficiaries b ON t.beneficiary_id = b.id WHERE t.customer_id = $1.

The why-tree

Try this with Claude

AI-edge prompt to try with Claude: 'Write a Postgres schema for a 3-tier remittance app: senders, recipients, transactions. Include indexes for monthly KYC reporting, foreign-key constraints, numeric precision suitable for PKR, status enum with check constraint, and an audit-log table with triggers. Then write the Drizzle ORM schema mirroring it. Then write the top five queries the operations team will run daily, with EXPLAIN ANALYZE expectations.' Save the output as schema.sql and queries.sql, then run them.