Add payment status to filters
see orders that are not paid
we want to create a calculated field called paymentStatus that can be calculated on the postgres server and then be utilised by the sync engine rather than calculating it on our side or on the frontend as we go, this means we can query it properly whenever we want rather than doing complex shit.
also we want to add a filter chip to the bookings list page that allows as to filter by paid or not paid.
Conversation with Gemini
Step 1: SQL Migration
You will need to run raw SQL to set this up in your database. This is usually done via a migration file in your ORM.
1. Add the column to the order table:
SQL
ALTER TABLE "order"
ADD COLUMN "paid_amount_in_cents" INTEGER DEFAULT 0 NOT NULL;
ADD COLUMN 'payment_status'
2. Create the Calculation Function: This function calculates the sum of all payments with the status 'paid' for a specific order and updates the order row.
Note: I have assumed the status string indicating a successful payment is 'paid'. If you use 'completed' or 'succeeded', change the string in the WHERE clause below.
SQL
CREATE OR REPLACE FUNCTION update_order_paid_amount()
RETURNS TRIGGER AS $$
BEGIN
-- 1. Handle INSERT or UPDATE (use NEW.order_id)
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
UPDATE "order"
SET "paid_amount_in_cents" = (
SELECT COALESCE(SUM(amount), 0)
FROM payment
WHERE order_id = NEW.order_id
AND status = 'paid' -- <--- IMPORTANT: Match your exact payment status here
)
WHERE id = NEW.order_id;
END IF;
-- 2. Handle DELETE or UPDATE where the order_id changed (use OLD.order_id)
IF (TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND OLD.order_id IS DISTINCT FROM NEW.order_id)) THEN
UPDATE "order"
SET "paid_amount_in_cents" = (
SELECT COALESCE(SUM(amount), 0)
FROM payment
WHERE order_id = OLD.order_id
AND status = 'paid'
)
WHERE id = OLD.order_id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
3. Create the Trigger: This tells Postgres to run the function above whenever the payment table is touched.
SQL
CREATE TRIGGER trigger_update_order_paid_amount
AFTER INSERT OR UPDATE OR DELETE ON payment
FOR EACH ROW
EXECUTE FUNCTION update_order_paid_amount();
Step 2: Update Your ORM Schema
Now that the database handles the logic, you need to expose this new field to your application code. Update your order table definition.
You should likely mark this field as read-only in your application logic (or exclude it from update schemas) so your code doesn't accidentally try to overwrite what the database is calculating.
TypeScript
const order = table("order")
.columns({
id: string(),
// ... existing columns ...
priceInCents: number().from("price_in_cents"),
// Add the new column here:
paidAmountInCents: number().from("paid_amount_in_cents"),
// ... rest of columns
})
.primaryKey("id");
Why this approach is best
Data Integrity: It is impossible for the "Paid Amount" to get out of sync with the actual payments, even if you manually edit the database using a SQL GUI.
Performance: Reading the order becomes very fast because you don't have to join the payments table and sum them up every time you fetch an order list.
Concurrency: Postgres handles the locking logic, preventing race conditions that might occur if you tried to calculate this in your Node.js/Application code.
Would you like me to...
Adjust the SQL trigger to handle multiple "success" statuses (e.g., if both paid and cleared count as valid payments)?
Show you how to backfill this column for your existing data?
lets make it instead "outstanding_in_cents"
To calculate Outstanding Amount (which is Total Price - Paid Amount), the logic changes slightly. We need to subtract the sum of payments from the price_in_cents field on the order itself.
Here is the updated implementation.
Step 1: SQL Migration
1. Add the column to the order table We default it to 0 initially, but we will run a backfill calculation immediately after to correct existing data.
SQL
ALTER TABLE "order"
ADD COLUMN "outstanding_in_cents" INTEGER DEFAULT 0 NOT NULL;
2. Create the Calculation Function This function now calculates: Order Price - Sum(Paid Payments).
SQL
CREATE OR REPLACE FUNCTION update_order_outstanding_amount()
RETURNS TRIGGER AS $$
BEGIN
-- 1. Handle INSERT or UPDATE (use NEW.order_id)
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
UPDATE "order"
SET "outstanding_in_cents" = "price_in_cents" - (
SELECT COALESCE(SUM(amount), 0)
FROM payment
WHERE order_id = NEW.order_id
AND status = 'paid' -- Only count successful payments
)
WHERE id = NEW.order_id;
END IF;
-- 2. Handle DELETE or UPDATE where the order_id changed (use OLD.order_id)
IF (TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND OLD.order_id IS DISTINCT FROM NEW.order_id)) THEN
UPDATE "order"
SET "outstanding_in_cents" = "price_in_cents" - (
SELECT COALESCE(SUM(amount), 0)
FROM payment
WHERE order_id = OLD.order_id
AND status = 'paid'
)
WHERE id = OLD.order_id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
3. Create the Trigger on the payment table This ensures that whenever a payment is made, updated, or deleted, the outstanding amount on the order is recalculated.
SQL
CREATE TRIGGER trigger_update_order_outstanding
AFTER INSERT OR UPDATE OR DELETE ON payment
FOR EACH ROW
EXECUTE FUNCTION update_order_outstanding_amount();
Step 2: Update Schema Definition
Update your ORM definition to include the new field.
TypeScript
const order = table("order")
.columns({
id: string(),
// ... existing columns ...
priceInCents: number().from("price_in_cents"),
// The new calculated field
outstandingInCents: number().from("outstanding_in_cents"),
// ... rest of columns
})
.primaryKey("id");
Step 3: Important Considerations
1. What if the Order Price changes?
The trigger above only fires when the Payment table changes. If you change the price_in_cents on the order table, the outstanding_in_cents will technically be wrong until a new payment is added.
To fix this, you should strictly add a second trigger on the order table itself to recalculate if the price changes.
SQL for Order Price Trigger:
SQL
CREATE OR REPLACE FUNCTION recalculate_outstanding_on_price_change()
RETURNS TRIGGER AS $$
BEGIN
-- If the price changed, recalculate outstanding using the existing payments
NEW.outstanding_in_cents := NEW.price_in_cents - (
SELECT COALESCE(SUM(amount), 0)
FROM payment
WHERE order_id = NEW.id
AND status = 'paid'
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_update_outstanding_on_price_change
BEFORE UPDATE OF price_in_cents ON "order"
FOR EACH ROW
EXECUTE FUNCTION recalculate_outstanding_on_price_change();
2. Backfill Existing Data
Since you just added the column, all your existing orders currently have outstanding_in_cents = 0 (the default). You need to fix this once.
Run this SQL once after your migration:
SQL
UPDATE "order" o
SET "outstanding_in_cents" = o.price_in_cents - (
SELECT COALESCE(SUM(p.amount), 0)
FROM payment p
WHERE p.order_id = o.id
AND p.payment_status = 'paid'
);
Would you like me to...
Combine all these SQL snippets into a single migration file script for you?
Explain how to verify this works using a few manual SQL INSERT statements?
Your Lockii chats aren't used to improve our models. Gemini can make mistakes, so double-check it. Your privacy and GeminiOpens in a new window
