pangolin/docs/superpowers/plans/2026-07-09-pay-orchestration-p1-schema-grant.md

统一支付编排层 · P1 数据层 + 开通重构 Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

设计文档(全景蓝图): docs/pay-orchestration-design.html(架构/契约/取舍)。本计划是其 7 阶段中的 P1 落地;阅读版 docs/pay-orchestration-p1-plan.html。

Goal: 给控制面加支付订单数据层(pay_orders + pay_attempts)并把「开通订阅」逻辑从 codes 里抽成可复用、可传 source 的 subscription 包,为后续支付编排(P2+)打地基。

Architecture: 两张新表:pay_orders(业务订单,order_no 幂等,建单时锁定 user/plan/天数)+ pay_attempts(支付尝试,UNIQUE(provider,provider_ref) 幂等)。开通逻辑从 codes.Service.applySubscription 抽到新包 server/internal/subscription,Grant(...) 收 source 参数;subscriptions.source 枚举加 'pay'。codes 改为委托新包,行为不变。

Tech Stack: Go(裸 SQL + internal/db 方言层)· golang-migrate(migrations/{mysql,sqlite}/)· SQLite(modernc.org/sqlite,:memory: 测试)· MySQL(docker 集成测试)。

Global Constraints

• 金额一律 int64 最小单位存(USDT 微单位 1e-6 / CNY 分);禁 float。
• 迁移分 server/migrations/{mysql,sqlite}/ 两套,同号同名,up+down 成对;golang-migrate。
• 时间一律 Go 端算好传 ?;禁 NOW()/UTC_TIMESTAMP()/DATE_ADD()/FIELD() 等 MySQL 专属构造。
• upsert 用 dialect.Upsert(...);行锁用 dialect.LockForUpdate()。
• SQLite 改列级 CHECK/UNIQUE 须重建表(rename→建新→复制→drop→重建索引),单事务内完成。
• 新代码在 server/ 模块内(go build ./... 通过);测试 go test ./... 免 docker(SQLite 实库)。
• 订单生命周期中文词(仅显示层):created=初始化 / pending=等待付款 / paid=付款完成 / expired=已过期 / canceled=已取消。枚举值用英文。

---

Task 1: 迁移 000022 — pay_orders / pay_attempts 表 + subscriptions.source 加 'pay'

Files:

• Create: server/migrations/sqlite/000022_pay_orders.up.sql
• Create: server/migrations/sqlite/000022_pay_orders.down.sql
• Create: server/migrations/mysql/000022_pay_orders.up.sql
• Create: server/migrations/mysql/000022_pay_orders.down.sql

Interfaces:

• Produces: 表 pay_orders(列:id,order_no,user_id,user_uuid,sku,plan_code,duration_days,amount_minor,currency,status,subscription_id,created_at,paid_at,expires_at)、pay_attempts(列:id,order_no,method,provider,provider_ref,render_type,amount_minor,currency,status,created_at,paid_at,UNIQUE(provider,provider_ref));subscriptions.source 枚举含 'pay'。后续 P1 Task2/3 与 P2+ 依赖这些列名。

• Step 1: 写 sqlite up 迁移

server/migrations/sqlite/000022_pay_orders.up.sql:

-- 支付编排层数据层:业务订单 pay_orders + 支付尝试 pay_attempts。
-- 金额一律最小单位 int64(USDT 微单位 1e-6 / CNY 分)。
-- subscriptions.source 加 'pay'(SQLite 列级 CHECK 不能 ALTER,重建表)。

