Docs/Development

Database

PostgreSQL-based database system with entities, relationships, and migrations.

Database Management

Overview

AlgorithmShift provides a powerful database management system built on PostgreSQL. Each workspace gets an isolated schema with full support for entities, relationships, indexing, and migrations.

Entity Definition

Field Types

Type	PostgreSQL	Description	Example
`uuid`	UUID	Unique identifier	`550e8400-...`
`string`	VARCHAR(255)	Short text	`"Product Name"`
`text`	TEXT	Long text	Paragraphs
`integer`	INTEGER	Whole numbers	`42`
`bigint`	BIGINT	Large integers	`9007199254740991`
`decimal`	DECIMAL(10,2)	Precise decimals	`99.99`
`float`	DOUBLE PRECISION	Floating point	`3.14159`
`boolean`	BOOLEAN	True/false	`true`
`date`	DATE	Date only	`2024-01-15`
`time`	TIME	Time only	`14:30:00`
`timestamp`	TIMESTAMPTZ	Date and time	`2024-01-15T14:30:00Z`
`json`	JSONB	Structured data	`{"key": "value"}`
`array`	ARRAY	List of values	`["a", "b", "c"]`
`enum`	ENUM	Predefined values	`'active'`

Relationships

One-to-Many

┌──────────┐       ┌──────────┐
│  Users   │ 1───* │  Orders  │
└──────────┘       └──────────┘

typescript

// In orders entity
{
  name: 'user_id',
  type: 'uuid',
  foreignKey: {
    entity: 'users',
    field: 'id',
    onDelete: 'set_null'  // Keep orders if user deleted
  }
}

Many-to-Many

┌──────────┐       ┌─────────────────┐       ┌──────────┐
│ Products │ *───* │ product_tags    │ *───* │   Tags   │
└──────────┘       │ (junction table)│       └──────────┘
                   └─────────────────┘

Indexes

Index Types

Type	Use Case	Example
Standard	Equality queries	`WHERE status = 'active'`
Unique	Enforce uniqueness	`email` column
Composite	Multi-column queries	`WHERE user_id = ? AND status = ?`
Partial	Conditional index	`WHERE deleted_at IS NULL`
GIN	JSON/Array search	`WHERE metadata @> '{}'`
Full-text	Text search	`WHERE name ILIKE '%search%'`

Soft Delete

Enable soft delete to retain records:

typescript

{
  name: 'products',
  enableSoftDelete: true,
  // Adds: deleted_at TIMESTAMP, deleted_by UUID
}

Behavior

Operation	With Soft Delete
`DELETE`	Sets `deleted_at = now()`
`SELECT`	Excludes `deleted_at IS NOT NULL`
`findWithDeleted()`	Includes soft-deleted records
`restore()`	Sets `deleted_at = NULL`
`forceDelete()`	Permanently removes record

Audit Logging

Track all changes to records:

typescript

{
  name: 'products',
  enableAuditLog: true
}

Audit Log Table

sql

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY,
  entity_name VARCHAR(100),
  record_id UUID,
  action VARCHAR(20),      -- create, update, delete
  old_values JSONB,
  new_values JSONB,
  changed_fields TEXT[],
  user_id UUID,
  ip_address INET,
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Migrations

Migration Workflow

1. Design Schema (UI)
   └── Visual entity editor

2. Generate Migration
   └── Auto-generate SQL from changes

3. Review Migration
   └── Edit SQL if needed

4. Apply to Development
   └── Test with sample data

5. Promote to Staging
   └── Apply and test integrations

6. Deploy to Production
   └── Backup → Apply → Verify

Backup & Restore

Automated Backups

Environment	Frequency	Retention
Development	Daily	7 days
Staging	Daily	14 days
Production	Hourly	30 days

Manual Backup

bash

# Create backup
algorithmshift db backup --env production

# Download backup
algorithmshift db backup download backup-20240115.sql

# Restore from backup
algorithmshift db restore backup-20240115.sql --env staging