database-rules

Database Rules — Aiyu Agent Kit

Data is the foundation. Design schemas that scale, query efficiently, and migrate safely.

---

🔴 CRITICAL: Schema Design

Naming Conventions

Element	Convention	Example
Tables	snake_case, plural	user_accounts, order_items
Columns	snake_case	created_at, user_id
Primary keys	id or table_name_id	id, user_id
Foreign keys	referenced_table_id	user_id → references users.id
Indexes	idx_table_columns	idx_users_email
Junction tables	table1_table2	users_roles

Column Types

-- ✅ GOOD — Appropriate types
CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       VARCHAR(255) NOT NULL,
    name        VARCHAR(100) NOT NULL,
    role        VARCHAR(20) NOT NULL DEFAULT 'user',
    is_active   BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ❌ BAD — Wrong types, no constraints
CREATE TABLE users (
    id          INT,          -- UUID better for distributed
    email       TEXT,         -- Too broad, no length limit
    name        TEXT,         -- No length constraint
    role        TEXT,         -- Should be ENUM or constrained
    is_active   INT,          -- Boolean, not INT
    created_at  VARCHAR(20)   -- Timestamp, not string
);

Indexing Strategy

-- Index columns used in WHERE, JOIN, ORDER BY
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Composite index for multi-column queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index for filtered queries
CREATE INDEX idx_users_active ON users(email) WHERE is_active = true;

-- Unique constraint for business rules
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);