CREATE TABLE pay_orders (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  order_no        TEXT NOT NULL UNIQUE,
  user_id         INTEGER NOT NULL,
  user_uuid       TEXT NOT NULL,
  sku             TEXT NOT NULL,
  plan_code       TEXT NOT NULL,
  duration_days   INTEGER NOT NULL,
  amount_minor    INTEGER NOT NULL,
  currency        TEXT NOT NULL,
  status          TEXT NOT NULL CHECK (status IN ('created','pending','paid','expired','canceled')),
  subscription_id INTEGER NULL,
  created_at      DATETIME NOT NULL,
  paid_at         DATETIME NULL,
  expires_at      DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE INDEX idx_pay_orders_user ON pay_orders (user_id, created_at);

CREATE TABLE pay_attempts (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  order_no      TEXT NOT NULL,
  method        TEXT NOT NULL,
  provider      TEXT NOT NULL,
  provider_ref  TEXT NOT NULL,
  render_type   TEXT NOT NULL,
  amount_minor  INTEGER NOT NULL,
  currency      TEXT NOT NULL,
  status        TEXT NOT NULL CHECK (status IN ('created','pending','paid','failed','expired')),
  expires_at    DATETIME NOT NULL,   -- 本次尝试超时(哪吒 payurl / crypto 15min);超时只弃本 attempt
  created_at    DATETIME NOT NULL,
  paid_at       DATETIME NULL,
  UNIQUE (provider, provider_ref),
  FOREIGN KEY (order_no) REFERENCES pay_orders(order_no)
);
CREATE INDEX idx_pay_attempts_order ON pay_attempts (order_no);

-- subscriptions.source 加 'pay' —— 重建表(rename→建新→复制→drop→重建索引)。
ALTER TABLE subscriptions RENAME TO subscriptions_old;
CREATE TABLE subscriptions (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id     INTEGER NOT NULL,
  plan_id     INTEGER NOT NULL,
  expires_at  DATETIME NOT NULL,
  source      TEXT NOT NULL CHECK (source IN ('trial','code','pay')),
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (plan_id) REFERENCES plans(id)
);
INSERT INTO subscriptions (id, user_id, plan_id, expires_at, source, created_at)
SELECT id, user_id, plan_id, expires_at, source, created_at FROM subscriptions_old;
DROP TABLE subscriptions_old;
CREATE INDEX idx_subs_user_exp ON subscriptions (user_id, expires_at);

• Step 2: 写 sqlite down 迁移

server/migrations/sqlite/000022_pay_orders.down.sql:

DROP TABLE IF EXISTS pay_attempts;
DROP TABLE IF EXISTS pay_orders;

-- 回滚 source 枚举(重建回 'trial','code';回滚前须无 source='pay' 行,否则 INSERT 被 CHECK 拒)。
ALTER TABLE subscriptions RENAME TO subscriptions_old;
CREATE TABLE subscriptions (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id     INTEGER NOT NULL,
  plan_id     INTEGER NOT NULL,
  expires_at  DATETIME NOT NULL,
  source      TEXT NOT NULL CHECK (source IN ('trial','code')),
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (plan_id) REFERENCES plans(id)
);
INSERT INTO subscriptions (id, user_id, plan_id, expires_at, source, created_at)
SELECT id, user_id, plan_id, expires_at, source, created_at FROM subscriptions_old;
DROP TABLE subscriptions_old;
CREATE INDEX idx_subs_user_exp ON subscriptions (user_id, expires_at);

• Step 3: 写 mysql up 迁移

server/migrations/mysql/000022_pay_orders.up.sql:

CREATE TABLE pay_orders (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no        VARCHAR(64) NOT NULL UNIQUE,
  user_id         BIGINT UNSIGNED NOT NULL,
  user_uuid       CHAR(36) NOT NULL,
  sku             VARCHAR(64) NOT NULL,
  plan_code       VARCHAR(32) NOT NULL,
  duration_days   INT NOT NULL,
  amount_minor    BIGINT NOT NULL,
  currency        VARCHAR(16) NOT NULL,
  status          ENUM('created','pending','paid','expired','canceled') NOT NULL,
  subscription_id BIGINT UNSIGNED NULL,
  created_at      DATETIME(6) NOT NULL,
  paid_at         DATETIME(6) NULL,
  expires_at      DATETIME(6) NOT NULL,
  INDEX idx_pay_orders_user (user_id, created_at),
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE pay_attempts (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no      VARCHAR(64) NOT NULL,
  method        VARCHAR(32) NOT NULL,
  provider      VARCHAR(32) NOT NULL,
  provider_ref  VARCHAR(128) NOT NULL,
  render_type   VARCHAR(32) NOT NULL,
  amount_minor  BIGINT NOT NULL,
  currency      VARCHAR(16) NOT NULL,
  status        ENUM('created','pending','paid','failed','expired') NOT NULL,
  expires_at    DATETIME(6) NOT NULL,
  created_at    DATETIME(6) NOT NULL,
  paid_at       DATETIME(6) NULL,
  UNIQUE KEY uq_provider_ref (provider, provider_ref),
  INDEX idx_pay_attempts_order (order_no),
  FOREIGN KEY (order_no) REFERENCES pay_orders(order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE subscriptions MODIFY source ENUM('trial','code','pay') NOT NULL;

• Step 4: 写 mysql down 迁移

server/migrations/mysql/000022_pay_orders.down.sql:

DROP TABLE IF EXISTS pay_attempts;
DROP TABLE IF EXISTS pay_orders;
ALTER TABLE subscriptions MODIFY source ENUM('trial','code') NOT NULL;

• Step 5: 在临时 sqlite 库上跑 up 验证建表成功

Run:

cd server && rm -f /tmp/p1.db && DB_DRIVER=sqlite DB_DSN=/tmp/p1.db go run ./cmd/migrate up && \
  sqlite3 /tmp/p1.db ".tables" | tr ' ' '\n' | grep -E 'pay_orders|pay_attempts' && \
  sqlite3 /tmp/p1.db "SELECT sql FROM sqlite_master WHERE name='subscriptions';" | grep -q "'pay'" && echo OK-UP

Expected: 打印 pay_attempts / pay_orders 两行 + OK-UP(subscriptions 含 'pay')。

• Step 6: 跑 down 验证干净回滚

Run:

cd server && DB_DRIVER=sqlite DB_DSN=/tmp/p1.db go run ./cmd/migrate down 1 && \
  sqlite3 /tmp/p1.db ".tables" | tr ' ' '\n' | grep -Eq 'pay_orders' && echo "FAIL: 表还在" || echo OK-DOWN

Expected: OK-DOWN(pay_orders 已删)。若 cmd/migrate 无 down N 子命令,改用 migrate -database ... 或按 cmd/migrate/main.go 实际子命令名调整。

• Step 7: Commit

cd /Users/wangjia/code/pangolin/.claude/worktrees/macos-killswitch
git add server/migrations/sqlite/000022_pay_orders.*.sql server/migrations/mysql/000022_pay_orders.*.sql
git commit -m "feat(pay): 迁移 000022 pay_orders/pay_attempts + subscriptions.source 加 pay"

---

Task 2: subscription 包 — 抽出可传 source 的开通逻辑

Files:

• Create: server/internal/subscription/grant.go
• Create: server/internal/subscription/grant_test.go

Interfaces:

• Consumes: 表 subscriptions/plans(Task 1)。

• Produces:

  • type Granter struct{ db *sql.DB }
  • func NewGranter(db *sql.DB) *Granter
  • func (g *Granter) Grant(ctx context.Context, tx *sql.Tx, userID, planID int64, durationDays int, source string) (subID int64, expiresAt time.Time, err error) — 复刻现 applySubscription 规则:同 plan 有活跃订阅→ExtendSubscription(max(expires,now)+days);否则→CreateSubscription(max(now, 同plan最晚)+days)。source 写进新建行。
  • func (g *Granter) PlanID(ctx context.Context, code string) (int64, error) — plan code→id。

• Step 1: 写失败测试(sqlite 实库)

server/internal/subscription/grant_test.go:

package subscription_test

import (
	"context"
	"database/sql"
	"testing"
	"time"

	_ "modernc.org/sqlite"
	"github.com/wangjia/pangolin/server/internal/subscription"
)

// openSeeded 建一个含 plans/subscriptions/users 最小 schema 的内存库并塞 pro 套餐 + 一个用户。
func openSeeded(t *testing.T) *sql.DB {
	t.Helper()
	db, err := sql.Open("sqlite", "file::memory:?cache=shared&_txlock=immediate")
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { _ = db.Close() })
	stmts := []string{
		`CREATE TABLE users (id INTEGER PRIMARY KEY, uuid TEXT)`,
		`CREATE TABLE plans (id INTEGER PRIMARY KEY, code TEXT UNIQUE)`,
		`CREATE TABLE subscriptions (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, plan_id INTEGER,
		   expires_at DATETIME NOT NULL, source TEXT NOT NULL CHECK (source IN ('trial','code','pay')),
		   created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)`,
		`INSERT INTO users (id, uuid) VALUES (1, 'u-1')`,
		`INSERT INTO plans (id, code) VALUES (10, 'pro')`,
	}
	for _, s := range stmts {
		if _, err := db.Exec(s); err != nil {
			t.Fatalf("seed: %v", err)
		}
	}
	return db
}

func TestGrantCreatesThenExtends(t *testing.T) {
	db := openSeeded(t)
	g := subscription.NewGranter(db)
	ctx := context.Background()

	planID, err := g.PlanID(ctx, "pro")
	if err != nil || planID != 10 {
		t.Fatalf("PlanID pro = %d, %v", planID, err)
	}

	tx, _ := db.Begin()
	subID, exp1, err := g.Grant(ctx, tx, 1, planID, 30, "pay")
	if err != nil {
		t.Fatalf("grant#1: %v", err)
	}
	_ = tx.Commit()
	if subID == 0 || time.Until(exp1) < 29*24*time.Hour {
		t.Fatalf("grant#1 subID=%d exp=%v", subID, exp1)
	}

	// 同 plan 再开 30 天 → 应在原到期上延长(≈60 天),不新建行。
	tx2, _ := db.Begin()
	_, exp2, err := g.Grant(ctx, tx2, 1, planID, 30, "pay")
	if err != nil {
		t.Fatalf("grant#2: %v", err)
	}
	_ = tx2.Commit()
	if exp2.Sub(exp1) < 29*24*time.Hour {
		t.Fatalf("续期未叠加:exp1=%v exp2=%v", exp1, exp2)
	}

	var n int
	db.QueryRow(`SELECT COUNT(*) FROM subscriptions WHERE user_id=1`).Scan(&n)
	if n != 1 {
		t.Fatalf("应只有 1 行订阅(延长非新建),实际 %d", n)
	}
	var src string
	db.QueryRow(`SELECT source FROM subscriptions WHERE user_id=1`).Scan(&src)
	if src != "pay" {
		t.Fatalf("source 应为 pay,实际 %s", src)
	}
}

• Step 2: 跑测试确认失败

Run: cd server && go test ./internal/subscription/ -run TestGrantCreatesThenExtends -v Expected: 编译失败 / FAIL —— subscription 包不存在。

• Step 3: 写实现

server/internal/subscription/grant.go:

// Package subscription owns "开通/续期订阅" —— 从 codes 抽出,供 codes 兑换与 pay 支付共用。
// 规则:同 plan 有活跃订阅→延长最晚一条;否则→新建。source 记来源('trial'|'code'|'pay')。
package subscription

import (
	"context"
	"database/sql"
	"fmt"
	"time"
)

type Granter struct{ db *sql.DB }

func NewGranter(db *sql.DB) *Granter { return &Granter{db: db} }

// PlanID maps a plans.code to its primary key.
func (g *Granter) PlanID(ctx context.Context, code string) (int64, error) {
	var id int64
	if err := g.db.QueryRowContext(ctx, `SELECT id FROM plans WHERE code=?`, code).Scan(&id); err != nil {
		return 0, fmt.Errorf("subscription.PlanID(%s): %w", code, err)
	}
	return id, nil
}

// Grant extends the same-plan subscription or creates a new one, inside tx.
// Base-date math is done in Go (portable across MySQL/SQLite).
func (g *Granter) Grant(ctx context.Context, tx *sql.Tx, userID, planID int64, durationDays int, source string) (int64, time.Time, error) {
	now := time.Now().UTC()

	// 找同 plan 未过期订阅里到期最晚的一条。
	var subID int64
	var latest time.Time
	err := tx.QueryRowContext(ctx,
		`SELECT id, expires_at FROM subscriptions
		 WHERE user_id=? AND plan_id=? AND expires_at > ?
		 ORDER BY expires_at DESC LIMIT 1`,
		userID, planID, now).Scan(&subID, &latest)
	switch {
	case err == sql.ErrNoRows:
		// 新建:base = max(now, 同 plan 任意最晚到期)。
		var anyLatest sql.NullTime
		_ = tx.QueryRowContext(ctx,
			`SELECT MAX(expires_at) FROM subscriptions WHERE user_id=? AND plan_id=?`,
			userID, planID).Scan(&anyLatest)
		base := now
		if anyLatest.Valid && anyLatest.Time.After(base) {
			base = anyLatest.Time.UTC()
		}
		expiresAt := base.AddDate(0, 0, durationDays)
		res, err := tx.ExecContext(ctx,
			`INSERT INTO subscriptions (user_id, plan_id, expires_at, source, created_at)
			 VALUES (?, ?, ?, ?, ?)`,
			userID, planID, expiresAt, source, now)
		if err != nil {
			return 0, time.Time{}, fmt.Errorf("subscription.Grant insert: %w", err)
		}
		id, _ := res.LastInsertId()
		return id, expiresAt, nil
	case err != nil:
		return 0, time.Time{}, fmt.Errorf("subscription.Grant read: %w", err)
	}

	// 延长:base = max(latest, now)。
	base := latest.UTC()
	if now.After(base) {
		base = now
	}
	expiresAt := base.AddDate(0, 0, durationDays)
	if _, err := tx.ExecContext(ctx,
		`UPDATE subscriptions SET expires_at=? WHERE id=?`, expiresAt, subID); err != nil {
		return 0, time.Time{}, fmt.Errorf("subscription.Grant extend: %w", err)
	}
	return subID, expiresAt, nil
}

• Step 4: 跑测试确认通过

Run: cd server && go test ./internal/subscription/ -run TestGrantCreatesThenExtends -v Expected: PASS。

• Step 5: Commit

cd /Users/wangjia/code/pangolin/.claude/worktrees/macos-killswitch
git add server/internal/subscription/
git commit -m "feat(pay): subscription 包抽出可传 source 的开通逻辑(Grant/PlanID)"

---

Task 3: codes 改为委托 subscription.Grant(行为不变)

Files:

• Modify: server/internal/codes/service.go:235-287(applySubscription 改为调 subscription.Grant,source 传 "code")
• Modify: server/internal/codes/service.go(Service 结构加 granter *subscription.Granter;NewService 构造它)
• Modify: server/cmd/server/main.go(若 NewService 签名变,更新调用)

Interfaces:

• Consumes: subscription.NewGranter(db)、(*Granter).Grant(ctx, tx, userID, planID, days, source)(Task 2)。

• Produces: codes 兑换行为不变(source 仍写 'code'),但开通走 subscription 包。

• Step 1: 跑现有 codes 测试记录基线(全绿)

Run: cd server && go test ./internal/codes/ 2>&1 | tail -3 Expected: ok .../internal/codes(记住这是重构后必须仍绿的基线)。

• Step 2: 改 Service 持有 granter 并委托

server/internal/codes/service.go —— 给 Service 加字段(在其结构定义处)granter *subscription.Granter,NewService 里 granter: subscription.NewGranter(store.DB())(若 Store 无 DB() getter,加一个 func (s *Store) DB() *sql.DB { return s.db })。然后把 applySubscription 体替换为委托:

func (svc *Service) applySubscription(
	ctx context.Context,
	tx *sql.Tx,
	userID int64,
	cr *CodeRow,
) (subID int64, expiresAt time.Time, apiErr *apierr.Error) {
	id, exp, err := svc.granter.Grant(ctx, tx, userID, cr.PlanID, cr.DurationDays, "code")
	if err != nil {
		return 0, time.Time{}, apierr.ErrInternal
	}
	return id, exp, nil
}

并在 import 块加 "github.com/wangjia/pangolin/server/internal/subscription"。

• Step 3: 编译

Run: cd server && go build ./... Expected: 无错误。若 NewService 参数未变(仍 NewService(store, rdb, ...)),main.go 无需改。

• Step 4: 跑 codes 全套测试确认行为不变

Run: cd server && go test ./internal/codes/ ./internal/subscription/ 2>&1 | tail -4 Expected: 两包均 ok(codes 兑换/幂等/并发单赢家等全绿,证明重构未改行为)。

• Step 5: 删除 codes.Store 里已迁走的重复方法(可选,若无其他调用者)

Run 先查引用:

cd server && grep -rn "\.GetActiveSubscriptions\|\.ExtendSubscription\|\.CreateSubscription\b" internal/ | grep -v _test.go

若仅 subscription 包内部有等价 SQL、codes 已不再调这三个 Store 方法,则从 server/internal/codes/store.go:184-261 删除它们及 SubscriptionRow(若无其他引用),减少重复。有任何其他调用者则保留,不动。

• Step 6: 编译 + 全量测试

Run: cd server && go build ./... && go test ./internal/codes/ ./internal/subscription/ 2>&1 | tail -4 Expected: 编译通过、两包 ok。

• Step 7: Commit

cd /Users/wangjia/code/pangolin/.claude/worktrees/macos-killswitch
git add server/internal/codes/ server/cmd/server/main.go
git commit -m "refactor(pay): codes 开通委托 subscription.Grant(行为不变,source=code)"

---

Task 4: payorders 包 — Order/Attempt 数据访问

Files:

• Create: server/internal/payorders/store.go
• Create: server/internal/payorders/store_test.go

Interfaces:

• Consumes: 表 pay_orders/pay_attempts(Task 1)。

• Produces:

  • type Order struct{ OrderNo string; UserID int64; UserUUID, SKU, PlanCode string; DurationDays int; AmountMinor int64; Currency, Status string; CreatedAt, ExpiresAt time.Time; PaidAt sql.NullTime; SubscriptionID sql.NullInt64 }
  • type Attempt struct{ ID int64; OrderNo, Method, Provider, ProviderRef, RenderType string; AmountMinor int64; Currency, Status string; ExpiresAt, CreatedAt time.Time; PaidAt sql.NullTime }
  • type Store struct{ db *sql.DB } · NewStore(db) *Store · func (s *Store) DB() *sql.DB
  • CreateOrder(ctx, Order) error · GetOrder(ctx, orderNo) (*Order, error)(ErrNotFound)· ListOrders(ctx, userID int64, limit int, beforeID int64) ([]Order, error) —— 返回全部状态(含 canceled),供订单历史页。
  • CreateAttempt(ctx, Attempt) (int64, error) · AttemptByProviderRef(ctx, provider, ref string) (*Attempt, error)
  • MarkOrderPaid(ctx, tx *sql.Tx, orderNo, provider, ref string, subID int64, at time.Time) (bool, error) —— 事务内幂等:仅当 order.status='pending' 时置 paid + 回填 subscription_id + 对应 attempt 置 paid;非 pending(已 paid/取消/过期)返回 false,nil。这是「一单只一个 attempt 成功」的守卫。
  • CancelOrder(ctx, orderNo string) (bool, error) —— 仅当 order.status='pending' 时置 canceled,返回 true;非 pending 返回 false,nil。
  • var ErrNotFound = errors.New("payorders: not found")

• Step 1: 写失败测试(sqlite 实库,建最小 schema)

server/internal/payorders/store_test.go:

package payorders_test

import (
	"context"
	"testing"
	"time"

	_ "modernc.org/sqlite"
	"database/sql"
	"github.com/wangjia/pangolin/server/internal/payorders"
)

func openPay(t *testing.T) *sql.DB {
	t.Helper()
	db, err := sql.Open("sqlite", "file::memory:?cache=shared&_txlock=immediate")
	if err != nil { t.Fatal(err) }
	t.Cleanup(func() { _ = db.Close() })
	ddl := []string{
		`CREATE TABLE pay_orders (id INTEGER PRIMARY KEY AUTOINCREMENT, order_no TEXT UNIQUE, user_id INTEGER,
		  user_uuid TEXT, sku TEXT, plan_code TEXT, duration_days INTEGER, amount_minor INTEGER, currency TEXT,
		  status TEXT, subscription_id INTEGER NULL, created_at DATETIME, paid_at DATETIME NULL, expires_at DATETIME)`,
		`CREATE TABLE pay_attempts (id INTEGER PRIMARY KEY AUTOINCREMENT, order_no TEXT, method TEXT, provider TEXT,
		  provider_ref TEXT, render_type TEXT, amount_minor INTEGER, currency TEXT, status TEXT,
		  expires_at DATETIME, created_at DATETIME, paid_at DATETIME NULL, UNIQUE(provider, provider_ref))`,
	}
	for _, s := range ddl {
		if _, err := db.Exec(s); err != nil { t.Fatalf("ddl: %v", err) }
	}
	return db
}

func TestCreateGetMarkPaidIdempotent(t *testing.T) {
	db := openPay(t)
	st := payorders.NewStore(db)
	ctx := context.Background()
	now := time.Now().UTC()

	o := payorders.Order{
		OrderNo: "PAY-1", UserID: 1, UserUUID: "u-1", SKU: "pro-year", PlanCode: "pro",
		DurationDays: 365, AmountMinor: 29990000, Currency: "USDT", Status: "pending",
		CreatedAt: now, ExpiresAt: now.Add(15 * time.Minute),
	}
	if err := st.CreateOrder(ctx, o); err != nil { t.Fatalf("create: %v", err) }

	got, err := st.GetOrder(ctx, "PAY-1")
	if err != nil || got.AmountMinor != 29990000 || got.Status != "pending" {
		t.Fatalf("get: %+v %v", got, err)
	}

	if _, err := st.CreateAttempt(ctx, payorders.Attempt{
		OrderNo: "PAY-1", Method: "usdt_trc20", Provider: "crypto", ProviderRef: "P-9",
		RenderType: "display_details", AmountMinor: 29990000, Currency: "USDT",
		Status: "pending", ExpiresAt: now.Add(15 * time.Minute), CreatedAt: now,
	}); err != nil { t.Fatalf("attempt: %v", err) }

	tx, _ := db.Begin()
	ok, err := st.MarkOrderPaid(ctx, tx, "PAY-1", "crypto", "P-9", 77, now)
	_ = tx.Commit()
	if err != nil || !ok { t.Fatalf("mark#1 ok=%v err=%v", ok, err) }

	// 幂等:再标一次 → false。
	tx2, _ := db.Begin()
	ok2, _ := st.MarkOrderPaid(ctx, tx2, "PAY-1", "crypto", "P-9", 77, now)
	_ = tx2.Commit()
	if ok2 { t.Fatalf("第二次 MarkOrderPaid 应 false(幂等)") }

	final, _ := st.GetOrder(ctx, "PAY-1")
	if final.Status != "paid" || !final.SubscriptionID.Valid || final.SubscriptionID.Int64 != 77 {
		t.Fatalf("final: %+v", final)
	}
}

func TestCancelOrder(t *testing.T) {
	db := openPay(t)
	st := payorders.NewStore(db)
	ctx := context.Background()
	now := time.Now().UTC()

	must := payorders.Order{
		OrderNo: "PAY-C", UserID: 1, UserUUID: "u-1", SKU: "pro-month", PlanCode: "pro",
		DurationDays: 30, AmountMinor: 3990000, Currency: "USDT", Status: "pending",
		CreatedAt: now, ExpiresAt: now.Add(time.Hour),
	}
	if err := st.CreateOrder(ctx, must); err != nil { t.Fatalf("create: %v", err) }

	ok, err := st.CancelOrder(ctx, "PAY-C")
	if err != nil || !ok { t.Fatalf("cancel#1 ok=%v err=%v", ok, err) }

	// 再取消 → false(非 pending)。
	ok2, _ := st.CancelOrder(ctx, "PAY-C")
	if ok2 { t.Fatalf("第二次 CancelOrder 应 false") }

	got, _ := st.GetOrder(ctx, "PAY-C")
	if got.Status != "canceled" { t.Fatalf("status = %s, 应 canceled", got.Status) }

	// canceled 单仍在 ListOrders 出现。
	list, _ := st.ListOrders(ctx, 1, 20, 0)
	var seen bool
	for _, o := range list {
		if o.OrderNo == "PAY-C" { seen = true }
	}
	if !seen { t.Fatalf("canceled 单应出现在订单列表") }
}

• Step 2: 跑测试确认失败

Run: cd server && go test ./internal/payorders/ -run TestCreateGetMarkPaidIdempotent -v Expected: 编译失败 —— payorders 包不存在。

• Step 3: 写实现

server/internal/payorders/store.go:

// Package payorders is the control-plane payment ledger: business orders
// (pay_orders) + payment attempts (pay_attempts). Amounts are int64 minor units.
package payorders

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"
)

var ErrNotFound = errors.New("payorders: not found")

type Order struct {
	OrderNo        string
	UserID         int64
	UserUUID       string
	SKU            string
	PlanCode       string
	DurationDays   int
	AmountMinor    int64
	Currency       string
	Status         string
	CreatedAt      time.Time
	ExpiresAt      time.Time
	PaidAt         sql.NullTime
	SubscriptionID sql.NullInt64
}

type Attempt struct {
	ID           int64
	OrderNo      string
	Method       string
	Provider     string
	ProviderRef  string
	RenderType   string
	AmountMinor  int64
	Currency     string
	Status       string
	ExpiresAt    time.Time
	CreatedAt    time.Time
	PaidAt       sql.NullTime
}

type Store struct{ db *sql.DB }

func NewStore(db *sql.DB) *Store { return &Store{db: db} }
func (s *Store) DB() *sql.DB     { return s.db }

func (s *Store) CreateOrder(ctx context.Context, o Order) error {
	_, err := s.db.ExecContext(ctx,
		`INSERT INTO pay_orders (order_no, user_id, user_uuid, sku, plan_code, duration_days,
		   amount_minor, currency, status, created_at, expires_at)
		 VALUES (?,?,?,?,?,?,?,?,?,?,?)`,
		o.OrderNo, o.UserID, o.UserUUID, o.SKU, o.PlanCode, o.DurationDays,
		o.AmountMinor, o.Currency, o.Status, o.CreatedAt.UTC(), o.ExpiresAt.UTC())
	if err != nil {
		return fmt.Errorf("payorders.CreateOrder: %w", err)
	}
	return nil
}

const orderCols = `order_no, user_id, user_uuid, sku, plan_code, duration_days,
	amount_minor, currency, status, subscription_id, created_at, paid_at, expires_at`

func scanOrder(row interface{ Scan(...any) error }) (*Order, error) {
	var o Order
	if err := row.Scan(&o.OrderNo, &o.UserID, &o.UserUUID, &o.SKU, &o.PlanCode, &o.DurationDays,
		&o.AmountMinor, &o.Currency, &o.Status, &o.SubscriptionID, &o.CreatedAt, &o.PaidAt, &o.ExpiresAt); err != nil {
		return nil, err
	}
	return &o, nil
}

func (s *Store) GetOrder(ctx context.Context, orderNo string) (*Order, error) {
	o, err := scanOrder(s.db.QueryRowContext(ctx,
		`SELECT `+orderCols+` FROM pay_orders WHERE order_no=?`, orderNo))
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("payorders.GetOrder: %w", err)
	}
	return o, nil
}

// ListOrders returns the user's orders newest-first; beforeID=0 means from the top.
func (s *Store) ListOrders(ctx context.Context, userID int64, limit int, beforeID int64) ([]Order, error) {
	if limit <= 0 || limit > 100 {
		limit = 20
	}
	q := `SELECT ` + orderCols + ` FROM pay_orders WHERE user_id=?`
	args := []any{userID}
	if beforeID > 0 {
		q += ` AND id < ?`
		args = append(args, beforeID)
	}
	q += ` ORDER BY id DESC LIMIT ?`
	args = append(args, limit)
	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, fmt.Errorf("payorders.ListOrders: %w", err)
	}
	defer rows.Close()
	var out []Order
	for rows.Next() {
		o, err := scanOrder(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *o)
	}
	return out, rows.Err()
}

