Database Schema

Database

Complete schema for Tonal — a gated indie-artist incubator and data-analytics marketplace. All tables use UUID primary keys, timestamps, and proper foreign key relationships.

users

Subscriber accounts — authentication and subscription state

Field	Type	Constraints	Description
id	UUID	PK	Primary key
email	VARCHAR(255)	UNIQUE NOT NULL	Login email
display_name	VARCHAR(100)	NOT NULL	Public display name
auth_provider	ENUM	NOT NULL	oauth2 \| email
auth_provider_id	VARCHAR(255)		External auth ID
stripe_customer_id	VARCHAR(255)	UNIQUE	Stripe customer ref
subscription_status	ENUM	NOT NULL	active \| paused \| cancelled \| trial
subscription_started_at	TIMESTAMP		Subscription start
created_at	TIMESTAMP	NOT NULL	Account created
updated_at	TIMESTAMP	NOT NULL	Last modified

artists

Musician profiles — onboarded and verified creators

Field	Type	Constraints	Description
id	UUID	PK	Primary key
user_id	UUID	FK → users UNIQUE	Linked user account
artist_name	VARCHAR(200)	NOT NULL	Stage/band name
bio	TEXT		Artist biography
avatar_url	VARCHAR(500)		Profile image CDN URL
base44_identity_hash	CHAR(64)	UNIQUE NOT NULL	SHA-256 Base-44 identity
id_verified	BOOLEAN	DEFAULT false	Stripe Identity check
pro_org	ENUM		ASCAP \| BMI \| SESAC \| NONE
pro_member_id	VARCHAR(100)		PRO membership ID
pro_verified	BOOLEAN	DEFAULT false	PRO verification status
ipi_cae_number	VARCHAR(20)		IPI/CAE identifier
stripe_connect_id	VARCHAR(255)		Stripe Connect for payouts
onboarding_status	ENUM	NOT NULL	pending \| verified \| suspended
created_at	TIMESTAMP	NOT NULL	Account created

tracks

Audio tracks — the core catalog with AI detection status

Field	Type	Constraints	Description
id	UUID	PK	Primary key
artist_id	UUID	FK → artists NOT NULL	Owning artist
album_id	UUID	FK → albums	Parent album (nullable)
title	VARCHAR(300)	NOT NULL	Track title
isrc	CHAR(12)	UNIQUE	International Standard Recording Code
duration_ms	INTEGER	NOT NULL	Track duration in ms
genre	VARCHAR(50)		Primary genre tag
audio_fingerprint	CHAR(64)	UNIQUE NOT NULL	Chromaprint hash
base44_rights_hash	CHAR(64)	UNIQUE NOT NULL	Rights provenance hash
hls_manifest_url	VARCHAR(500)		CDN HLS playlist
ai_detection_status	ENUM	NOT NULL	passed \| flagged \| quarantined \| rejected
ai_confidence_score	DECIMAL(5,4)		0.0000–1.0000 human confidence
source_platform	ENUM		upload (manual onboarding only)
published	BOOLEAN	DEFAULT false	Publicly available
created_at	TIMESTAMP	NOT NULL	Upload timestamp

albums

Album groupings for track organization

Field	Type	Constraints	Description
id	UUID	PK	Primary key
artist_id	UUID	FK → artists NOT NULL	Owning artist
title	VARCHAR(300)	NOT NULL	Album title
cover_art_url	VARCHAR(500)		Cover art CDN URL
upc	CHAR(12)	UNIQUE	Universal Product Code
release_date	DATE		Official release date
track_count	INTEGER	DEFAULT 0	Number of tracks
created_at	TIMESTAMP	NOT NULL	Record created

listen_events

Validated stream events — feeds the payout engine

Field	Type	Constraints	Description
id	BIGINT	PK AUTO_INCREMENT	Event ID
user_id	UUID	FK → users NOT NULL	Listening subscriber
track_id	UUID	FK → tracks NOT NULL	Streamed track
artist_id	UUID	FK → artists NOT NULL	Track owner (denorm)
started_at	TIMESTAMP	NOT NULL	Playback started
duration_listened_ms	INTEGER	NOT NULL	Actual listen time
qualified	BOOLEAN	NOT NULL	≥30s and fraud-cleared
fraud_score	DECIMAL(3,2)		0.00–1.00 risk score
device_fingerprint	CHAR(64)		Browser/device hash
ip_hash	CHAR(64)		SHA-256 of IP (privacy)
country_code	CHAR(2)		ISO country code

