Accounts, Orders & Credits

Why This Matters

This template ships with authentication, Stripe‑friendly order tracking, and a credit balance system. Knowing how the tables connect helps you add products, integrate payments, or debug “where did my credits go?” without guesswork.

---

Meet The Cast (Core Tables)

• users — Profile source of truth. Columns: uuid (our cross‑table key), email, locale, role, etc. Better Auth populates this.
• accounts & sessions — Better Auth internals that back sign‑in. Useful for audits; not part of the ledger math.
• orders — Every checkout attempt/result. Columns: order_no, status, amount, subscription fields (sub_*), credits, expired_at.
• credits — Append‑only ledger. Positive rows = grants/additions; negative rows = consumption. Columns: trans_no, user_uuid, trans_type, credits, order_no, expired_at.
• tasks — Usage jobs (e.g., text‑to‑video). Columns: credits_used, credits_trans_no link the task to the exact consumption ledger row.
• Cameos: apikeys, affiliates also hang off user_uuid for API access and referrals.

ER-ish cheat sheet (no foreign keys, but stable links):

• users.uuid → orders.user_uuid | credits.user_uuid | tasks.user_uuid | apikeys.user_uuid | affiliates.user_uuid
• orders.order_no → credits.order_no (grants from purchases)
• credits.trans_no → tasks.credits_trans_no (which consumption paid for a task)

Each table timestamps its changes so you can reconstruct history and power analytics.

---

The Analogy: Wallet + Receipts

• Think of credits as a transparent wallet. Every deposit or spend is a receipt you drop into the wallet.
• Deposits (positive rows) add to your cash. Spends (negative rows) take cash out. Expired gift cards? They’re still receipts, just no longer spendable.
• Your balance is simply: sum(all non‑expired positives) − sum(all negatives).

This is why the ledger is append‑only: instead of mutating old gifts, we add new receipts so history is always explainable.

---

One Action → Many Rows (Walkthroughs)

Checkout paid (Stripe)

1. orders: the row for order_no flips to paid with charge metadata.
2. credits: we add a positive grant row { trans_type: "order_pay", credits: <order.credits>, order_no }.
3. affiliates: optional reward update ties back to this order.

Spend credits on a task (Text → Video)

1. credits: we add a negative row { trans_type: "task_text_to_video", credits: -N } and return its trans_no.
2. tasks: we insert the task { credits_used: N, credits_trans_no: <that trans_no> } so you can trace “who spent what, where”.

Tiny ping demo (1 credit)

1. credits: negative row { trans_type: "ping", credits: -1 }.

Expiry

• Any positive row with expired_at in the past doesn’t count toward balance, but stays in history for audits.

---

Useful Services & APIs

• src/services/credit.ts#getUserCreditSummary — returns balance, granted, consumed, expired, expiringSoon[], and a trimmed ledger[].
• src/services/credit.ts#getUserCredits — lightweight snapshot for gating: { left_credits, is_pro, is_recharged }.
• src/services/credit.ts#decreaseCredits — appends a negative ledger row; throws on insufficient credits.
• src/services/credit.ts#increaseCredits — appends a positive ledger row; used by Stripe and grants.
• src/services/stripe.ts#handleCheckoutSession — turns a paid session into an order status + grant row.
• src/services/tasks.ts#createTextToVideoTask — decrements credits, then inserts a tasks row referencing that spend.

HTTP endpoints

• POST /api/account/credits — credit summary; accepts { includeLedger, ledgerLimit, includeExpiring }.
• POST /api/account/profile — profile + credit summary; send { includeCreditLedger: false } to keep it light.
• POST /api/ping — demo consumption; spends CreditsAmount.PingCost.

All responses use our respJson helpers so the shape is { code, message, data }.

---

Try It Locally

1. Run the app: pnpm dev (requires a Postgres URL in .env.local).

2. Create a user: visit /en/signup, register, and sign in.

3. Add credits manually (dev only) by inserting into credits:

   insert into credits (trans_no, user_uuid, trans_type, credits, created_at)
   values ('dev-grant-1', '<the-user-uuid>', 'manual_adjustment', 100, now());

   You can run this with pnpm drizzle-kit studio or your preferred SQL client.

4. Fetch balances: call /api/account/credits via HTTPie or curl:

   curl -X POST http://localhost:3000/api/account/credits \
     -H "Content-Type: application/json" \
     -H "Cookie: <copy auth cookies from the browser>"

5. Spend credits: call /api/ping with a message to subtract CreditsAmount.PingCost (defaults to 1 credit) and then re-run the balance query.

   curl -X POST http://localhost:3000/api/ping \
     -H "Content-Type: application/json" \
     -H "Cookie: <copy auth cookies from the browser>" \
     -d '{\"message\":\"hello\"}'

---

Customize the System

Change Credit Expiry Windows

Edit EXPIRING_WINDOW_DAYS in src/services/credit.ts to adjust the “expiring soon” warning horizon. To enforce auto-expiry, write a cron job that zeroes out rows and adds negative adjustments when expired_at is passed.

Seed Onboarding Bonuses

Use insertCredit from src/models/credit.ts right after insertUser to give new accounts a welcome balance. Keep the transaction number (trans_no) unique to avoid duplicates.

Integrate Real Payments

• The template assumes Stripe but any processor works: call insertOrder with your provider’s payload.
• Log the granted credits in credits and set order_no so reconciliations are simple.
• Update the docs or UI to show subscription metadata (sub_* columns) if you offer recurring plans.

Track More Context

Add columns (e.g. meta JSON) to credits/orders for feature flags or experiment IDs. Drizzle’s migration system will update the schema snapshot automatically.

---

Gotchas & Tips

• Balance math is simple sums; we don’t “consume” specific positive rows. Negative entries reference a likely source via order_no for traceability, but no mutation happens to old grants.
• getUserValidCredits orders positive grants by expired_at so consumption “tags” a nearby source for audits.
• Need strict FIFO or per‑grant drawdowns? Extend the model and keep the append‑only ledger for audits.
• Keep users.uuid stable; it’s the glue across orders, credits, tasks, keys, and affiliates.

---

Next Steps

• Add dashboard charts: getUserCreditSummary already exposes totals you can graph.
• Use the /api/ping handler as a template for other credit‑consuming features.
• Write unit tests for credit math; mock a list of ledger rows and assert on balance, expired, and expiringSoon.
• Want this in other languages? I can sync the updates to FR/ES/JA/ZH.