# 统一支付编排层 · 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`:
```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`:
```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`:
```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`:
```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:
```bash
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:
```bash
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**

```bash
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`:
```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`:
```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**

```bash
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` 体替换为委托:
```go
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 先查引用:
```bash
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**

```bash
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`:
```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`:
```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**

```bash
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** 端到端联调(两轨小额真链/真单)。