GSign Docs

Appearance

Database Schema ​

GSign database architecture and schema design.

Overview ​

DatabasePurposePort
PostgreSQLPrimary data (users, documents, envelopes)5432
MongoDBAudit logs, document metadata27017
RedisCache, sessions, queues6379

PostgreSQL Schema ​

Users & Organizations ​

sql
-- Organizations (tenants)
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    plan_id UUID REFERENCES plans(id),
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Users
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    role VARCHAR(50) DEFAULT 'user',
    status VARCHAR(20) DEFAULT 'active',
    email_verified_at TIMESTAMPTZ,
    last_login_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

Documents ​

sql
-- Documents
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) NOT NULL,
    organization_id UUID REFERENCES organizations(id) NOT NULL,
    name VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL, -- pdf, docx, xlsx
    mime_type VARCHAR(100) NOT NULL,
    size BIGINT NOT NULL,
    page_count INTEGER,
    s3_key VARCHAR(500) NOT NULL,
    status VARCHAR(20) DEFAULT 'processing',
    hash VARCHAR(64), -- SHA-256
    encrypted BOOLEAN DEFAULT true,
    metadata JSONB DEFAULT '{}',
    tags TEXT[],
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_documents_user ON documents(user_id);
CREATE INDEX idx_documents_org ON documents(organization_id);
CREATE INDEX idx_documents_status ON documents(status);

Envelopes & Recipients ​

sql
-- Envelopes
CREATE TABLE envelopes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) NOT NULL,
    organization_id UUID REFERENCES organizations(id) NOT NULL,
    document_id UUID REFERENCES documents(id) NOT NULL,
    name VARCHAR(255),
    status VARCHAR(20) DEFAULT 'draft',
    routing_type VARCHAR(20) DEFAULT 'sequential',
    message TEXT,
    expires_at TIMESTAMPTZ,
    sent_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    voided_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Recipients
CREATE TABLE recipients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    envelope_id UUID REFERENCES envelopes(id) NOT NULL,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    role VARCHAR(20) DEFAULT 'signer',
    routing_order INTEGER DEFAULT 1,
    status VARCHAR(20) DEFAULT 'pending',
    access_code VARCHAR(20),
    auth_method VARCHAR(20) DEFAULT 'email',
    signed_at TIMESTAMPTZ,
    declined_at TIMESTAMPTZ,
    decline_reason TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Signature Fields
CREATE TABLE signature_fields (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    envelope_id UUID REFERENCES envelopes(id) NOT NULL,
    recipient_id UUID REFERENCES recipients(id),
    type VARCHAR(20) NOT NULL, -- signature, initial, text, date, checkbox
    page_number INTEGER NOT NULL,
    x DECIMAL(10,2) NOT NULL,
    y DECIMAL(10,2) NOT NULL,
    width DECIMAL(10,2) NOT NULL,
    height DECIMAL(10,2) NOT NULL,
    required BOOLEAN DEFAULT true,
    value TEXT,
    signed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Certificates ​

sql
-- Certificates
CREATE TABLE certificates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) NOT NULL,
    label VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL, -- self_signed, ca_issued, eidas, corporate
    status VARCHAR(20) DEFAULT 'active',
    is_default BOOLEAN DEFAULT false,
    serial_number VARCHAR(100),
    subject_cn VARCHAR(255),
    issuer_cn VARCHAR(255),
    valid_from TIMESTAMPTZ NOT NULL,
    valid_to TIMESTAMPTZ NOT NULL,
    key_type VARCHAR(20) DEFAULT 'RSA',
    key_size INTEGER DEFAULT 2048,
    usage_count INTEGER DEFAULT 0,
    last_used_at TIMESTAMPTZ,
    hsm_key_id VARCHAR(255), -- Reference to HSM
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_certificates_user ON certificates(user_id);
CREATE INDEX idx_certificates_default ON certificates(user_id, is_default);

Billing ​

sql
-- Subscriptions
CREATE TABLE subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) NOT NULL,
    plan_id UUID REFERENCES plans(id) NOT NULL,
    stripe_subscription_id VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active',
    current_period_start TIMESTAMPTZ,
    current_period_end TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Usage Records
CREATE TABLE usage_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) NOT NULL,
    type VARCHAR(50) NOT NULL, -- envelope, signature, certificate
    quantity INTEGER DEFAULT 1,
    unit_price DECIMAL(10,4),
    recorded_at TIMESTAMPTZ DEFAULT NOW()
);

MongoDB Collections ​

Audit Events ​

javascript
// audit_events collection
{
  _id: ObjectId(),
  user_id: "uuid",
  organization_id: "uuid",
  action: "document.upload",
  resource_type: "document",
  resource_id: "uuid",
  ip_address: "192.168.1.1",
  user_agent: "Mozilla/5.0...",
  location: {
    country: "MN",
    city: "Ulaanbaatar"
  },
  metadata: {
    file_name: "contract.pdf",
    file_size: 1024000
  },
  timestamp: ISODate("2026-01-24T10:00:00Z")
}

// Indexes
db.audit_events.createIndex({ user_id: 1, timestamp: -1 })
db.audit_events.createIndex({ organization_id: 1, timestamp: -1 })
db.audit_events.createIndex({ resource_type: 1, resource_id: 1 })

Webhook Deliveries ​

javascript
// webhook_deliveries collection
{
  _id: ObjectId(),
  webhook_id: "uuid",
  event_type: "envelope_completed",
  payload: {
    envelope_id: "uuid",
    completed_at: "2026-01-24T10:00:00Z"
  },
  endpoint_url: "https://api.example.com/webhook",
  status: "delivered", // pending, delivered, failed
  response_code: 200,
  response_body: "OK",
  attempts: 1,
  max_attempts: 5,
  last_attempt_at: ISODate("2026-01-24T10:00:00Z"),
  delivered_at: ISODate("2026-01-24T10:00:00Z")
}

Redis Keys ​

# Sessions
session:{user_id}                 → JWT token data (TTL: 24h)

# Cache
cache:user:{id}                   → User object (TTL: 5m)
cache:document:{id}               → Document metadata (TTL: 5m)
cache:organization:{id}           → Organization data (TTL: 5m)

# Rate Limiting
rate:ip:{ip_address}              → Request count (TTL: 1h)
rate:api:{api_key}                → Request count (TTL: 1h)

# Queues (Asynq)
asynq:{queue}:pending             → Pending tasks
asynq:{queue}:active              → Active tasks
asynq:{queue}:completed           → Completed tasks

# Locks
lock:document:{id}                → Document processing lock (TTL: 5m)
lock:envelope:{id}                → Envelope update lock (TTL: 30s)

Connection Strings ​

bash
# PostgreSQL
postgresql://gsign:gsign_dev_2026@gsign-postgres:5432/gsign

# MongoDB
mongodb://gsign:gsign_dev_2026@gsign-mongodb:27017/gsign

# Redis
redis://:gsign_dev_2026@gsign-redis:6379

Database Management ​

Access Tools ​

ToolURLPurpose
Adminerhttps://pgadmin.gesign.mnPostgreSQL UI
Mongo Expresshttps://mongo.gesign.mnMongoDB UI
Redis Commanderhttps://redis.gesign.mnRedis UI

Migrations ​

Backend uses golang-migrate for schema migrations:

bash
# Run migrations
migrate -path migrations -database "postgresql://..." up

# Rollback
migrate -path migrations -database "postgresql://..." down 1

GSign Digital Signature Platform

Copyright © 2026 GSign