DocuGraph Relational Database Schema & Data Dictionary

Estado de fase (MASTER-00): POST_MVP — documento de catálogo de producto sujeto a validación; no es alcance aprobado de Gate 0 / PoC / MVP. Gobernanza: MASTER-00.

DocuGraph Relational Database Schema & Data Dictionary

1. Overview & Entity Relationship Architecture

The DocuGraph backend service (:docugraph-backend) relies on a high-performance PostgreSQL relational database to index documentation projects, atomic nodes, directed edges, user sessions, subscriptions, and MCP authentication API keys.

                      +-------------------+
                      |       users       |
                      +---------+---------+
                                |
        +-----------------------+-----------------------+
        | 1                     | 1                     | 1
        v                       v                       v
  +-----+-----+           +-----+-----+           +-----+-----+
  |  projects |           |subscriptions|         |mcp_api_keys|
  +-----+-----+           +-----------+           +-----------+
        | 1
        +-----------------------+
        | 1                     | 1
        v                       v
  +-----+-----+           +-----+-----+
  |   nodes   |<----------| node_edges|
  +-----------+ 1       * +-----------+

2. Entity Traceability Matrix

Table Name	Description	Primary Key	Foreign Keys	Upstream Atomic Links
`users`	User credentials, identity, OAuth metadata	`id` (UUID)	None	`US-01`, `IX-01`, `BE-01`
`subscriptions`	User subscription tier, RevenueCat entitlement state, offline grace limit	`id` (UUID)	`user_id` $\to$ `users(id)`	`US-06`, `IX-01`, `BE-06`
`projects`	Registered documentation projects, workspace directory, Git remote tracking	`id` (UUID)	`owner_id` $\to$ `users(id)`	`US-02`, `IX-02`, `BE-02`
`nodes`	Parsed atomic documentation nodes (frontmatter, checksum, file path)	`id` (UUID)	`project_id` $\to$ `projects(id)`	`US-03`, `US-04`, `US-05`, `IX-03`, `IX-04`, `BE-03`, `BE-04`, `BE-05`
`node_edges`	Graph adjacency edges (`depends_on`) connecting source and target nodes	`id` (UUID)	`project_id` $\to$ `projects(id)`, `source_node_id` $\to$ `nodes(id)`, `target_node_id` $\to$ `nodes(id)`	`US-03`, `US-04`, `US-05`, `IX-03`, `IX-04`, `BE-03`, `BE-04`, `BE-05`
`mcp_api_keys`	API keys generated for MCP Server authentication and AI coding agents	`id` (UUID)	`user_id` $\to$ `users(id)`	`US-01`, `US-04`, `IX-04`, `BE-01`, `BE-04`

3. Complete PostgreSQL DDL Script

-- =============================================================================
-- DocuGraph Relational Database Schema DDL (PostgreSQL 14+)
-- Specification ID: SCHEMA-01
-- =============================================================================

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

-- -----------------------------------------------------------------------------
-- Helper Function: Auto-update updated_at timestamp
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- -----------------------------------------------------------------------------
-- Table 1: users
-- Source Stories: US-01 | IX-01 | BE-01
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    oauth_provider VARCHAR(50) DEFAULT 'email',
    oauth_id VARCHAR(255),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_oauth ON users(oauth_provider, oauth_id);

CREATE TRIGGER trg_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- -----------------------------------------------------------------------------
-- Table 2: subscriptions
-- Source Stories: US-06 | BE-06
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    entitlement_id VARCHAR(50) NOT NULL DEFAULT 'free',
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    revenuecat_customer_id VARCHAR(255),
    revenuecat_entitlement_id VARCHAR(100) DEFAULT 'pro_tier',
    plan_type VARCHAR(50) DEFAULT 'free',
    current_period_start TIMESTAMPTZ,
    current_period_end TIMESTAMPTZ,
    offline_grace_until TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_sub_status CHECK (status IN ('active', 'canceled', 'grace_period', 'expired')),
    CONSTRAINT chk_sub_entitlement CHECK (entitlement_id IN ('free', 'pro_tier'))
);

CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_user_status ON subscriptions(user_id, status);

