Database Configuration

PostgreSQL Setup

Installation

1. Windows: Download from https://www.postgresql.org/download/windows/
2. Linux: sudo apt-get install postgresql
3. Mac: brew install postgresql

Create Database

CREATE DATABASE filesharing;

Create User (Optional)

CREATE USER xerox WITH PASSWORD 'xerox123';
GRANT ALL PRIVILEGES ON DATABASE filesharing TO xerox;

Connection String Format

postgres://username:password@host:port/database?sslmode=disable

Schema Initialization

The database schema is automatically created on backend startup via backend/internal/database/schema.go.

The schema includes:

• Table creation with proper constraints
• Index creation for performance
• Enum type definitions
• Foreign key relationships

Database Tables

1. users Table

Stores all user accounts (customers, shopkeepers, admins).

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL,                    -- 'customer', 'shopkeeper', or 'admin'
    lat DOUBLE PRECISION,                  -- Latitude (for shopkeepers)
    long DOUBLE PRECISION,                 -- Longitude (for shopkeepers)
    address TEXT,                          -- Shop address
    is_open BOOLEAN DEFAULT FALSE,         -- Shop open/closed (default closed; heartbeat/toggle opens)
    full_name TEXT,                        -- User's full name
    email TEXT,                            -- Email address (unique)
    phone TEXT,                            -- Phone number
    shop_name TEXT,                        -- Shop name (for shopkeepers)
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

Key Fields:

• role: Determines user type and access level
• lat/long: Required for shopkeepers to appear on map
• is_open: Shopkeepers can toggle shop availability

2. files Table

Stores uploaded files and print job information.

CREATE TABLE files (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),      -- Customer who uploaded
    file_path TEXT NOT NULL,               -- Storage path (local or S3)
    unique_code TEXT UNIQUE NOT NULL,      -- 6-character code for retrieval
    status file_status DEFAULT 'uploaded', -- ENUM: 'uploaded', 'downloaded', 'withdrawn'
    created_at TIMESTAMP DEFAULT NOW(),
    print_type TEXT DEFAULT 'private',     -- 'private' or 'queue'
    copies INT DEFAULT 1,
    print_mode TEXT DEFAULT 'single',      -- 'single' or 'double'
    color_mode TEXT DEFAULT 'bw',           -- 'bw' or 'color'
    paper_size TEXT DEFAULT 'A4',          -- 'A4', 'Letter', etc.
    num_pages INT DEFAULT 0,
    total_cost DECIMAL(10,2) DEFAULT 0,
    shop_id INT REFERENCES users(id),      -- Shopkeeper assigned (for queue prints)
    queue_position INT,                    -- Position in shop queue
    comment TEXT,                          -- Customer notes
    payment_order_id INT REFERENCES payment_orders(id),
    payment_status TEXT DEFAULT 'unpaid'   -- 'unpaid', 'paid', 'refunded'
);

3. payment_orders Table

Stores payment transactions and order details.

CREATE TABLE payment_orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) NOT NULL,
    order_id TEXT UNIQUE NOT NULL,         -- Razorpay order_id
    payment_id TEXT,                       -- Razorpay payment_id (after payment)
    amount DECIMAL(10,2) NOT NULL,
    status TEXT DEFAULT 'pending',         -- 'pending', 'paid', 'failed', 'refunded', 'expired'
    payment_gateway TEXT DEFAULT 'razorpay',
    shopkeeper_id INT REFERENCES users(id), -- NULL for private prints
    platform_commission DECIMAL(10,2) DEFAULT 0,
    shopkeeper_amount DECIMAL(10,2),      -- Amount to pay shopkeeper
    copies INT DEFAULT 1,
    print_mode TEXT DEFAULT 'single',
    color_mode TEXT DEFAULT 'bw',
    paper_size TEXT DEFAULT 'A4',
    print_type TEXT DEFAULT 'private',    -- 'private' or 'queue'
    comment TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    paid_at TIMESTAMP,
    expires_at TIMESTAMP,
    file_id INT REFERENCES files(id)
);

4. shopkeeper_payouts Table

Tracks payouts to shopkeepers.

CREATE TABLE shopkeeper_payouts (
    id SERIAL PRIMARY KEY,
    shopkeeper_id INT REFERENCES users(id) NOT NULL,
    payment_order_id INT REFERENCES payment_orders(id) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TEXT DEFAULT 'pending',         -- 'pending', 'paid', 'failed'
    payout_method TEXT,                    -- Payment method used
    payout_reference TEXT,                 -- Transaction reference
    paid_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(shopkeeper_id, payment_order_id)
);

Note: users also has wallet_balance, last_app_heartbeat_at, and last_web_activity_at (for shop auto-close). payment_orders has payment_method and wallet_amount for wallet/hybrid payments.

5. password_reset_tokens Table

Stores password reset tokens for email verification.

CREATE TABLE password_reset_tokens (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    token TEXT UNIQUE NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    used BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

Other tables (schema.go)

• app_download_links — Single row (id=1): Windows shopkeeper URL, Android customer URL, iOS customer URL, and "coming soon" flags. Used by the public Download Apps page and admin PUT /admin/app-downloads.
• wallet_transactions — Wallet top-up, payment, and refund history per user.
• wallet_topup_orders — Pending/completed Razorpay orders for wallet top-up.

Database Relationships

users (1) ──< (many) files
users (1) ──< (many) payment_orders
users (1) ──< (many) shopkeeper_payouts
users (1) ──< (many) password_reset_tokens
payment_orders (1) ──< (many) shopkeeper_payouts
payment_orders (1) ──< (1) files

Migrations

Located in backend/migrations/:

• 001_add_queue_print_columns.sql - Queue print support
• 002_add_address_column.sql - Address field
• 003_add_withdrawn_status.sql - Withdrawal status
• 004_add_payment_system.sql - Payment tables
• 005_add_payment_columns_to_files.sql - Payment links in files

Note: Migrations are automatically applied via schema initialization in schema.go.

Common Queries

Get all shopkeepers with locations

SELECT id, username, shop_name, lat, long, is_open, address
FROM users
WHERE role = 'shopkeeper' AND lat IS NOT NULL AND long IS NOT NULL;

Get customer's files

SELECT f.*, po.status as payment_status, po.amount
FROM files f
LEFT JOIN payment_orders po ON f.payment_order_id = po.id
WHERE f.user_id = $1
ORDER BY f.created_at DESC;

Get shop queue

SELECT f.*, u.username as customer_name
FROM files f
JOIN users u ON f.user_id = u.id
WHERE f.shop_id = $1 
  AND f.print_type = 'queue'
  AND f.status = 'uploaded'
ORDER BY f.queue_position ASC;

Get pending payouts

SELECT sp.*, u.username as shopkeeper_name, po.amount as order_amount
FROM shopkeeper_payouts sp
JOIN users u ON sp.shopkeeper_id = u.id
JOIN payment_orders po ON sp.payment_order_id = po.id
WHERE sp.status = 'pending'
ORDER BY sp.created_at ASC;