Checking authentication...

Store Credit & TCG Buyback

Operational reference for v1 (SQL-first staff ops). Migration: worker/migrations/0010_store_credit_buyback.sql.

Session across shop and main site

Login sets a db_session cookie on .dicebastion.com so the shop can share auth with the main site. Shop pages sync via utils.session.syncFromSharedCookie. Guests cannot use credit or buyback.

Concepts

• Balance — users.store_credit_pence (integer pence).
• Ledger — append-only store_credit_ledger. Every credit change writes a history row with an idempotency_key.
• Buyback case — buyback_cases + buyback_case_items. Customer submission; staff quote and credit via SQL until an admin UI exists.
• Spend targets — shop orders and paid event tickets only. Not memberships or donations.
• Guests — never see or use store credit.

Case statuses

Sub-£1 card charge rule

If applying available credit would leave a card remainder of 1–99p, credit is not applied and the full amount is charged on card (SumUp minimum £1.00). Credit applies only when the remainder is £0 or ≥ £1.00.

Credit is held as soon as a checkout starts, so a customer's balance drops before they pay by card. Held credit is given back when the card is declined, when the same customer starts another checkout more than 2 minutes later without paying the first, or by the nightly job for checkouts over an hour old. If a released checkout is paid later, the credit is taken again. If the customer has spent it by then, the order or ticket still goes through and staff get a "Store credit debit failed after card payment" email to settle the difference.

SQL recipes (D1)

Run against the production D1 database. Prefer a single batch / transaction mentally: update the case, then ledger + balance together. Use a unique idempotency_key every time.

1. Mark case under review

UPDATE buyback_cases
SET status = 'under_review', updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now')
WHERE id = :case_id;

2. Set quote

UPDATE buyback_cases
SET status = 'quoted',
    agreed_value_pence = :quote_pence,
    staff_comments = :comments,
    quoted_at = strftime('%Y-%m-%dT%H:%M:%fZ','now'),
    updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now')
WHERE id = :case_id;

3. Issue credit when buyback completes

Replace placeholders. :user_id must match the case owner. :credit_pence is usually agreed_value_pence.

Never type a new balance in by hand. A customer can spend credit between you reading the balance and writing it, and a typed balance would wipe out that spend. Every recipe below adds a change to the stored balance instead.

Run step A on its own first. If it fails on the unique idempotency_key, credit was already issued for that case: stop and do not run step B. If it reports 0 rows written, the user ID is wrong.

-- Check the case owner and that it has not been completed already
SELECT id, user_id, agreed_value_pence, status FROM buyback_cases WHERE id = :case_id;
– Step A: ledger row (balance_after is worked out from the live balance)
INSERT INTO store_credit_ledger (
user_id, delta_pence, balance_after_pence, entry_type,
reference_type, reference_id, idempotency_key, note, created_by, created_at
)
SELECT
user_id,
:credit_pence,
store_credit_pence + :credit_pence,
‘buyback_credit’,
‘buyback_case’,
CAST(:case_id AS TEXT),
‘buyback_credit:’ || CAST(:case_id AS TEXT),
‘Buyback case #’ || CAST(:case_id AS TEXT),
‘sql_admin’,
strftime(’%Y-%m-%dT%H:%M:%fZ’,’now’)
FROM users WHERE user_id = :user_id;
– Step B: balance and case
UPDATE users
SET store_credit_pence = store_credit_pence + :credit_pence,
updated_at = strftime(’%Y-%m-%dT%H:%M:%fZ’,’now’)
WHERE user_id = :user_id;
UPDATE buyback_cases
SET status = ‘completed’,
resolved_at = strftime(’%Y-%m-%dT%H:%M:%fZ’,’now’),
updated_at = strftime(’%Y-%m-%dT%H:%M:%fZ’,’now’)
WHERE id = :case_id;

4. Manual adjustment

:delta_pence is positive to add credit and negative to remove it. A removal that would take the balance below zero writes 0 rows in step A; do not run step B in that case.

-- Step A
INSERT INTO store_credit_ledger (
  user_id, delta_pence, balance_after_pence, entry_type,
  reference_type, reference_id, idempotency_key, note, created_by, created_at
)
SELECT
  user_id, :delta_pence, store_credit_pence + :delta_pence, 'adjustment',
  NULL, NULL,
  'adjustment:' || lower(hex(randomblob(8))),
  :note,
  'sql_admin',
  strftime('%Y-%m-%dT%H:%M:%fZ','now')
FROM users
WHERE user_id = :user_id AND store_credit_pence + :delta_pence >= 0;
– Step B
UPDATE users SET store_credit_pence = store_credit_pence + :delta_pence,
updated_at = strftime(’%Y-%m-%dT%H:%M:%fZ’,’now’)
WHERE user_id = :user_id AND store_credit_pence + :delta_pence >= 0;

5. Reverse a mistaken credit

Use a negative manual adjustment (recipe 4); never delete ledger rows. Do not write void rows by hand: the Worker uses them to release credit held by unpaid checkouts, and the accounts only count buyback_credit and adjustment rows as credit given out.

Inspect a case

SELECT * FROM buyback_cases WHERE id = :case_id;
SELECT * FROM buyback_case_items WHERE case_id = :case_id ORDER BY id;
SELECT * FROM store_credit_ledger WHERE user_id = :user_id ORDER BY created_at DESC LIMIT 20;

Card search APIs

• MTG — Scryfall (free fair-use). Worker proxies GET /buyback/cards/search?game=mtg&q=…. No API key. Descriptive User-Agent set server-side. Prices are never returned to the client.
• Riftbound — Scrydex (https://api.scrydex.com/riftbound/v1/…). Requires secrets SCRYDEX_API_KEY and SCRYDEX_TEAM_ID (wrangler secret put). Until set, Riftbound search returns 503 and the submit form blocks Riftbound.
• Cardmarket — not used for search (new API apps closed). Condition labels only: MT, NM, EX, GD, LP, PL, PO.

Compliance notes (Gibraltar)

• Customers sell second-hand cards to Dice Bastion; we are the buyer. We are not selling used goods to customers via this flow.
• Public terms: shop /buyback-terms/ (ownership on acceptance/completion, store credit only, 12-month validity policy without technical enforcement yet, non-transferable, no memberships/donations).
• Privacy: existing Privacy Policy covers account and case data.
• Have a Gibraltar-qualified advisor review the published terms before heavy marketing use.

Customer URLs

• Explainer: https://shop.dicebastion.com/sell-cards/
• Terms: https://shop.dicebastion.com/buyback-terms/
• Submit form: https://shop.dicebastion.com/sell-cards/submit/
• Account balance: https://dicebastion.com/account

Env

• BUYBACK_NOTIFY_EMAIL — staff notification on new cases (default SUPPORT_CONTACT_EMAIL).
• SCRYDEX_API_KEY, SCRYDEX_TEAM_ID — optional until Riftbound onboarding.