func (s *Store) CreateAttempt(ctx context.Context, a Attempt) (int64, error) {
	res, err := s.db.ExecContext(ctx,
		`INSERT INTO pay_attempts (order_no, method, provider, provider_ref, render_type,
		   amount_minor, currency, status, expires_at, created_at)
		 VALUES (?,?,?,?,?,?,?,?,?,?)`,
		a.OrderNo, a.Method, a.Provider, a.ProviderRef, a.RenderType,
		a.AmountMinor, a.Currency, a.Status, a.ExpiresAt.UTC(), a.CreatedAt.UTC())
	if err != nil {
		return 0, fmt.Errorf("payorders.CreateAttempt: %w", err)
	}
	id, _ := res.LastInsertId()
	return id, nil
}

func (s *Store) AttemptByProviderRef(ctx context.Context, provider, ref string) (*Attempt, error) {
	var a Attempt
	err := s.db.QueryRowContext(ctx,
		`SELECT id, order_no, method, provider, provider_ref, render_type, amount_minor,
		   currency, status, expires_at, created_at, paid_at
		 FROM pay_attempts WHERE provider=? AND provider_ref=?`, provider, ref).
		Scan(&a.ID, &a.OrderNo, &a.Method, &a.Provider, &a.ProviderRef, &a.RenderType,
			&a.AmountMinor, &a.Currency, &a.Status, &a.ExpiresAt, &a.CreatedAt, &a.PaidAt)
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("payorders.AttemptByProviderRef: %w", err)
	}
	return &a, nil
}

