🗄️ DATABASE INTEGRATION ANALYSIS - Wallets, Users, Accounting
Date: October 6, 2025
Purpose: Understand database connections for production wallet-user-accounting flow
📊 DATABASE SCHEMA OVERVIEW
Core Tables (Wallet Service):
┌──────────────────────────────────────────────────────────────────┐
│ users │
├──────────────────────────────────────────────────────────────────┤
│ id (UUID, PK) │
│ telegram_id (BIGINT, UNIQUE) ◄─────┐ │
│ username, first_name, last_name │ │
│ language_code, default_network │ │
│ is_active, is_verified │ │
└────────────────────────────────────┼──────────────────────────────┘
│
│ Foreign Key Relationship
│
┌────────────────────────────────────┼──────────────────────────────┐
│ wallets │ │
├────────────────────────────────────┼──────────────────────────────┤
│ id (UUID, PK) │ │
│ user_id (UUID, FK) ────────────────┘ (Links to users.id) │
│ telegram_id (BIGINT) ────────────────────┐ │
│ wallet_type (hd|connected|hot) │ Dual linkage: │
│ status (active|inactive|pending) │ - UserID for service │
│ address (TON/TRON/ETH address) │ - TelegramId for bot │
│ public_key, network │ │
│ derivation_path, xpub, encrypted_xprv │ │
│ is_default, source (internal|walletconnect) │
│ label, description │
│ created_at, updated_at, deleted_at │
└──────────────────────────────────────────────────────────────────┘
│
│ One-to-Many
▼
┌──────────────────────────────────────────────────────────────────┐
│ wallet_roots (HD Wallet Master Keys) │
├──────────────────────────────────────────────────────────────────┤
│ id (UUID, PK) │
│ telegram_id (BIGINT, UNIQUE per network) │
│ network (TON|TRON|ETH|BTC) │
│ encrypted_mnemonic (TEXT) ◄─── AES-256-GCM encrypted │
│ passphrase (optional) │
│ xpub (extended public key) │
│ encrypted_xprv (encrypted private key) │
│ last_backup_at │
│ UNIQUE(telegram_id, network) ◄─── One root per user per chain │
└──────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────────┐
│ wallet_connections (WalletConnect Sessions) │
├──────────────────────────────────────────────────────────────────┤
│ id (UUID, PK) │
│ user_id (UUID, FK) │
│ telegram_id (BIGINT) │
│ session_id, relay_url, topic │
│ network, address │
│ is_default, status (pending|approved|rejected|expired) │
└──────────────────────────────────────────────────────────────────┘
🔗 DATABASE RELATIONSHIPS
User ↔ Wallet Linkage:
// Wallet Entity (wallet.entity.go)
type Wallet struct {
UserID uuid.UUID // ← Links to users table (account service)
TelegramId int64 // ← Links to Telegram bot
// Both fields required for dual-service architecture:
// 1. UserID: For account service integration (accounting, KYC, etc.)
// 2. TelegramId: For bot operations (quick lookups)
}
// User Entity (user.entity.go)
type User struct {
Id uuid.UUID // ← Referenced by wallets.user_id
TelegramID int64 // ← UNIQUE, referenced by wallets.telegram_id
// Wallet-related fields:
DefaultNetwork string // ← User's preferred network (TON/TRON/ETH)
}
Data Flow:
Telegram Bot Request (telegram_id: 123456789)
↓
1. Find/Create User in users table
- INSERT INTO users (telegram_id, username, ...)
- Returns: user_id (UUID)
↓
2. Create/Find Wallet for User
- INSERT INTO wallets (user_id, telegram_id, network, address, ...)
- Links to both user_id AND telegram_id
↓
3. Store Wallet Root (if HD wallet)
- INSERT INTO wallet_roots (telegram_id, network, encrypted_mnemonic, ...)
- One root per telegram_id per network
↓
4. Return Deposit Address
- SELECT address FROM wallets WHERE user_id = ? AND network = ? AND is_default = true
🏦 ACCOUNTING SERVICE INTEGRATION
How Wallets Connect to Accounting:
┌────────────────────────────────────────────────────────────────┐
│ Accounting Service (Future - Phase 2+) │
├────────────────────────────────────────────────────────────────┤
│ │
│ ledger_entries table: │
│ ├─ id, user_id (UUID) ◄──────────────────────┐ │
│ ├─ wallet_id (UUID) ◄───────────┐ │ │
│ ├─ transaction_type │ │ │
│ ├─ amount, currency │ │ │
│ ├─ debit, credit │ │ │
│ └─ created_at │ │ │
│ │ │ │
│ user_balances table (aggregated):│ │ │
│ ├─ user_id (UUID, FK) ───────────┼────────────┘ │
│ ├─ currency │ │
│ ├─ available_balance │ │
│ ├─ locked_balance │ │
│ └─ total_balance │ │
│ │ │
└───────────────────────────────────┼─────────────────────────────┘
│
│ References
│
┌───────────────────────────────────┼─────────────────────────────┐
│ Wallet Service │ │
├───────────────────────────────────┼─────────────────────────────┤
│ wallets table: │ │
│ ├─ id (UUID, PK) ─────────────────┘ │
│ ├─ user_id (UUID, FK to users) │
│ ├─ telegram_id │
│ └─ address, network, ... │
└─────────────────────────────────────────────────────────────────┘
Accounting Flow:
Deposit Detected (Watcher Service):
INSERT INTO deposits (wallet_id, tx_hash, amount, network, status) VALUES ('wallet-uuid', '0xabc...', 100.5, 'TON', 'confirmed');Credit User Balance (Accounting Service): “`sql – Get wallet owner SELECT user_id FROM wallets WHERE id = ‘wallet-uuid’;
– Create ledger entry (double-entry bookkeeping) INSERT INTO ledger_entries (user_id, wallet_id, type, amount, currency, debit, credit) VALUES (‘user-uuid’, ‘wallet-uuid’, ‘deposit’, 100.5, ‘TON’, 0, 100.5);
– Update balance (atomically) UPDATE user_balances SET available_balance = available_balance + 100.5, total_balance = total_balance + 100.5 WHERE user_id = ‘user-uuid’ AND currency = ‘TON’;
3. **Withdrawal Request** (Wallet Service):
```sql
-- Lock balance (Accounting Service)
UPDATE user_balances
SET available_balance = available_balance - 50.0,
locked_balance = locked_balance + 50.0
WHERE user_id = 'user-uuid' AND currency = 'TON';
-- Create withdrawal record
INSERT INTO withdrawals (user_id, wallet_id, amount, address, status)
VALUES ('user-uuid', 'wallet-uuid', 50.0, 'EQ...', 'pending');
- Withdrawal Processed (Wallet Service): “`sql – Sign transaction (using Signer interface - Task 0.1!) – Broadcast to blockchain – Update withdrawal UPDATE withdrawals SET status = ‘completed’, tx_hash = ‘0xdef…’ WHERE id = ‘withdrawal-uuid’;
– Unlock and deduct balance (Accounting Service) UPDATE user_balances SET locked_balance = locked_balance - 50.0 WHERE user_id = ‘user-uuid’ AND currency = ‘TON’;
– Create ledger entry INSERT INTO ledger_entries (user_id, wallet_id, type, amount, debit, credit) VALUES (‘user-uuid’, ‘wallet-uuid’, ‘withdrawal’, 50.0, 50.0, 0);
---
## 🔌 PRODUCTION DATABASE CONNECTION
### **Database Configuration:**
**File:** `services/wallet/pkg/infrastructure/config/config.go`
```go
Database: Database{
Postgres: database.PgConfig{
Host: getEnvOrDefault("PG_HOST", "postgres"),
Port: getEnvIntOrDefault("PG_PORT", 5432),
Username: getEnvOrDefault("PG_USER", "postgres"),
Password: getEnvOrDefault("PG_PASS", "postgres"),
Name: getEnvOrDefault("PG_DB", "wallet_core"),
Ssl: "disable",
},
Redis: Redis{
Address: getEnvOrDefault("REDIS_ADDR", "redis:6379"),
Password: getEnvOrDefault("REDIS_PASSWORD", ""),
Database: 0,
},
}
Production Environment Variables:
# .env file (production)
PG_HOST=postgres # Or production DB host
PG_PORT=5432
PG_USER=postgres
PG_PASS=secure_password # Change in production!
PG_DB=wallet_core
REDIS_ADDR=redis:6379
REDIS_PASSWORD=secure_redis # Change in production!
WALLET_ENC_KEY=... # 32-byte encryption key for mnemonics
Docker Compose (Production):
services:
postgres:
image: postgres:15
environment:
POSTGRES_DB: wallet_core
POSTGRES_USER: postgres
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
volumes:
- postgres_data:/var/lib/postgresql/data
networks:
- net
wallet:
image: registry/wallet:latest
env_file: .env
depends_on:
- postgres
- redis
ports:
- "9092:9092" # gRPC
networks:
- net
✅ DATABASE INTEGRATION CHECKLIST
For Task 0.1 (TON Signer) Implementation:
[x] Database schema supports wallet generation
- ✅
walletstable has all needed fields - ✅
wallet_rootstable stores encrypted mnemonics - ✅
userstable links wallets to accounts
- ✅
[x] Dual indexing (UserID + TelegramId)
- ✅ Supports account service integration (UserID)
- ✅ Supports bot quick lookups (TelegramId)
- ✅ Both fields indexed for performance
[x] Security in place
- ✅ Mnemonic encryption (AES-256-GCM + PBKDF2)
- ✅ Encrypted storage in
encrypted_mnemonicfield - ✅ EncryptionKey from environment variable
[x] Ready for accounting integration
- ✅ Wallet has
user_idfield for ledger entries - ✅ Wallet has
addressfield for transaction tracking - ✅ Wallet has
networkfield for multi-chain support
- ✅ Wallet has
[ ] Production database connection (Need to verify)
- ⚠️ Docker services not currently running
- ✅ Configuration in place (config.go)
- ✅ Connection pooling via GORM
- 🔧 Need to test connection before deployment
🔄 COMPLETE FLOW (Deposit to Withdrawal)
1. User Registration:
Telegram User (telegram_id: 123456789)
↓
Bot calls: account.CreateUser(telegram_id, username, ...)
↓
INSERT INTO users (telegram_id, username, first_name, ...)
RETURNING id; -- Returns UUID
↓
User Created: {id: "550e8400-...", telegram_id: 123456789}
2. Wallet Creation (Deposit Address):
User requests TON wallet
↓
wallet.EnsureDefaultWallet(user_id, telegram_id, "TON")
↓
Check if exists:
SELECT * FROM wallets
WHERE user_id = '550e8400-...' AND network = 'TON' AND is_default = true;
↓
If not exists, create:
1. TONWalletGeneratorV4R2.GenerateWallet()
- Generate 24-word mnemonic (tonutils-go)
- Derive ed25519 keypair
- Create v4R2 address (EQ...)
- Encrypt mnemonic
↓
2. INSERT INTO wallet_roots (telegram_id, network, encrypted_mnemonic, xpub, ...)
VALUES (123456789, 'TON', 'encrypted...', 'v4r2:0', ...);
↓
3. INSERT INTO wallets (id, user_id, telegram_id, address, network, ...)
VALUES (gen_random_uuid(), '550e8400-...', 123456789, 'EQ...', 'TON', ...);
↓
Return deposit address: "EQCa1b2c3d4e5f6g7h8i9j0k..."
3. Deposit Detection (Watcher Service):
TON Watcher polls blockchain
↓
Detects transaction to monitored address: "EQCa1b2c..."
↓
Find wallet:
SELECT id, user_id, telegram_id FROM wallets WHERE address = 'EQCa1b2c...';
↓
Create deposit record:
INSERT INTO deposits (wallet_id, user_id, tx_hash, amount, confirmations, status)
VALUES ('wallet-uuid', 'user-uuid', '0xabc...', 100.5, 0, 'pending');
↓
Emit event to outbox:
INSERT INTO outbox (aggregate_id, event_type, payload, status)
VALUES ('deposit-uuid', 'deposit.detected', '{"amount":100.5,"network":"TON"}', 'pending');
↓
After 20 confirmations:
UPDATE deposits SET status = 'confirmed', confirmations = 20 WHERE id = 'deposit-uuid';
↓
Event consumed by Accounting Service:
- Credits user balance
- Creates ledger entry
- Sends Telegram notification
4. Withdrawal Request (Needs Task 0.1!):
User requests withdrawal: 50 TON to external address
↓
Accounting Service checks balance:
SELECT available_balance FROM user_balances
WHERE user_id = '550e8400-...' AND currency = 'TON';
↓
Lock balance:
UPDATE user_balances
SET available_balance = available_balance - 50,
locked_balance = locked_balance + 50
WHERE user_id = '550e8400-...' AND currency = 'TON';
↓
Create withdrawal:
INSERT INTO withdrawals (user_id, wallet_id, amount, to_address, network, status)
VALUES ('user-uuid', 'wallet-uuid', 50, 'EQ...external...', 'TON', 'pending');
↓
Admin approves (or auto-approve based on limits)
↓
Wallet Service processes withdrawal:
1. Get wallet root:
SELECT encrypted_mnemonic FROM wallet_roots
WHERE telegram_id = 123456789 AND network = 'TON';
2. Decrypt mnemonic (AES-256-GCM)
3. Derive private key (tonutils-go)
4. Sign transaction (Task 0.1 - NEEDED HERE!) ◄────── ❌ CURRENTLY BROKEN
signer := NewSigner("TON")
signature, err := signer.SignTx("TON", privateKey, txPayload)
❌ ERROR: "ton signer not implemented"
5. Broadcast to TON blockchain
6. Update withdrawal:
UPDATE withdrawals SET status = 'completed', tx_hash = '...'
WHERE id = 'withdrawal-uuid';
7. Unlock and deduct balance (Accounting Service):
UPDATE user_balances SET locked_balance = locked_balance - 50;
This is why Task 0.1 is CRITICAL! Withdrawals are completely blocked without it.
🎯 WHAT TASK 0.1 ENABLES
Before Task 0.1:
✅ Can create deposit addresses (TON Wallet Gen v4R2 works)
✅ Can store wallets in database
✅ Can link wallets to users
❌ CANNOT sign withdrawal transactions
❌ CANNOT process withdrawals
❌ Users' funds are stuck!
After Task 0.1:
✅ Can create deposit addresses
✅ Can store wallets in database
✅ Can link wallets to users
✅ CAN sign withdrawal transactions ◄── NEW!
✅ CAN process withdrawals ◄── NEW!
✅ Complete deposit → withdrawal flow ◄── NEW!
🔐 SECURITY CONSIDERATIONS
Key Storage:
1. Mnemonic Generation (TON Wallet Gen v4R2)
↓
2. Encryption (AES-256-GCM + PBKDF2)
- Key: WALLET_ENC_KEY environment variable
- Salt: Random 16 bytes per encryption
- Nonce: Random per encryption
- Result: Secure ciphertext
↓
3. Storage in Database
- Field: wallet_roots.encrypted_mnemonic
- Never logs mnemonics
- Never transmits unencrypted
↓
4. Decryption (Only for signing)
- Load encrypted_mnemonic from DB
- Decrypt using WALLET_ENC_KEY
- Derive private key
- Sign transaction
- Zero memory after use ◄── IMPORTANT!
↓
5. Private key NEVER stored
- Only mnemonic is stored (encrypted)
- Private key derived on-demand
- Zeroed after each use
Access Control:
// Only specific operations can decrypt mnemonics:
1. Withdrawal signing (Task 0.1)
2. Wallet recovery (admin operation)
3. Key rotation (future)
// All other operations use public data only:
- Deposit address: wallet.address (plaintext, public)
- Balance queries: Uses address only
- Transaction history: Uses address/user_id
📊 PRODUCTION READINESS CHECKLIST
Database:
- [x] Schema defined and migrated
- [x] Indexes created for performance
- [x] Constraints in place (unique telegram_id, default wallet per network)
- [ ] Production database connection tested ◄── NEED TO VERIFY
- [ ] Connection pool configured
- [ ] Backup strategy defined
Service Integration:
- [x] Users table links to Account service
- [x] Wallets table links to Users (dual: user_id + telegram_id)
- [ ] Accounting service integration (Phase 2)
- [ ] Transaction service integration (Phase 1)
Security:
- [x] Mnemonic encryption implemented
- [x] Encryption key from environment
- [ ] Key rotation plan (future)
- [ ] HSM/KMS integration (future)
- [ ] Memory zeroing after key use (implement in Task 0.1)
🚀 READY FOR TASK 0.1
What We Have:
✅ Database schema with proper relationships ✅ User ↔ Wallet linkage via user_id + telegram_id ✅ Encrypted mnemonic storage ✅ TON address generation (v4R2) working ✅ Repository pattern for database access
What We Need:
❌ Signer implementation (Task 0.1) ❌ Transaction signing for withdrawals ❌ Integration with TON Wallet Gen v4R2
What Task 0.1 Will Do:
- Replace placeholder tonSigner
- Implement Address() - reuse TON Wallet Gen v4R2 logic
- Implement SignTx() - use tonutils-go or ed25519
- Enable end-to-end withdrawal flow
- Link to accounting service via user_id
📋 DATABASE VERIFICATION STEPS
Before starting implementation, let’s verify production database:
# 1. Check if database is accessible
docker-compose up -d postgres redis
# 2. Run migrations
cd services/wallet && go run cmd/migrator/main.go
# 3. Verify tables exist
psql -h localhost -U postgres -d wallet_core -c "\dt"
# Expected tables:
# - users
# - wallets
# - wallet_roots
# - wallet_connections
# - deposits (from watcher migrations)
# - outbox (from watcher migrations)
# 4. Verify indexes
psql -h localhost -U postgres -d wallet_core -c "\di"
Next Step: Implement Task 0.1 with full database integration awareness.