database-patterns

Use when designing database schemas, implementing repository patterns, writing optimized queries, managing migrations, or working with indexes and transactions for SQL/NoSQL databases.

Zur Installation springen

Repository: MadAppGang/magus
Letzte Quellaktivität: 29. Juli 2026 um 14:55
Erkannte Sprache von SKILL.md: Englisch
Sterne: 7
Forks: 4

Installationsoptionen

Standardmäßig ist der Prompt ausgewählt, der zuerst die Quelle prüft. Sie können zu einem direkten Befehl wechseln oder eine lokale Kopie herunterladen.

Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.

Ein direkter Befehl überspringt den Prüf-Prompt. Prüfen Sie die Quelle, bevor Sie ihn ausführen.

npx skills add https://github.com/MadAppGang/magus --skill database-patterns

Der Befehl bleibt in einer Zeile. Scrollen Sie horizontal, um ihn vor dem Kopieren vollständig zu prüfen.

Sie bevorzugen eine lokale Kopie? Laden Sie die Dateien herunter, die SkillsMP derzeit vorliegen.

Quelldateien prüfen

Lesen Sie SKILL.md und alle von SkillsMP angezeigten Begleitdateien, bevor Sie sich für eine Installation entscheiden.

SKILL.md

Quellanweisungen · Schreibgeschützte Vorschau

Zurück zur Suche Zurück zur Installation

agent-model

Picks the browser-use agent brain LLM per task. Use when choosing a model for retry_with_browser_use_agent or a cloud session, or the user mentions a cheap, slow, or smart model.

2026-08-09

core-api

Reference for the Browser Use MCP tools — parameters, returns, session lifecycle. Use when navigating, clicking, typing, evaluating JS, or extracting page content.

2026-08-09

debug-ui

Visual UI debugging — screenshot capture and analysis, responsive layout checking at multiple viewport sizes, CSS validation via DOM state, visual regression detection, before/after state comparison.

2026-08-09

Alle aus diesem Repository ansehen

-- One-to-Many
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_orders_user ON orders(user_id);

-- Many-to-Many
CREATE TABLE order_products (
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID REFERENCES products(id) ON DELETE CASCADE,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Self-referential (tree/hierarchy)
CREATE TABLE categories (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id UUID REFERENCES categories(id)
);
CREATE INDEX idx_categories_parent ON categories(parent_id);

-- Primary key (automatic)
CREATE TABLE users (id UUID PRIMARY KEY);

-- Foreign keys
CREATE INDEX idx_orders_user ON orders(user_id);

-- Frequent filters
CREATE INDEX idx_users_status ON users(status);

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

-- Partial index for common queries
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Use specific columns, not *
SELECT id, name, email FROM users WHERE id = $1;

-- Limit results
SELECT * FROM users ORDER BY created_at DESC LIMIT 20;

-- Exists vs COUNT
SELECT EXISTS(SELECT 1 FROM users WHERE email = $1);

-- Batch inserts
INSERT INTO users (name, email) VALUES
    ('User 1', 'user1@example.com'),
    ('User 2', 'user2@example.com'),
    ('User 3', 'user3@example.com');

-- Offset pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 100;

-- Cursor pagination (better performance)
SELECT * FROM users
WHERE created_at < $cursor
ORDER BY created_at DESC
LIMIT 20;

-- Keyset pagination with tie-breaker
SELECT * FROM users
WHERE (created_at, id) < ($cursor_time, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Upsert (INSERT or UPDATE)
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = $1;
SELECT * FROM users WHERE deleted_at IS NULL;

-- Lock for update (prevent race conditions)
SELECT * FROM accounts WHERE id = $1 FOR UPDATE;

-- Bulk update
UPDATE orders SET status = 'shipped'
WHERE id = ANY($1::uuid[]);

interface UserRepository {
  findById(id: string): Promise<User | null>;
  findByEmail(email: string): Promise<User | null>;
  findAll(filter: UserFilter, pagination: Pagination): Promise<PaginatedResult<User>>;
  create(data: CreateUserInput): Promise<User>;
  update(id: string, data: UpdateUserInput): Promise<User>;
  delete(id: string): Promise<void>;
}

class PostgresUserRepository implements UserRepository {
  constructor(private db: Database) {}

  async findById(id: string): Promise<User | null> {
    const result = await this.db.query(
      'SELECT * FROM users WHERE id = $1 AND deleted_at IS NULL',
      [id]
    );
    return result.rows[0] || null;
  }

  async create(data: CreateUserInput): Promise<User> {
    const result = await this.db.query(
      `INSERT INTO users (name, email, password_hash)
       VALUES ($1, $2, $3)
       RETURNING *`,
      [data.name, data.email, await hashPassword(data.password)]
    );
    return result.rows[0];
  }
}

async function transferFunds(fromId: string, toId: string, amount: number) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Lock accounts
    await client.query(
      'SELECT * FROM accounts WHERE id IN ($1, $2) FOR UPDATE',
      [fromId, toId]
    );

    // Debit
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );

    // Credit
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );

    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

-- Set isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

migrations/
├── 001_create_users.sql
├── 002_add_user_status.sql
├── 003_create_orders.sql
└── 004_add_order_index.sql

-- Always reversible
-- UP
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- DOWN
ALTER TABLE users DROP COLUMN phone;

-- Non-blocking index creation
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);

-- Safe column renames (PostgreSQL)
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Add NOT NULL safely
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
UPDATE users SET status = 'active' WHERE status IS NULL;
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,              // Max connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Embedded (for one-to-few)
{
  _id: ObjectId("..."),
  name: "John",
  addresses: [
    { type: "home", street: "123 Main St" },
    { type: "work", street: "456 Office Blvd" }
  ]
}

// Referenced (for one-to-many)
{
  _id: ObjectId("..."),
  name: "John",
  orderIds: [ObjectId("..."), ObjectId("...")]
}

PK              | SK                | Attributes
----------------|-------------------|------------------
USER#123        | METADATA          | name, email, ...
USER#123        | ORDER#001         | total, status, ...
USER#123        | ORDER#002         | total, status, ...
ORDER#001       | METADATA          | userId, total, ...
ORDER#001       | ITEM#1            | productId, qty, ...

Level	Description	Use Case
1NF	Atomic values, no repeating groups	Base requirement
2NF	No partial dependencies	Most applications
3NF	No transitive dependencies	OLTP systems
Denormalized	Redundant data for reads	Read-heavy, analytics

Level	Description	Use Case
1NF	Atomic values, no repeating groups	Base requirement
2NF	No partial dependencies	Most applications
3NF	No transitive dependencies	OLTP systems
Denormalized	Redundant data for reads	Read-heavy, analytics

Type	Use Case	Example
B-tree	Range, equality	Most columns
Hash	Equality only	Exact matches
GIN	Arrays, JSON, full-text	JSONB, text search
GiST	Geometric, range types	PostGIS, IP ranges

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Serializable	No	No	No

database-patterns

Installationsoptionen

Quelldateien prüfen