// CancelOrder flips a pending order to canceled. Returns false if the order was
// not in 'pending' (already paid / canceled / expired).
func (s *Store) CancelOrder(ctx context.Context, orderNo string) (bool, error) {
	res, err := s.db.ExecContext(ctx,
		`UPDATE pay_orders SET status='canceled' WHERE order_no=? AND status='pending'`, orderNo)
	if err != nil {
		return false, fmt.Errorf("payorders.CancelOrder: %w", err)
	}
	n, _ := res.RowsAffected()
	return n > 0, nil
}

// MarkOrderPaid flips a pending order to paid inside tx (idempotent). Returns
// false if the order was not in 'pending' (already handled / expired).
func (s *Store) MarkOrderPaid(ctx context.Context, tx *sql.Tx, orderNo, provider, ref string, subID int64, at time.Time) (bool, error) {
	res, err := tx.ExecContext(ctx,
		`UPDATE pay_orders SET status='paid', subscription_id=?, paid_at=?
		 WHERE order_no=? AND status='pending'`, subID, at.UTC(), orderNo)
	if err != nil {
		return false, fmt.Errorf("payorders.MarkOrderPaid order: %w", err)
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return false, nil
	}
	if _, err := tx.ExecContext(ctx,
		`UPDATE pay_attempts SET status='paid', paid_at=? WHERE provider=? AND provider_ref=?`,
		at.UTC(), provider, ref); err != nil {
		return false, fmt.Errorf("payorders.MarkOrderPaid attempt: %w", err)
	}
	return true, nil
}

