> ## Documentation Index
> Fetch the complete documentation index at: https://docs.shieldhq.xyz/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Setup

> Configure PostgreSQL for SHIELD

# Database Setup

SHIELD uses PostgreSQL for policy metadata storage.

## Options

### 1. Neon (Recommended)

Serverless PostgreSQL with generous free tier.

**Setup:**

1. Sign up at [neon.tech](https://neon.tech)
2. Create new project
3. Create database
4. Copy connection string

**Connection String:**

```
postgresql://user:pass@host.neon.tech/shield?sslmode=require
```

### 2. Supabase

PostgreSQL + additional features.

**Setup:**

1. Sign up at [supabase.com](https://supabase.com)
2. Create new project
3. Get connection string from Settings → Database

### 3. Local PostgreSQL

For development:

```bash theme={null}
# macOS
brew install postgresql
brew services start postgresql
createdb shield

# Ubuntu
sudo apt install postgresql
sudo -u postgres createdb shield
```

## Schema

Create tables:

```sql theme={null}
-- Policies table
CREATE TABLE policies (
    policy_id VARCHAR(66) PRIMARY KEY,
    cid VARCHAR(64) NOT NULL,
    sender VARCHAR(42) NOT NULL,
    recipient VARCHAR(42) NOT NULL,
    expiry INTEGER NOT NULL,
    max_attempts INTEGER NOT NULL,
    attempts INTEGER DEFAULT 0,
    content_type VARCHAR(10) NOT NULL,
    file_name VARCHAR(255),
    file_size INTEGER,
    valid BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Access logs table
CREATE TABLE access_logs (
    id SERIAL PRIMARY KEY,
    policy_id VARCHAR(66) REFERENCES policies(policy_id),
    tx_hash VARCHAR(66),
    success BOOLEAN NOT NULL,
    timestamp TIMESTAMP DEFAULT NOW(),
    error TEXT
);

-- Rate limit table
CREATE TABLE rate_limits (
    id SERIAL PRIMARY KEY,
    key VARCHAR(255) UNIQUE NOT NULL,
    count INTEGER DEFAULT 0,
    window_start TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_policies_sender ON policies(sender);
CREATE INDEX idx_policies_recipient ON policies(recipient);
CREATE INDEX idx_access_logs_policy ON access_logs(policy_id);
```

## Migration

Use a migration tool:

```bash theme={null}
# Install pg-migrate
npm install -g node-pg-migrate

# Create migration
npx node-pg-migrate create create_policies_table

# Run migrations
DATABASE_URL=... npx node-pg-migrate up
```

## Backup

### Automated (Neon)

Neon provides automatic backups. Configure retention in dashboard.

### Manual

```bash theme={null}
pg_dump $DATABASE_URL > backup.sql

# Restore
psql $DATABASE_URL < backup.sql
```

## Security

* Use SSL/TLS for connections
* Restrict IP allowlist
* Rotate credentials regularly
* Enable query logging
* Never commit credentials

## Connection Pooling

Use PgBouncer for production:

```
postgresql://user:pass@pooler.neon.tech:5432/shield?sslmode=require
```