💾 Database Schema

SourceNet uses PostgreSQL for data persistence, storing user information, DataPod metadata, purchases, reviews, and more.

Database Overview

The database consists of the following main tables:

• users: User accounts and authentication data
• datapods: Dataset metadata and Walrus references
• purchases: Purchase records and transaction tracking
• reviews: Ratings and comments from buyers
• escrow_transactions: Escrow state tracking
• ai_conversations: AI chat history
• download_logs: Download tracking for analytics

Entity Relationship Diagram

┌─────────────┐       ┌─────────────┐
│    users    │───┐   │  datapods   │
└─────────────┘   │   └─────────────┘
       │          │          │
       │          └──────────┤
       │                     │
       │          ┌──────────┴────────┐
       │          │                   │
       ▼          ▼                   ▼
┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│  purchases  │  │   reviews    │  │   escrow    │
└─────────────┘  └─────────────┘  └─────────────┘
       │
       ▼
┌─────────────┐
│download_logs│
└─────────────┘

Core Tables

users

Stores user account information, SUI addresses, and zkLogin salts.

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    google_id VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    profile_image VARCHAR(500),
    sui_address VARCHAR(66) UNIQUE NOT NULL,
    salt VARCHAR(64) NOT NULL,
    average_rating DECIMAL(3, 2) DEFAULT 0,
    total_sales INTEGER DEFAULT 0,
    total_purchases INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_users_google_id ON users(google_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_sui_address ON users(sui_address);

datapods

Stores DataPod metadata, Walrus blob references, and encryption information.

CREATE TABLE datapods (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    seller_id UUID REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(50),
    price_sui DECIMAL(20, 9) NOT NULL,
    walrus_blob_id VARCHAR(255) UNIQUE NOT NULL,
    walrus_storage_size BIGINT,
    encryption_key_hash VARCHAR(64) NOT NULL,
    encryption_iv VARCHAR(32) NOT NULL,
    file_type VARCHAR(100),
    file_size BIGINT,
    data_hash VARCHAR(64),
    schema JSONB,
    sample_data JSONB,
    published BOOLEAN DEFAULT false,
    purchase_count INTEGER DEFAULT 0,
    average_rating DECIMAL(3, 2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_datapods_seller ON datapods(seller_id);
CREATE INDEX idx_datapods_category ON datapods(category);
CREATE INDEX idx_datapods_published ON datapods(published);
CREATE INDEX idx_datapods_price ON datapods(price_sui);
CREATE INDEX idx_datapods_rating ON datapods(average_rating);

-- Full-text search index
CREATE INDEX idx_datapods_search ON datapods 
    USING gin(to_tsvector('english', title || ' ' || description));

purchases

Records all purchases with blockchain transaction references.

CREATE TABLE purchases (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    buyer_id UUID REFERENCES users(id) ON DELETE CASCADE,
    datapod_id UUID REFERENCES datapods(id) ON DELETE CASCADE,
    payment_tx_digest VARCHAR(100) UNIQUE NOT NULL,
    amount_sui DECIMAL(20, 9) NOT NULL,
    encrypted_blob_id VARCHAR(255),
    decryption_key TEXT,
    decryption_iv VARCHAR(32),
    status VARCHAR(20) DEFAULT 'pending_payment',
    created_at TIMESTAMP DEFAULT NOW(),
    completed_at TIMESTAMP,
    updated_at TIMESTAMP DEFAULT NOW(),
    
    CONSTRAINT unique_buyer_datapod UNIQUE(buyer_id, datapod_id)
);

-- Indexes
CREATE INDEX idx_purchases_buyer ON purchases(buyer_id);
CREATE INDEX idx_purchases_datapod ON purchases(datapod_id);
CREATE INDEX idx_purchases_status ON purchases(status);
CREATE INDEX idx_purchases_tx_digest ON purchases(payment_tx_digest);

-- Enum-like constraint
ALTER TABLE purchases ADD CONSTRAINT check_status 
    CHECK (status IN ('pending_payment', 'processing', 'completed', 'refunded'));

reviews

Buyer ratings and comments for purchased DataPods.

CREATE TABLE reviews (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchase_id UUID REFERENCES purchases(id) UNIQUE ON DELETE CASCADE,
    buyer_id UUID REFERENCES users(id) ON DELETE CASCADE,
    datapod_id UUID REFERENCES datapods(id) ON DELETE CASCADE,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_reviews_datapod ON reviews(datapod_id);
CREATE INDEX idx_reviews_buyer ON reviews(buyer_id);
CREATE INDEX idx_reviews_rating ON reviews(rating);

escrow_transactions

Tracks escrow state for each purchase.

CREATE TABLE escrow_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchase_id UUID REFERENCES purchases(id) ON DELETE CASCADE,
    seller_id UUID REFERENCES users(id),
    amount_sui DECIMAL(20, 9) NOT NULL,
    escrow_object_id VARCHAR(66),
    status VARCHAR(20) DEFAULT 'holding',
    created_at TIMESTAMP DEFAULT NOW(),
    released_at TIMESTAMP,
    
    CONSTRAINT check_escrow_status 
        CHECK (status IN ('holding', 'released', 'refunded'))
);

-- Indexes
CREATE INDEX idx_escrow_purchase ON escrow_transactions(purchase_id);
CREATE INDEX idx_escrow_status ON escrow_transactions(status);

AI & Analytics Tables

ai_conversations

CREATE TABLE ai_conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255),
    total_tokens INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE ai_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID REFERENCES ai_conversations(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL, -- 'user' or 'assistant'
    content TEXT NOT NULL,
    tokens INTEGER,
    context JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_conversations_user ON ai_conversations(user_id);
CREATE INDEX idx_messages_conversation ON ai_messages(conversation_id);

download_logs

CREATE TABLE download_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchase_id UUID REFERENCES purchases(id) ON DELETE CASCADE,
    buyer_id UUID REFERENCES users(id),
    datapod_id UUID REFERENCES datapods(id),
    downloaded_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_downloads_purchase ON download_logs(purchase_id);
CREATE INDEX idx_downloads_datapod ON download_logs(datapod_id);
CREATE INDEX idx_downloads_date ON download_logs(downloaded_at);

Database Functions & Triggers

Update Average Rating

-- Trigger to update DataPod average rating after review
CREATE OR REPLACE FUNCTION update_datapod_rating()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE datapods
    SET average_rating = (
        SELECT AVG(rating)
        FROM reviews
        WHERE datapod_id = NEW.datapod_id
    )
    WHERE id = NEW.datapod_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_datapod_rating
    AFTER INSERT OR UPDATE ON reviews
    FOR EACH ROW
    EXECUTE FUNCTION update_datapod_rating();

Update Seller Rating

-- Trigger to update seller average rating
CREATE OR REPLACE FUNCTION update_seller_rating()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE users
    SET average_rating = (
        SELECT AVG(d.average_rating)
        FROM datapods d
        WHERE d.seller_id = (
            SELECT seller_id FROM datapods WHERE id = NEW.datapod_id
        )
        AND d.average_rating > 0
    )
    WHERE id = (SELECT seller_id FROM datapods WHERE id = NEW.datapod_id);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_seller_rating
    AFTER INSERT OR UPDATE ON reviews
    FOR EACH ROW
    EXECUTE FUNCTION update_seller_rating();

Update Purchase Counts

-- Trigger to increment purchase counts
CREATE OR REPLACE FUNCTION increment_purchase_counts()
RETURNS TRIGGER AS $$
BEGIN
    -- Update DataPod purchase count
    UPDATE datapods
    SET purchase_count = purchase_count + 1
    WHERE id = NEW.datapod_id;
    
    -- Update buyer total purchases
    UPDATE users
    SET total_purchases = total_purchases + 1
    WHERE id = NEW.buyer_id;
    
    -- Update seller total sales
    UPDATE users
    SET total_sales = total_sales + 1
    WHERE id = (SELECT seller_id FROM datapods WHERE id = NEW.datapod_id);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_increment_purchase_counts
    AFTER INSERT ON purchases
    FOR EACH ROW
    WHEN (NEW.status = 'completed')
    EXECUTE FUNCTION increment_purchase_counts();

Database Queries

Search DataPods

SELECT 
    d.*,
    u.name as seller_name,
    u.average_rating as seller_rating,
    COUNT(p.id) as total_purchases,
    AVG(r.rating) as avg_rating
FROM datapods d
JOIN users u ON d.seller_id = u.id
LEFT JOIN purchases p ON d.id = p.datapod_id
LEFT JOIN reviews r ON d.id = r.datapod_id
WHERE 
    d.published = true
    AND d.category = $1
    AND d.price_sui BETWEEN $2 AND $3
    AND to_tsvector('english', d.title || ' ' || d.description) 
        @@ plainto_tsquery('english', $4)
GROUP BY d.id, u.name, u.average_rating
ORDER BY d.created_at DESC
LIMIT $5 OFFSET $6;

Get Seller Analytics

SELECT 
    COUNT(d.id) as total_datapods,
    COUNT(d.id) FILTER (WHERE d.published = true) as published_datapods,
    SUM(d.purchase_count) as total_sales,
    SUM(d.purchase_count * d.price_sui) as total_revenue,
    AVG(d.average_rating) as average_rating
FROM datapods d
WHERE d.seller_id = $1;

Get Buyer Purchase History

SELECT 
    p.*,
    d.title as datapod_title,
    d.category,
    u.name as seller_name,
    r.rating,
    r.comment
FROM purchases p
JOIN datapods d ON p.datapod_id = d.id
JOIN users u ON d.seller_id = u.id
LEFT JOIN reviews r ON p.id = r.purchase_id
WHERE p.buyer_id = $1
ORDER BY p.created_at DESC;

Performance Optimization

Connection Pooling

// Backend configuration
const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20, // Maximum pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Query Optimization

• Indexes: B-tree indexes on foreign keys, unique constraints, and frequently queried fields
• JSONB Indexes: GIN indexes on JSONB columns for efficient querying
• Full-text Search: GiST indexes for text search on titles and descriptions
• Partial Indexes: Indexes on frequently filtered subsets (e.g., published=true)

Backup & Recovery

Automated Backups

# Daily backup script
pg_dump -U postgres -d sourcenet -F c -f backup_`date +%Y%m%d`.dump

# Restore from backup
pg_restore -U postgres -d sourcenet -c backup_20241127.dump

Point-in-Time Recovery

# Enable WAL archiving in postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'