Database Schema Documentation

Overview

This comprehensive guide documents the complete database schema for the Ink platform, including entity relationships, table structures, indexes, constraints, and naming conventions.

Target Audience: Developers, database administrators, and architects
Prerequisites: SQL and relational database knowledge
Estimated Time: 60+ minutes (reference document)

Prerequisites

• PostgreSQL knowledge
• Understanding of relational database concepts
• JPA/Hibernate familiarity
• SQL proficiency

Database Architecture

Installation Steps

1. Database Connection

# filepath: /Users/jetstart/dev/jetrev/ink/src/main/resources/application.yml
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/ink_database
    username: ink_user
    password: ink_password
    driver-class-name: org.postgresql.Driver
  
  jpa:
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect

2. Schema Initialization

-- filepath: /Users/jetstart/dev/jetrev/ink/src/main/resources/db/init-schema.sql
-- Create schema
CREATE SCHEMA IF NOT EXISTS ink;

-- Set default search path
ALTER DATABASE ink_database SET search_path TO ink, public;

-- Create extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

Configuration

Core User Management Schema

-- filepath: /Users/jetstart/dev/jetrev/ink/src/main/resources/db/schema/users.sql

-- Users table
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    active BOOLEAN DEFAULT true,
    email_verified BOOLEAN DEFAULT false,
    verification_token VARCHAR(255),
    reset_password_token VARCHAR(255),
    reset_password_expires_at TIMESTAMP,
    last_login_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    version INTEGER DEFAULT 0,
    
    CONSTRAINT chk_username_length CHECK (LENGTH(username) >= 3),
    CONSTRAINT chk_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$')
);

-- Indexes for users
CREATE INDEX idx_users_username ON users(username) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_email ON users(email) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_active ON users(active) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_created_at ON users(created_at);

-- User roles (many-to-many)
CREATE TABLE user_roles (
    user_id BIGINT NOT NULL,
    role VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (user_id, role),
    CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) 
        REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_user_roles_role ON user_roles(role);

-- User sessions
CREATE TABLE user_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id BIGINT NOT NULL,
    token VARCHAR(500) NOT NULL UNIQUE,
    ip_address VARCHAR(45),
    user_agent TEXT,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_accessed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) 
        REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_sessions_user_id ON user_sessions(user_id);
CREATE INDEX idx_sessions_token ON user_sessions(token);
CREATE INDEX idx_sessions_expires_at ON user_sessions(expires_at);

Product and Licensing Schema

-- filepath: /Users/jetstart/dev/jetrev/ink/src/main/resources/db/schema/products.sql

-- Products table
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    sku VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    product_type VARCHAR(50) NOT NULL, -- 'LICENSE', 'SUBSCRIPTION', 'SERVICE'
    active BOOLEAN DEFAULT true,
    metadata JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_price_positive CHECK (price >= 0),
    CONSTRAINT chk_product_type CHECK (product_type IN ('LICENSE', 'SUBSCRIPTION', 'SERVICE'))
);

CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_type ON products(product_type);
CREATE INDEX idx_products_active ON products(active);
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

-- Licenses table
CREATE TABLE licenses (
    id BIGSERIAL PRIMARY KEY,
    license_key VARCHAR(255) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    license_type VARCHAR(50) NOT NULL, -- 'TRIAL', 'PERPETUAL', 'SUBSCRIPTION'
    status VARCHAR(50) NOT NULL DEFAULT 'ACTIVE', -- 'ACTIVE', 'EXPIRED', 'REVOKED', 'SUSPENDED'
    max_activations INTEGER DEFAULT 1,
    current_activations INTEGER DEFAULT 0,
    expires_at TIMESTAMP,
    issued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    activated_at TIMESTAMP,
    revoked_at TIMESTAMP,
    revoke_reason TEXT,
    metadata JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_licenses_user FOREIGN KEY (user_id) 
        REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_licenses_product FOREIGN KEY (product_id) 
        REFERENCES products(id) ON DELETE RESTRICT,
    CONSTRAINT chk_license_type CHECK (license_type IN ('TRIAL', 'PERPETUAL', 'SUBSCRIPTION')),
    CONSTRAINT chk_license_status CHECK (status IN ('ACTIVE', 'EXPIRED', 'REVOKED', 'SUSPENDED')),
    CONSTRAINT chk_activations CHECK (current_activations <= max_activations)
);