payout_periods

Monthly payout calculation periods

Field	Type	Constraints	Description
id	UUID	PK	Period ID
period_start	DATE	NOT NULL	First day of month
period_end	DATE	NOT NULL	Last day of month
active_subscribers	INTEGER	NOT NULL	Count of paying subs
total_pool_usd	DECIMAL(12,2)	NOT NULL	80% of subscription revenue
total_qualified_listens	BIGINT	NOT NULL	Total valid listens
status	ENUM	NOT NULL	calculating \| finalized \| paid
finalized_at	TIMESTAMP		When calculations locked

artist_payouts

Per-artist payout records for each period

Field	Type	Constraints	Description
id	UUID	PK	Payout record ID
payout_period_id	UUID	FK → payout_periods NOT NULL	Billing period
artist_id	UUID	FK → artists NOT NULL	Receiving artist
qualified_listens	BIGINT	NOT NULL	Artist's valid listens
share_percentage	DECIMAL(10,8)	NOT NULL	% of total pool
payout_amount_usd	DECIMAL(10,2)	NOT NULL	Calculated payout
stripe_transfer_id	VARCHAR(255)		Stripe transfer ref
transfer_status	ENUM	NOT NULL	pending \| sent \| failed
smart_contract_tx	VARCHAR(66)		On-chain receipt hash

merch_products

Artist merchandise listings

Field	Type	Constraints	Description
id	UUID	PK	Product ID
artist_id	UUID	FK → artists NOT NULL	Selling artist
name	VARCHAR(200)	NOT NULL	Product name
description	TEXT		Product description
price_usd	DECIMAL(8,2)	NOT NULL	List price
category	ENUM	NOT NULL	vinyl \| apparel \| digital \| other
image_url	VARCHAR(500)		Product image
inventory_count	INTEGER	DEFAULT 0	Available stock
fulfillment_partner	ENUM		printful \| shipstation \| self
active	BOOLEAN	DEFAULT true	Listed for sale

merch_orders

Merchandise purchase orders

Field	Type	Constraints	Description
id	UUID	PK	Order ID
user_id	UUID	FK → users NOT NULL	Buyer
artist_id	UUID	FK → artists NOT NULL	Seller artist
total_usd	DECIMAL(8,2)	NOT NULL	Order total
platform_commission_usd	DECIMAL(8,2)	NOT NULL	15% commission
artist_revenue_usd	DECIMAL(8,2)	NOT NULL	85% to artist
stripe_payment_id	VARCHAR(255)		Stripe Payment Intent
fulfillment_status	ENUM	NOT NULL	pending \| shipped \| delivered \| returned
tracking_number	VARCHAR(100)		Shipping tracking
created_at	TIMESTAMP	NOT NULL	Order placed

provenance_log

Append-only Base-44 identity and rights chain ledger

Field	Type	Constraints	Description
id	BIGINT	PK AUTO_INCREMENT	Log entry ID
entity_type	ENUM	NOT NULL	artist \| track \| rights_transfer
entity_id	UUID	NOT NULL	Referenced entity
action	ENUM	NOT NULL	created \| verified \| transferred \| revoked
base44_hash	CHAR(64)	NOT NULL	Base-44 encoded hash
prev_hash	CHAR(64)		Previous entry hash (chain)
metadata_json	JSONB		Additional context
ipfs_cid	VARCHAR(100)		IPFS content ID backup
created_at	TIMESTAMP	NOT NULL IMMUTABLE	Append timestamp

fraud_events

Detected fraudulent or suspicious activity

Field	Type	Constraints	Description
id	BIGINT	PK AUTO_INCREMENT	Event ID
user_id	UUID	FK → users	Suspicious user
event_type	ENUM	NOT NULL	bot_detected \| velocity_spike \| pattern_anomaly \| device_farm
severity	ENUM	NOT NULL	low \| medium \| high \| critical
details_json	JSONB		Detection details
action_taken	ENUM	NOT NULL	flagged \| blocked \| suspended \| dismissed
reviewed_by	UUID		Admin reviewer
created_at	TIMESTAMP	NOT NULL	Detection time

Key Indexes

• listen_events(user_id, started_at)

• listen_events(artist_id, qualified, started_at)

• tracks(audio_fingerprint)

• tracks(ai_detection_status, published)

• artist_payouts(payout_period_id, artist_id)

• provenance_log(entity_type, entity_id)

• fraud_events(user_id, created_at)

• merch_orders(user_id, created_at)