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

# macOS
brew install postgresql
brew services start postgresql
createdb shield

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

-- 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);

# 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

pg_dump $DATABASE_URL > backup.sql

# Restore
psql $DATABASE_URL < backup.sql

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