CREATE INDEX idx_licenses_key ON licenses(license_key);
CREATE INDEX idx_licenses_user_id ON licenses(user_id);
CREATE INDEX idx_licenses_product_id ON licenses(product_id);
CREATE INDEX idx_licenses_status ON licenses(status);
CREATE INDEX idx_licenses_expires_at ON licenses(expires_at);

-- License activations
CREATE TABLE license_activations (
    id BIGSERIAL PRIMARY KEY,
    license_id BIGINT NOT NULL,
    machine_id VARCHAR(255) NOT NULL,
    machine_name VARCHAR(255),
    ip_address VARCHAR(45),
    activated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_checked_at TIMESTAMP,
    deactivated_at TIMESTAMP,
    
    CONSTRAINT fk_activations_license FOREIGN KEY (license_id) 
        REFERENCES licenses(id) ON DELETE CASCADE,
    CONSTRAINT uq_license_machine UNIQUE (license_id, machine_id)
);

CREATE INDEX idx_activations_license_id ON license_activations(license_id);
CREATE INDEX idx_activations_machine_id ON license_activations(machine_id);

Subscription Management Schema

-- filepath: /Users/jetstart/dev/jetrev/ink/src/main/resources/db/schema/subscriptions.sql

-- Subscription plans
CREATE TABLE subscription_plans (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    billing_period VARCHAR(20) NOT NULL, -- 'MONTHLY', 'YEARLY'
    price DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    trial_days INTEGER DEFAULT 0,
    features JSONB,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_billing_period CHECK (billing_period IN ('MONTHLY', 'YEARLY', 'QUARTERLY')),
    CONSTRAINT chk_price_positive CHECK (price >= 0)
);

CREATE INDEX idx_subscription_plans_active ON subscription_plans(active);

-- Subscriptions
CREATE TABLE subscriptions (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    plan_id BIGINT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'ACTIVE',
    current_period_start TIMESTAMP NOT NULL,
    current_period_end TIMESTAMP NOT NULL,
    trial_start TIMESTAMP,
    trial_end TIMESTAMP,
    canceled_at TIMESTAMP,
    cancel_at_period_end BOOLEAN DEFAULT false,
    stripe_subscription_id VARCHAR(255),
    stripe_customer_id VARCHAR(255),
    metadata JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_subscriptions_user FOREIGN KEY (user_id) 
        REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_subscriptions_plan FOREIGN KEY (plan_id) 
        REFERENCES subscription_plans(id) ON DELETE RESTRICT,
    CONSTRAINT chk_subscription_status CHECK (
        status IN ('ACTIVE', 'CANCELED', 'EXPIRED', 'PAST_DUE', 'TRIALING')
    )
);

CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_plan_id ON subscriptions(plan_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);
CREATE INDEX idx_subscriptions_stripe_id ON subscriptions(stripe_subscription_id);
CREATE INDEX idx_subscriptions_period_end ON subscriptions(current_period_end);

-- Subscription items
CREATE TABLE subscription_items (
    id BIGSERIAL PRIMARY KEY,
    subscription_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    stripe_price_id VARCHAR(255),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_subscription_items_subscription FOREIGN KEY (subscription_id) 
        REFERENCES subscriptions(id) ON DELETE CASCADE,
    CONSTRAINT fk_subscription_items_product FOREIGN KEY (product_id) 
        REFERENCES products(id) ON DELETE RESTRICT,
    CONSTRAINT chk_quantity_positive CHECK (quantity > 0)
);

