58b344aa31
Fixed:
- Price: €800K-€1.5M, Sunseeker added
- Agent 1: Joe Trader persona + actual sale ads research
- Ignored meilisearch binary + data/ (too large for GitHub)
- SESSION_DEBUG_BLOCKERS.md created
Ready for Session 1 launch.
🤖 Generated with Claude Code
48 lines
3.1 KiB
SQL
48 lines
3.1 KiB
SQL
-- Migration 009: Permission Templates and Invitations System
|
|
-- Date: 2025-10-21
|
|
-- Description: Add support for permission templates and email invitations
|
|
|
|
-- Permission Templates Table
|
|
CREATE TABLE IF NOT EXISTS permission_templates (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
description TEXT,
|
|
permission_level TEXT NOT NULL CHECK(permission_level IN ('viewer', 'editor', 'manager', 'admin')),
|
|
default_duration_hours INTEGER, -- NULL means permanent
|
|
is_system BOOLEAN DEFAULT 0, -- System templates cannot be deleted
|
|
metadata TEXT, -- JSON: {icon, color, restrictions, etc}
|
|
created_by TEXT REFERENCES users(id),
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
-- Invitations Table
|
|
CREATE TABLE IF NOT EXISTS invitations (
|
|
id TEXT PRIMARY KEY,
|
|
email TEXT NOT NULL,
|
|
template_id TEXT REFERENCES permission_templates(id),
|
|
entity_id TEXT, -- Which property/vessel
|
|
entity_type TEXT DEFAULT 'document', -- document, vessel, property, etc
|
|
invited_by TEXT REFERENCES users(id) NOT NULL,
|
|
status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'accepted', 'expired', 'cancelled')),
|
|
expires_at INTEGER NOT NULL,
|
|
accepted_at INTEGER,
|
|
metadata TEXT, -- JSON: {message, restrictions}
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_templates_system ON permission_templates(is_system);
|
|
CREATE INDEX idx_templates_level ON permission_templates(permission_level);
|
|
CREATE INDEX idx_invitations_email ON invitations(email);
|
|
CREATE INDEX idx_invitations_status ON invitations(status);
|
|
CREATE INDEX idx_invitations_entity ON invitations(entity_id, entity_type);
|
|
|
|
-- Insert System Templates
|
|
INSERT INTO permission_templates (id, name, description, permission_level, default_duration_hours, is_system, metadata, created_by, created_at, updated_at) VALUES
|
|
('tpl-captain', 'Captain', 'Full vessel management access with emergency override', 'manager', NULL, 1, '{"icon":"⚓","color":"#1e40af","scope":"vessel"}', NULL, strftime('%s', 'now'), strftime('%s', 'now')),
|
|
('tpl-crew-member', 'Crew Member', 'Basic viewer access for crew during shifts', 'viewer', 8, 1, '{"icon":"👷","color":"#059669","scope":"vessel"}', NULL, strftime('%s', 'now'), strftime('%s', 'now')),
|
|
('tpl-maintenance', 'Maintenance Contractor', 'Temporary editor access for maintenance work', 'editor', 168, 1, '{"icon":"🔧","color":"#d97706","scope":"vessel"}', NULL, strftime('%s', 'now'), strftime('%s', 'now')),
|
|
('tpl-inspector', 'Inspector/Auditor', 'Read-only access for compliance inspections', 'viewer', 24, 1, '{"icon":"📋","color":"#7c3aed","scope":"vessel"}', NULL, strftime('%s', 'now'), strftime('%s', 'now')),
|
|
('tpl-property-manager', 'Property Manager', 'Full administrative access to organization', 'admin', NULL, 1, '{"icon":"🏢","color":"#dc2626","scope":"organization"}', NULL, strftime('%s', 'now'), strftime('%s', 'now')),
|
|
('tpl-office-staff', 'Office Staff', 'Viewer access to all documents', 'viewer', NULL, 1, '{"icon":"💼","color":"#64748b","scope":"organization"}', NULL, strftime('%s', 'now'), strftime('%s', 'now'));
|