Skip to main content

Database Schema

This guide details the database structure used in Studio.

Overview​

The database uses a relational model with the following main entities:

  • Users
  • Content
  • Media
  • Workflows
  • Analytics

Entity Relationship Diagram​

Tables​

Users Table​

CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Content Table​

CREATE TABLE content (
    id UUID PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT,
    status VARCHAR(50) NOT NULL,
    author_id UUID REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Media Table​

CREATE TABLE media (
    id UUID PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    size INTEGER NOT NULL,
    url VARCHAR(255) NOT NULL,
    content_id UUID REFERENCES content(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes​

tip

The following indexes are recommended for optimal performance:

CREATE INDEX idx_content_author ON content(author_id);
CREATE INDEX idx_media_content ON media(content_id);
CREATE INDEX idx_users_email ON users(email);

Data Types​

Column TypeDescriptionExample
UUIDUnique identifier123e4567-e89b-12d3-a456-426614174000
VARCHARVariable length string"example"
TEXTLong text content"Long content..."
TIMESTAMPDate and time2024-03-20 10:00:00

Relationships​

One-to-Many​

  • User to Content
  • Content to Media
  • User to Workflows

Many-to-Many​

  • Content to Tags
  • Users to Roles

Next Steps​

Learn about the Process Flow to understand how data moves through the system.