CREATE INDEX idx_subscription_items_subscription_id ON subscription_items(subscription_id);

Order Management Schema

-- filepath: /Users/jetstart/dev/jetrev/ink/src/main/resources/db/schema/orders.sql

-- Orders table
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
    subtotal DECIMAL(10, 2) NOT NULL,
    tax DECIMAL(10, 2) DEFAULT 0,
    shipping DECIMAL(10, 2) DEFAULT 0,
    discount DECIMAL(10, 2) DEFAULT 0,
    total DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    payment_method VARCHAR(50),
    payment_id VARCHAR(255),
    paid_at TIMESTAMP,
    fulfilled_at TIMESTAMP,
    canceled_at TIMESTAMP,
    cancellation_reason TEXT,
    tracking_number VARCHAR(255),
    shipping_carrier VARCHAR(100),
    notes TEXT,
    metadata JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) 
        REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT chk_order_status CHECK (
        status IN ('PENDING', 'PAID', 'FULFILLED', 'CANCELED', 'REFUNDED', 'PAYMENT_FAILED')
    ),
    CONSTRAINT chk_totals_positive CHECK (
        subtotal >= 0 AND tax >= 0 AND shipping >= 0 AND total >= 0
    )
);

CREATE INDEX idx_orders_order_number ON orders(order_number);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_payment_id ON orders(payment_id);

-- Order items
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    metadata JSONB,
    
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) 
        REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) 
        REFERENCES products(id) ON DELETE RESTRICT,
    CONSTRAINT chk_order_item_quantity CHECK (quantity > 0),
    CONSTRAINT chk_order_item_prices CHECK (unit_price >= 0 AND total >= 0)
);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Usage Examples

JPA Entity Mapping

// filepath: /Users/jetstart/dev/jetrev/ink/src/main/java/com/jetrev/ink/entity/User.java
@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_users_username", columnList = "username"),
    @Index(name = "idx_users_email", columnList = "email")
})
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "username", unique = true, nullable = false, length = 50)
    private String username;
    
    @Column(name = "email", unique = true, nullable = false)
    private String email;
    
    @Column(name = "password", nullable = false)
    private String password;
    
    @Column(name = "first_name", length = 100)
    private String firstName;
    
    @Column(name = "last_name", length = 100)
    private String lastName;
    
    @Column(name = "active")
    private boolean active = true;
    
    @Column(name = "email_verified")
    private boolean emailVerified = false;
    
    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(
        name = "user_roles",
        joinColumns = @JoinColumn(name = "user_id")
    )
    @Column(name = "role")
    private Set<String> roles = new HashSet<>();
    
    @Column(name = "created_at", nullable = false, updatable = false)
    private Instant createdAt;
    
    @Column(name = "updated_at", nullable = false)
    private Instant updatedAt;
    
    @Column(name = "deleted_at")
    private Instant deletedAt;
    
    @Version
    @Column(name = "version")
    private Integer version;
    
    @PrePersist
    protected void onCreate() {
        createdAt = Instant.now();
        updatedAt = Instant.now();
    }
    
    @PreUpdate
    protected void onUpdate() {
        updatedAt = Instant.now();
    }
}

Complex Queries

// filepath: /Users/jetstart/dev/jetrev/ink/src/main/java/com/jetrev/ink/repository/UserRepository.java
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    Optional<User> findByUsername(String username);
    
    Optional<User> findByEmail(String email);
    
    boolean existsByUsername(String username);
    
    boolean existsByEmail(String email);
    
    @Query("SELECT u FROM User u JOIN u.roles r WHERE r = :role AND u.active = true")
    List<User> findByRolesContaining(@Param("role") String role);
    
    @Query("SELECT u FROM User u WHERE u.createdAt >= :startDate AND u.active = true")
    List<User> findRecentActiveUsers(@Param("startDate") Instant startDate);
    
    @Modifying
    @Query("UPDATE User u SET u.deletedAt = :deletedAt WHERE u.id = :id")
    void softDelete(@Param("id") Long id, @Param("deletedAt") Instant deletedAt);
}