• Step 4: 跑测试确认通过(含取消/列表)

Run: cd server && go test ./internal/payorders/ -v Expected: TestCreateGetMarkPaidIdempotent 与 TestCancelOrder 均 PASS。

• Step 5: 全量编译 + 测试

Run: cd server && go build ./... && go test ./internal/payorders/ ./internal/subscription/ ./internal/codes/ 2>&1 | tail -5 Expected: 编译通过,三包 ok。

• Step 6: Commit

cd /Users/wangjia/code/pangolin/.claude/worktrees/macos-killswitch
git add server/internal/payorders/
git commit -m "feat(pay): payorders 包 Order/Attempt 数据访问 + 幂等 MarkOrderPaid"

---

Self-Review

Spec coverage(P1 范围): 迁移(pay_orders/pay_attempts/source=pay)= Task1 ✓;开通重构可传 source = Task2+3 ✓;订单数据访问(建单/查单/列表/幂等标付)= Task4 ✓。P1 只覆盖数据层 + 开通原语,不含 HTTP/adapter/客户端(P2+)。

Placeholder scan: 无 TBD;所有 SQL/Go/测试均完整给出。Task1 Step6 对 cmd/migrate 子命令名留了「按实际调整」的注记——因未逐字读 cmd/migrate/main.go,执行者据实调整,不影响建表正确性。

Type consistency: Granter.Grant(ctx,tx,userID,planID,days,source) 在 Task2 定义、Task3 调用一致;payorders.Order/Attempt 字段在 Task4 测试与实现一致;MarkOrderPaid 幂等语义(status='pending' 才动)测试与实现一致。

---

后续阶段(各自独立成计划,落地前逐一细化)

• P2 Provider 抽象/注册表 + Order/Attempt service + 客户端 REST(methods/orders/get/list/retry)+ 统一开通管线 + Query 轮询 worker(用 fake provider 测)。
• P3 crypto adapter(包裹 pangolin-pay)+ pay-server 出站 webhook + /v1/webhooks/pay/crypto。
• P4 哪吒 adapter(RSA 签/验、GET 回调、查单;密钥 Bitwarden nzzf:shop_id/ShopPrivateKey/PublicPlateKey)+ /v1/webhooks/pay/nazha。
• P5 四端 Flutter 支付页(方法选择器 + render_type 分发 redirect/display_details + 轮询)+ 接上现有购买入口。
• P6 用户中心订单历史页(读 GET /v1/pay/orders,新增导航;return_url 跳此)。
• P7 端到端联调(两轨小额真链/真单)。