root
70725193eb
Addresses findings from consolidated Phase 1 code review: - Add composite unique indexes + composite FKs so subscription_prompts and packages cannot reference mismatched (tenant_id, package_id, prompt_id) tuples, enforcing ACL lineage at the schema level. - Add status CHECK constraints on all KOL tables as defense-in-depth. - ListActiveSubscribersForPackage: drop meaningless tenant_id IS NOT NULL, add end_at expiry filter so fan-out skips expired subscribers. - ApproveKolSubscription: add status='pending' guard to prevent double-approval. - ListSubscriptionPromptsByTenant: join subscription/prompt/package/profile lifecycle so hidden/archived content no longer leaks; surface kol_display_name for UI. - ListPublishedKolPackages: exclude expired subscribers from marketplace count. - KolDashboardTrend: rewrite as CTE with day-series, returning both daily usage and new subscription counts. - Remove cosmetic tenant_id IS NOT NULL from dashboard overview queries (the column is NOT NULL by schema). - check_tenant_scope.sh: require tenant_id = sqlc.(n?)arg filter, reject tenant_id IS NOT NULL as a fake scope, maintain explicit exemption list. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
330 lines
10 KiB
Go
330 lines
10 KiB
Go
// Code generated by sqlc. DO NOT EDIT.
|
|
// versions:
|
|
// sqlc v1.30.0
|
|
// source: kol_usage.sql
|
|
|
|
package generated
|
|
|
|
import (
|
|
"context"
|
|
|
|
"github.com/jackc/pgx/v5/pgtype"
|
|
)
|
|
|
|
const createKolUsageLog = `-- name: CreateKolUsageLog :one
|
|
INSERT INTO kol_usage_logs (tenant_id, subscription_id, subscription_prompt_id,
|
|
package_id, prompt_id, prompt_revision_no,
|
|
generation_task_id, status, variables_snapshot,
|
|
schema_snapshot, rendered_prompt_hash)
|
|
VALUES ($1::bigint, $2,
|
|
$3, $4,
|
|
$5, $6,
|
|
$7, 'pending',
|
|
$8::jsonb, $9::jsonb,
|
|
$10)
|
|
RETURNING id, tenant_id, generation_task_id, status, created_at
|
|
`
|
|
|
|
type CreateKolUsageLogParams struct {
|
|
TenantID int64 `json:"tenant_id"`
|
|
SubscriptionID pgtype.Int8 `json:"subscription_id"`
|
|
SubscriptionPromptID pgtype.Int8 `json:"subscription_prompt_id"`
|
|
PackageID int64 `json:"package_id"`
|
|
PromptID int64 `json:"prompt_id"`
|
|
PromptRevisionNo int32 `json:"prompt_revision_no"`
|
|
GenerationTaskID pgtype.Int8 `json:"generation_task_id"`
|
|
VariablesSnapshot []byte `json:"variables_snapshot"`
|
|
SchemaSnapshot []byte `json:"schema_snapshot"`
|
|
RenderedPromptHash pgtype.Text `json:"rendered_prompt_hash"`
|
|
}
|
|
|
|
type CreateKolUsageLogRow struct {
|
|
ID int64 `json:"id"`
|
|
TenantID int64 `json:"tenant_id"`
|
|
GenerationTaskID pgtype.Int8 `json:"generation_task_id"`
|
|
Status string `json:"status"`
|
|
CreatedAt pgtype.Timestamptz `json:"created_at"`
|
|
}
|
|
|
|
func (q *Queries) CreateKolUsageLog(ctx context.Context, arg CreateKolUsageLogParams) (CreateKolUsageLogRow, error) {
|
|
row := q.db.QueryRow(ctx, createKolUsageLog,
|
|
arg.TenantID,
|
|
arg.SubscriptionID,
|
|
arg.SubscriptionPromptID,
|
|
arg.PackageID,
|
|
arg.PromptID,
|
|
arg.PromptRevisionNo,
|
|
arg.GenerationTaskID,
|
|
arg.VariablesSnapshot,
|
|
arg.SchemaSnapshot,
|
|
arg.RenderedPromptHash,
|
|
)
|
|
var i CreateKolUsageLogRow
|
|
err := row.Scan(
|
|
&i.ID,
|
|
&i.TenantID,
|
|
&i.GenerationTaskID,
|
|
&i.Status,
|
|
&i.CreatedAt,
|
|
)
|
|
return i, err
|
|
}
|
|
|
|
const getKolUsageByGenerationTask = `-- name: GetKolUsageByGenerationTask :one
|
|
SELECT id, tenant_id, package_id, prompt_id, status, created_at
|
|
FROM kol_usage_logs
|
|
WHERE generation_task_id = $1
|
|
AND tenant_id = $2::bigint
|
|
`
|
|
|
|
type GetKolUsageByGenerationTaskParams struct {
|
|
GenerationTaskID pgtype.Int8 `json:"generation_task_id"`
|
|
TenantID int64 `json:"tenant_id"`
|
|
}
|
|
|
|
type GetKolUsageByGenerationTaskRow struct {
|
|
ID int64 `json:"id"`
|
|
TenantID int64 `json:"tenant_id"`
|
|
PackageID int64 `json:"package_id"`
|
|
PromptID int64 `json:"prompt_id"`
|
|
Status string `json:"status"`
|
|
CreatedAt pgtype.Timestamptz `json:"created_at"`
|
|
}
|
|
|
|
func (q *Queries) GetKolUsageByGenerationTask(ctx context.Context, arg GetKolUsageByGenerationTaskParams) (GetKolUsageByGenerationTaskRow, error) {
|
|
row := q.db.QueryRow(ctx, getKolUsageByGenerationTask, arg.GenerationTaskID, arg.TenantID)
|
|
var i GetKolUsageByGenerationTaskRow
|
|
err := row.Scan(
|
|
&i.ID,
|
|
&i.TenantID,
|
|
&i.PackageID,
|
|
&i.PromptID,
|
|
&i.Status,
|
|
&i.CreatedAt,
|
|
)
|
|
return i, err
|
|
}
|
|
|
|
const kolDashboardOverview = `-- name: KolDashboardOverview :one
|
|
SELECT
|
|
(SELECT COUNT(DISTINCT s.tenant_id) FROM kol_subscriptions s
|
|
WHERE s.package_id = ANY($1::bigint[]) AND s.status='active'
|
|
AND (s.end_at IS NULL OR s.end_at > NOW())) AS total_subs,
|
|
(SELECT COUNT(*) FROM kol_subscriptions s
|
|
WHERE s.package_id = ANY($1::bigint[])
|
|
AND s.status='active'
|
|
AND s.created_at >= date_trunc('month', NOW())) AS month_subs,
|
|
(SELECT COUNT(*) FROM kol_usage_logs u
|
|
WHERE u.package_id = ANY($1::bigint[])) AS total_usage,
|
|
(SELECT COUNT(*) FROM kol_usage_logs u
|
|
WHERE u.package_id = ANY($1::bigint[])
|
|
AND u.created_at >= date_trunc('month', NOW())) AS month_usage,
|
|
(SELECT COUNT(*) FROM kol_usage_logs u
|
|
WHERE u.package_id = ANY($1::bigint[])
|
|
AND u.status='failed') AS failure_count
|
|
`
|
|
|
|
type KolDashboardOverviewRow struct {
|
|
TotalSubs int64 `json:"total_subs"`
|
|
MonthSubs int64 `json:"month_subs"`
|
|
TotalUsage int64 `json:"total_usage"`
|
|
MonthUsage int64 `json:"month_usage"`
|
|
FailureCount int64 `json:"failure_count"`
|
|
}
|
|
|
|
// Cross-tenant aggregation: KOL owns packages across subscriber tenants.
|
|
func (q *Queries) KolDashboardOverview(ctx context.Context, packageIds []int64) (KolDashboardOverviewRow, error) {
|
|
row := q.db.QueryRow(ctx, kolDashboardOverview, packageIds)
|
|
var i KolDashboardOverviewRow
|
|
err := row.Scan(
|
|
&i.TotalSubs,
|
|
&i.MonthSubs,
|
|
&i.TotalUsage,
|
|
&i.MonthUsage,
|
|
&i.FailureCount,
|
|
)
|
|
return i, err
|
|
}
|
|
|
|
const kolDashboardTrend = `-- name: KolDashboardTrend :many
|
|
WITH days AS (
|
|
SELECT generate_series(
|
|
date_trunc('day', NOW()) - ($1::int - 1) * INTERVAL '1 day',
|
|
date_trunc('day', NOW()),
|
|
INTERVAL '1 day'
|
|
)::date AS d
|
|
),
|
|
u AS (
|
|
SELECT date_trunc('day', created_at)::date AS d,
|
|
COUNT(*) FILTER (WHERE status = 'completed') AS usages
|
|
FROM kol_usage_logs
|
|
WHERE package_id = ANY($2::bigint[])
|
|
AND created_at >= NOW() - $1::int * INTERVAL '1 day'
|
|
GROUP BY 1
|
|
),
|
|
s AS (
|
|
SELECT date_trunc('day', created_at)::date AS d,
|
|
COUNT(*) AS subscriptions
|
|
FROM kol_subscriptions
|
|
WHERE package_id = ANY($2::bigint[])
|
|
AND status IN ('active', 'expired', 'revoked')
|
|
AND created_at >= NOW() - $1::int * INTERVAL '1 day'
|
|
GROUP BY 1
|
|
)
|
|
SELECT days.d,
|
|
COALESCE(u.usages, 0)::bigint AS usages,
|
|
COALESCE(s.subscriptions, 0)::bigint AS subscriptions
|
|
FROM days
|
|
LEFT JOIN u ON u.d = days.d
|
|
LEFT JOIN s ON s.d = days.d
|
|
ORDER BY days.d ASC
|
|
`
|
|
|
|
type KolDashboardTrendParams struct {
|
|
PeriodDays int32 `json:"period_days"`
|
|
PackageIds []int64 `json:"package_ids"`
|
|
}
|
|
|
|
type KolDashboardTrendRow struct {
|
|
D pgtype.Date `json:"d"`
|
|
Usages int64 `json:"usages"`
|
|
Subscriptions int64 `json:"subscriptions"`
|
|
}
|
|
|
|
// Returns a per-day series combining usage count and new subscription count for KOL dashboard.
|
|
func (q *Queries) KolDashboardTrend(ctx context.Context, arg KolDashboardTrendParams) ([]KolDashboardTrendRow, error) {
|
|
rows, err := q.db.Query(ctx, kolDashboardTrend, arg.PeriodDays, arg.PackageIds)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var items []KolDashboardTrendRow
|
|
for rows.Next() {
|
|
var i KolDashboardTrendRow
|
|
if err := rows.Scan(&i.D, &i.Usages, &i.Subscriptions); err != nil {
|
|
return nil, err
|
|
}
|
|
items = append(items, i)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
return items, nil
|
|
}
|
|
|
|
const kolUsageCountByPackage = `-- name: KolUsageCountByPackage :many
|
|
SELECT package_id, COUNT(*) AS total,
|
|
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
|
|
SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed
|
|
FROM kol_usage_logs
|
|
WHERE package_id = ANY($1::bigint[])
|
|
GROUP BY package_id
|
|
`
|
|
|
|
type KolUsageCountByPackageRow struct {
|
|
PackageID int64 `json:"package_id"`
|
|
Total int64 `json:"total"`
|
|
Completed int64 `json:"completed"`
|
|
Failed int64 `json:"failed"`
|
|
}
|
|
|
|
// Cross-tenant aggregation for KOL dashboard.
|
|
func (q *Queries) KolUsageCountByPackage(ctx context.Context, packageIds []int64) ([]KolUsageCountByPackageRow, error) {
|
|
rows, err := q.db.Query(ctx, kolUsageCountByPackage, packageIds)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var items []KolUsageCountByPackageRow
|
|
for rows.Next() {
|
|
var i KolUsageCountByPackageRow
|
|
if err := rows.Scan(
|
|
&i.PackageID,
|
|
&i.Total,
|
|
&i.Completed,
|
|
&i.Failed,
|
|
); err != nil {
|
|
return nil, err
|
|
}
|
|
items = append(items, i)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
return items, nil
|
|
}
|
|
|
|
const markKolUsageCompleted = `-- name: MarkKolUsageCompleted :exec
|
|
UPDATE kol_usage_logs
|
|
SET status = 'completed', article_id = $1,
|
|
finished_at = NOW()
|
|
WHERE id = $2
|
|
AND tenant_id = $3::bigint
|
|
`
|
|
|
|
type MarkKolUsageCompletedParams struct {
|
|
ArticleID pgtype.Int8 `json:"article_id"`
|
|
ID int64 `json:"id"`
|
|
TenantID int64 `json:"tenant_id"`
|
|
}
|
|
|
|
func (q *Queries) MarkKolUsageCompleted(ctx context.Context, arg MarkKolUsageCompletedParams) error {
|
|
_, err := q.db.Exec(ctx, markKolUsageCompleted, arg.ArticleID, arg.ID, arg.TenantID)
|
|
return err
|
|
}
|
|
|
|
const markKolUsageCompletedByTask = `-- name: MarkKolUsageCompletedByTask :exec
|
|
UPDATE kol_usage_logs
|
|
SET status = 'completed', article_id = $1, finished_at = NOW()
|
|
WHERE generation_task_id = $2
|
|
AND tenant_id = $3::bigint
|
|
`
|
|
|
|
type MarkKolUsageCompletedByTaskParams struct {
|
|
ArticleID pgtype.Int8 `json:"article_id"`
|
|
GenerationTaskID pgtype.Int8 `json:"generation_task_id"`
|
|
TenantID int64 `json:"tenant_id"`
|
|
}
|
|
|
|
func (q *Queries) MarkKolUsageCompletedByTask(ctx context.Context, arg MarkKolUsageCompletedByTaskParams) error {
|
|
_, err := q.db.Exec(ctx, markKolUsageCompletedByTask, arg.ArticleID, arg.GenerationTaskID, arg.TenantID)
|
|
return err
|
|
}
|
|
|
|
const markKolUsageFailed = `-- name: MarkKolUsageFailed :exec
|
|
UPDATE kol_usage_logs
|
|
SET status = 'failed', error_message = $1,
|
|
finished_at = NOW()
|
|
WHERE id = $2
|
|
AND tenant_id = $3::bigint
|
|
`
|
|
|
|
type MarkKolUsageFailedParams struct {
|
|
ErrorMessage pgtype.Text `json:"error_message"`
|
|
ID int64 `json:"id"`
|
|
TenantID int64 `json:"tenant_id"`
|
|
}
|
|
|
|
func (q *Queries) MarkKolUsageFailed(ctx context.Context, arg MarkKolUsageFailedParams) error {
|
|
_, err := q.db.Exec(ctx, markKolUsageFailed, arg.ErrorMessage, arg.ID, arg.TenantID)
|
|
return err
|
|
}
|
|
|
|
const markKolUsageFailedByTask = `-- name: MarkKolUsageFailedByTask :exec
|
|
UPDATE kol_usage_logs
|
|
SET status = 'failed', error_message = $1, finished_at = NOW()
|
|
WHERE generation_task_id = $2
|
|
AND tenant_id = $3::bigint
|
|
`
|
|
|
|
type MarkKolUsageFailedByTaskParams struct {
|
|
ErrorMessage pgtype.Text `json:"error_message"`
|
|
GenerationTaskID pgtype.Int8 `json:"generation_task_id"`
|
|
TenantID int64 `json:"tenant_id"`
|
|
}
|
|
|
|
func (q *Queries) MarkKolUsageFailedByTask(ctx context.Context, arg MarkKolUsageFailedByTaskParams) error {
|
|
_, err := q.db.Exec(ctx, markKolUsageFailedByTask, arg.ErrorMessage, arg.GenerationTaskID, arg.TenantID)
|
|
return err
|
|
}
|