Verification

Schema Validation Script

-- filepath: /Users/jetstart/dev/jetrev/ink/src/test/resources/db/verify-schema.sql

-- Verify all required tables exist
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
AND table_name IN (
    'users', 'user_roles', 'user_sessions',
    'products', 'licenses', 'license_activations',
    'subscription_plans', 'subscriptions', 'subscription_items',
    'orders', 'order_items'
);

-- Verify indexes
SELECT indexname, tablename 
FROM pg_indexes 
WHERE schemaname = 'public' 
ORDER BY tablename, indexname;

-- Verify foreign keys
SELECT
    tc.table_name, 
    kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name;

Troubleshooting

Common Schema Issues

Issue: Constraint violation on insert

-- Check existing constraints
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

-- Temporarily disable constraint (use with caution)
ALTER TABLE users DISABLE TRIGGER ALL;
-- ... perform operation
ALTER TABLE users ENABLE TRIGGER ALL;

Issue: Index not being used

-- Analyze query plan
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- Force index rebuild
REINDEX TABLE users;

-- Update statistics
ANALYZE users;

Code Examples

Database Migration Script

<!-- filepath: /Users/jetstart/dev/jetrev/ink/src/main/resources/db/changelog/changes/001-create-users-table.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.20.xsd">

    <changeSet id="001-create-users-table" author="developer">
        <createTable tableName="users">
            <column name="id" type="BIGSERIAL" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="username" type="VARCHAR(50)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="email" type="VARCHAR(255)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="password" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="active" type="BOOLEAN" defaultValueBoolean="true"/>
            <column name="created_at" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
                <constraints nullable="false"/>
            </column>
        </createTable>
        
        <createIndex indexName="idx_users_username" tableName="users">
            <column name="username"/>
        </createIndex>
        
        <createIndex indexName="idx_users_email" tableName="users">
            <column name="email"/>
        </createIndex>
    </changeSet>
</databaseChangeLog>

Best Practices

1. Use Appropriate Data Types: Choose correct PostgreSQL types
2. Index Strategy: Index foreign keys and frequently queried columns
3. Constraints: Use CHECK constraints for data validation
4. Soft Deletes: Use deleted_at timestamp instead of hard deletes
5. Audit Fields: Include created_at, updated_at on all tables
6. Versioning: Use optimistic locking with @Version
7. JSONB: Use JSONB for flexible metadata fields
8. Naming: Follow consistent naming conventions
9. Foreign Keys: Always define foreign key constraints
10. Documentation: Document all tables and non-obvious columns

Database Conventions

Naming Conventions

-- Tables: plural, snake_case
users, products, subscriptions

-- Columns: snake_case
first_name, created_at, is_active

-- Indexes: idx_{table}_{column(s)}
idx_users_email, idx_orders_user_id

-- Foreign Keys: fk_{table}_{referenced_table}
fk_orders_user, fk_licenses_product

-- Unique Constraints: uq_{table}_{column(s)}
uq_users_email, uq_licenses_key

-- Check Constraints: chk_{table}_{description}
chk_users_email_format, chk_price_positive

Performance Optimization

Index Optimization

-- Create partial index for active users only
CREATE INDEX idx_users_active ON users(email) WHERE active = true AND deleted_at IS NULL;

-- Create composite index for common queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at DESC);

-- Create index for JSONB queries
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

Query Optimization

-- Use EXPLAIN ANALYZE to check query performance
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id;

Related Documentation

• Local Development Setup
• Test Database Setup
• JPA Best Practices

Additional Resources

• PostgreSQL Documentation
• Liquibase Documentation
• JPA Specification

---

Next Steps: Learn about JPA Best Practices for entity design and optimization.