🐘 Nile Postgres Admin

Tenant Isolation

Nile provides built-in multi-tenancy for isolating data by organization or user.

-- Create tenant-aware table
CREATE TABLE battles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,  -- Nile automatically filters by this
  challenger_id UUID,
  opponent_id UUID,
  status VARCHAR(20),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Enable RLS for tenant isolation
ALTER TABLE battles ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their tenant's data
CREATE POLICY tenant_isolation ON battles
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::UUID);

-- Set tenant context in application
SET app.tenant_id = 'tenant-uuid-here';

-- Now all queries automatically filter by tenant
SELECT * FROM battles;  -- Only returns battles for current tenant
          

Use Cases for WorldBridge One

• Organizations - Separate data for different refugee camps
• Communities - Isolate battle leagues or garden groups
• Events - Separate hackathon or tournament data
• Privacy - Ensure data doesn't leak between tenants

Use Cases

Implementation Example

-- 1. Create embeddings table for battle submissions
CREATE TABLE battle_submission_embeddings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  submission_id UUID REFERENCES battle_submissions(id),
  bars_text TEXT,
  embedding vector(1536),
  created_at TIMESTAMP DEFAULT NOW()
);

-- 2. Create index for fast similarity search
CREATE INDEX battle_embedding_idx
  ON battle_submission_embeddings
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

-- 3. Store embedding (generated via OpenAI API)
INSERT INTO battle_submission_embeddings (submission_id, bars_text, embedding)
VALUES (
  'submission-uuid',
  'Your bars here...',
  '[0.123, -0.456, ...]'::vector  -- From OpenAI embeddings API
);

-- 4. Find similar bars
SELECT
  s.bars_text,
  e.embedding <=> $1::vector AS similarity_score
FROM battle_submission_embeddings e
JOIN battle_submissions s ON e.submission_id = s.id
ORDER BY similarity_score
LIMIT 10;

Job Ideas for WorldBridge One

SELECT cron.schedule(
  'weekly-airdrops',
  '0 0 * * 1',
  $$SELECT activate_weekly_airdrops();$$
);

SELECT cron.schedule(
  'battle-timeouts',
  '0 * * * *',
  $$SELECT check_battle_timeouts();$$
);

SELECT cron.schedule(
  'daily-impact-calc',
  '0 1 * * *',
  $$SELECT calculate_daily_impact();$$
);

SELECT cron.schedule(
  'notification-cleanup',
  '0 2 * * 0',
  $$DELETE FROM notifications
     WHERE is_read = true
     AND created_at < NOW() - INTERVAL '30 days';$$
);

Manage Jobs

-- List all scheduled jobs
SELECT * FROM cron.job;

-- Unschedule a job
SELECT cron.unschedule('weekly-airdrops');

-- View job run history
SELECT * FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;
        

Use Cases

• Garden Locations - Store and query community garden coordinates
• Event Venues - Find nearby events based on location
• Impact Mapping - Visualize Kakuma camp projects on a map
• Resource Distribution - Optimize delivery routes

Implementation

-- Add location column to gardens
ALTER TABLE gardens
ADD COLUMN location GEOGRAPHY(POINT, 4326);

-- Create spatial index
CREATE INDEX gardens_location_idx
  ON gardens
  USING GIST (location);

-- Insert garden with coordinates (Kakuma Camp)
UPDATE gardens
SET location = ST_SetSRID(ST_MakePoint(34.813, 3.121), 4326)
WHERE name = 'Kakuma Victory Garden';

-- Find gardens within 5km of a point
SELECT
  name,
  ST_Distance(location, ST_SetSRID(ST_MakePoint(34.8, 3.1), 4326)) / 1000 AS distance_km
FROM gardens
WHERE ST_DWithin(
  location,
  ST_SetSRID(ST_MakePoint(34.8, 3.1), 4326),
  5000  -- 5km in meters
)
ORDER BY distance_km;

-- Find nearest garden to a location
SELECT name, location
FROM gardens
ORDER BY location <-> ST_SetSRID(ST_MakePoint(34.813, 3.121), 4326)
LIMIT 1;