Payment Database¶

Payment tables, statuses, and support queries.

Database: vpn (MySQL 8.0.34 on 10.99.87.62, container: vpn-db)

Access¶

./scripts/ssh-internal.sh 10.99.87.62 \
  "docker exec vpn-db mysql -uvpn -p'$(см. Vaultwarden → Databases)' vpn -e 'QUERY'"

Password in [Vaultwarden](https://vault.shivavpn.io).

Key Statistics (as of Feb 2026)¶

Metric	Value
Total payments	53,802
Total subscriptions	18,294
Active accounts	143,798
Accounts with payments	10,988 (7.6%)
Accounts with subscriptions	16,235 (11.3%)

Tables Overview¶

payment¶

All payment transactions across providers.

Column	Type	Notes
id	binary(16)	PK, UUID
created_at	datetime(6)	Indexed
payment_id	varchar(255)	Unique with payment_type
payment_type	varchar(20)	Provider identifier
payment_status	varchar(20)	SUCCESSFUL, PENDING, NULL
account_id	binary(16)	FK to account
subscription_id	binary(16)	FK to subscription (nullable)
rate_id	binary(16)	FK to rate
promo_code_id	binary(16)	FK to promo_code (nullable)
total_amount_usd	decimal(38,2)
total_amount_rub	decimal(38,2)
affiliate_type	enum	indoleads, alanbase, PRMonline

Payment types distribution:

Type	Count	Share
SBP	31,232	58%
CYPIX	9,068	17%
APPLE	6,337	12%
STRIPE	3,210	6%
NOWPAYMENTS	2,557	5%
GOOGLE	450	1%
YOOKASSA	408	1%
Others	~500	<1%

Payment status distribution:

Status	Count	Notes
NULL	41,488 (77%)	Legacy data, no explicit status
PENDING	8,761 (16%)	Awaiting confirmation
SUCCESSFUL	3,526 (7%)	Confirmed

subscription¶

Recurring subscriptions (Apple, Google, Stripe).

Column	Type	Notes
id	binary(16)	PK
subscription_id	varchar(255)	Unique with subscription_type
apple_original_transaction_id	varchar(255)	For Apple renewals
account_id	binary(16)	FK to account
rate_id	binary(16)	FK to rate
subscription_type	enum	GOOGLE, APPLE, STRIPE
subscription_status	enum	PENDING, ACTIVE, CANCELED
is_with_trial	tinyint(1)

Distribution:

Type + Status	Count	Notes
APPLE ACTIVE	12,132 (66%)
STRIPE PENDING	2,457 (13%)	High pending = webhook issue
APPLE CANCELED	1,872 (10%)
GOOGLE ACTIVE	1,770 (10%)
Others	~60

Stripe pending anomaly

2,457 STRIPE PENDING vs 3 STRIPE ACTIVE — this is abnormal. Likely webhook processing issues or abandoned subscriptions. Investigate before Stripe-related support cases.

rate¶

Pricing plans.

Column	Notes
payment_period	TRIAL, THREE_DAY, TWO_WEEK, MONTH, SIX_MONTH, YEAR, TWO_YEAR, LIFETIME, and *_BOT variants
cost_per_month_usd / rub	Display price per month
total_amount_usd / rub	Actual charge amount
days	Subscription duration
limit_ip	Concurrent connections allowed
google_product_id / apple_product_id	Store SKUs
stripe_subscription_price_lookup_key	Stripe price ID
is_active	Whether shown in app

Active rates:

Period	USD	RUB	Discount	Days
TRIAL	—	—	—	3
MONTH	$10	990₽	0%	30
SIX_MONTH	$25	2500₽	54%	183
YEAR	$32	3200₽	58%	365

account (payment-relevant fields)¶

Column	Notes
pay_status	ADMIN, TRIAL, PAID_WAS_TRIAL, PAID, CREATED_BY_ADMIN, POOL
valid_to	Subscription expiry date
stripe_customer_id	Unique Stripe customer

Pay status distribution (active accounts):

Status	Count	Share
TRIAL	116,819	81%
PAID	25,618	18%
CREATED_BY_ADMIN	1,198	1%
PAID_WAS_TRIAL	163	<1%

promo_code¶

Column	Notes
code	Unique promo string
discount_percent
promo_code_status	ACTIVE, INACTIVE

subscription_notification¶

Webhook payloads from payment providers. No primary key — can cause replication issues.

Payment Flows¶

One-time (SBP, Crypto)¶

User selects rate → payment record created (NULL/PENDING)
  → User pays with provider
  → Webhook: payment_status = SUCCESSFUL
  → account.pay_status = PAID, account.valid_to updated

Recurring (Apple, Google, Stripe)¶

User subscribes → subscription record (PENDING or ACTIVE)
  → First payment: payment record linked to subscription_id
  → Renewals: new payment records with same subscription_id
  → Cancellation: subscription_status = CANCELED
  → Webhooks logged to subscription_notification

Support Queries¶

User's payment history¶

SELECT
    p.created_at,
    p.payment_type,
    p.payment_status,
    p.total_amount_usd,
    r.payment_period,
    a.account
FROM payment p
JOIN account a ON p.account_id = a.id
JOIN rate r ON p.rate_id = r.id
WHERE a.account = '1234567890123456'
ORDER BY p.created_at DESC;

Active subscriptions¶

SELECT
    a.account,
    s.subscription_type,
    s.subscription_status,
    s.created_at,
    r.payment_period,
    a.valid_to
FROM subscription s
JOIN account a ON s.account_id = a.id
JOIN rate r ON s.rate_id = r.id
WHERE s.subscription_status = 'ACTIVE'
ORDER BY s.created_at DESC;

Monthly revenue by payment type¶

SELECT
    DATE_FORMAT(created_at, '%Y-%m') as month,
    payment_type,
    COUNT(*) as payment_count,
    SUM(total_amount_usd) as total_usd,
    SUM(total_amount_rub) as total_rub
FROM payment
WHERE payment_status = 'SUCCESSFUL'
GROUP BY month, payment_type
ORDER BY month DESC, total_usd DESC;

Conversion rate¶

SELECT
    COUNT(DISTINCT a.id) as total_accounts,
    COUNT(DISTINCT CASE WHEN a.pay_status IN ('PAID', 'PAID_WAS_TRIAL') THEN a.id END) as paid_accounts,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.pay_status IN ('PAID', 'PAID_WAS_TRIAL') THEN a.id END)
        / COUNT(DISTINCT a.id), 2) as conversion_rate
FROM account a
WHERE a.deleted_at IS NULL;

Check if user has active Apple subscription¶

SELECT s.subscription_status, s.created_at, r.payment_period, a.valid_to
FROM subscription s
JOIN account a ON s.account_id = a.id
JOIN rate r ON s.rate_id = r.id
WHERE a.account = '1234567890123456'
  AND s.subscription_type = 'APPLE'
ORDER BY s.created_at DESC
LIMIT 5;

Known Data Issues¶

subscription_notification has no primary key — replication and indexing risk
77% of payments have NULL payment_status — legacy data from before status tracking
Stripe: 99% PENDING subscriptions — webhook processing issue, not a normal state
Only 7% of payments linked to subscriptions — renewal records may not always link back

См. также: Аналитические SQL-запросы · Подключение к production БД