CREATE TRIGGER trg_subscriptions_updated_at
    BEFORE UPDATE ON subscriptions
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- -----------------------------------------------------------------------------
-- Table 3: projects
-- Source Stories: US-02 | IX-02 | BE-02
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    workspace_path VARCHAR(512) NOT NULL,
    git_remote_url VARCHAR(512),
    git_default_branch VARCHAR(100) DEFAULT 'main',
    schema_config JSONB NOT NULL DEFAULT '{}'::jsonb,
    node_count INT NOT NULL DEFAULT 0,
    edge_count INT NOT NULL DEFAULT 0,
    last_indexed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_projects_owner_path UNIQUE (owner_id, workspace_path)
);

CREATE INDEX idx_projects_owner_id ON projects(owner_id);

CREATE TRIGGER trg_projects_updated_at
    BEFORE UPDATE ON projects
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- -----------------------------------------------------------------------------
-- Table 4: nodes
-- Source Stories: US-03 | US-04 | US-05 | IX-03 | IX-04 | BE-03 | BE-04 | BE-05
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS nodes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    atomic_id VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    document_type VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'DRAFT',
    relative_file_path VARCHAR(512) NOT NULL,
    file_checksum_sha256 VARCHAR(64) NOT NULL,
    frontmatter_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    tags TEXT[] DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_nodes_project_atomic_id UNIQUE (project_id, atomic_id),
    CONSTRAINT uq_nodes_project_file_path UNIQUE (project_id, relative_file_path),
    CONSTRAINT chk_nodes_status CHECK (status IN ('DRAFT', 'IN_REVIEW', 'APPROVED', 'DEPRECATED'))
);

CREATE INDEX idx_nodes_project_id ON nodes(project_id);
CREATE INDEX idx_nodes_atomic_id ON nodes(atomic_id);
CREATE INDEX idx_nodes_project_type ON nodes(project_id, document_type);
CREATE INDEX idx_nodes_tags_gin ON nodes USING GIN (tags);
CREATE INDEX idx_nodes_frontmatter_gin ON nodes USING GIN (frontmatter_json);

CREATE TRIGGER trg_nodes_updated_at
    BEFORE UPDATE ON nodes
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- -----------------------------------------------------------------------------
-- Table 5: node_edges
-- Source Stories: US-03 | US-04 | US-05 | IX-03 | IX-04 | BE-03 | BE-04 | BE-05
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS node_edges (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    source_node_id UUID NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    target_node_id UUID NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    relationship_type VARCHAR(50) NOT NULL DEFAULT 'depends_on',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_node_edges_unique UNIQUE (source_node_id, target_node_id, relationship_type),
    CONSTRAINT chk_node_edges_no_self_loop CHECK (source_node_id <> target_node_id),
    CONSTRAINT chk_node_edges_rel_type CHECK (relationship_type IN ('depends_on', 'depended_by', 'related', 'tables'))
);

CREATE INDEX idx_node_edges_project_id ON node_edges(project_id);
CREATE INDEX idx_node_edges_source ON node_edges(source_node_id);
CREATE INDEX idx_node_edges_target ON node_edges(target_node_id);
CREATE INDEX idx_node_edges_proj_source ON node_edges(project_id, source_node_id);
CREATE INDEX idx_node_edges_proj_target ON node_edges(project_id, target_node_id);

-- -----------------------------------------------------------------------------
-- Table 6: mcp_api_keys
-- Source Stories: US-01 | US-04 | IX-04 | BE-01 | BE-04
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mcp_api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key_name VARCHAR(100) NOT NULL,
    api_key_hash VARCHAR(255) NOT NULL UNIQUE,
    key_prefix VARCHAR(16) NOT NULL,
    permissions TEXT[] DEFAULT '{read_context,validate_graph}',
    last_used_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ,
    is_revoked BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_mcp_keys_hash ON mcp_api_keys(api_key_hash);
CREATE INDEX idx_mcp_keys_user_revoked ON mcp_api_keys(user_id, is_revoked);

CREATE TRIGGER trg_mcp_api_keys_updated_at
    BEFORE UPDATE ON mcp_api_keys
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

4. Traceability Index

Upstream Dependencies:
- docs/00_BUSINESS_VISION.md (VISION-00) — Monetization & Entitlement Strategy
- docs/user_stories/US-01_login.md (US-01) through US-06_revenuecat_pro.md (US-06)
- docs/01_PROJECT_MANIFEST.md (MANIFEST-01) — Traceability Matrix
Downstream Artifacts:
- docs/interaction/IX-01_login-flow.md (IX-01) through IX-04_mcp-context-bundle-flow.md (IX-04)
- docs/backend/BE-01_auth-login.md (BE-01) through BE-06_paywall-check-entitlement.md (BE-06)