Saltar a contenido

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