Rafał Tyński
6df1027699
Add an idempotent SurrealDB 3.x schema for all domain entities with indexes (HNSW vector and full-text), record access and permissions, plus a taxonomy seed with the 8 social challenges, grouped tags and sample locations. Verified on SurrealDB 3.2.4: migrations apply cleanly, re-run is idempotent and CRUD works.
340 lines
20 KiB
Plaintext
340 lines
20 KiB
Plaintext
-- TarninaHub schema (SurrealDB 3.x) — migration 0001
|
|
-- Idempotent: uses IF NOT EXISTS, safe to re-run. Backend database is the
|
|
-- source of truth; the frontend (WASM) applies the same subset of definitions.
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Reference / dictionaries
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
DEFINE TABLE IF NOT EXISTS social_challenge SCHEMAFULL
|
|
PERMISSIONS FOR select FULL, FOR create, update, delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS name ON TABLE social_challenge TYPE string ASSERT string::len($value) > 0;
|
|
DEFINE FIELD IF NOT EXISTS description ON TABLE social_challenge TYPE string DEFAULT "";
|
|
DEFINE FIELD IF NOT EXISTS order ON TABLE social_challenge TYPE int DEFAULT 0;
|
|
DEFINE INDEX IF NOT EXISTS social_challenge_name ON TABLE social_challenge FIELDS name UNIQUE;
|
|
|
|
DEFINE TABLE IF NOT EXISTS tag SCHEMAFULL
|
|
PERMISSIONS FOR select FULL, FOR create, update, delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS name ON TABLE tag TYPE string ASSERT string::len($value) > 0;
|
|
DEFINE FIELD IF NOT EXISTS group ON TABLE tag TYPE string
|
|
ASSERT $value IN ['audience', 'topic', 'stage', 'sector', 'cross'];
|
|
DEFINE INDEX IF NOT EXISTS tag_name_group ON TABLE tag FIELDS name, group UNIQUE;
|
|
|
|
DEFINE TABLE IF NOT EXISTS location SCHEMAFULL
|
|
PERMISSIONS FOR select FULL, FOR create, update, delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS name ON TABLE location TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS kind ON TABLE location TYPE string
|
|
ASSERT $value IN ['gmina', 'powiat', 'wojewodztwo'];
|
|
DEFINE FIELD IF NOT EXISTS teryt ON TABLE location TYPE option<string>;
|
|
DEFINE FIELD IF NOT EXISTS geometry ON TABLE location TYPE option<geometry>;
|
|
DEFINE FIELD IF NOT EXISTS parent ON TABLE location TYPE option<record<location>>;
|
|
DEFINE INDEX IF NOT EXISTS location_teryt ON TABLE location FIELDS teryt UNIQUE;
|
|
|
|
DEFINE TABLE IF NOT EXISTS organization SCHEMAFULL
|
|
PERMISSIONS FOR select FULL,
|
|
FOR create, update WHERE $auth.role IN ['operator', 'admin'],
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS name ON TABLE organization TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS type ON TABLE organization TYPE string
|
|
ASSERT $value IN ['ngo', 'jst', 'institution', 'company'];
|
|
DEFINE FIELD IF NOT EXISTS location ON TABLE organization TYPE option<record<location>>;
|
|
DEFINE FIELD IF NOT EXISTS contact ON TABLE organization TYPE option<object>;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Identity (shared across departments)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
DEFINE TABLE IF NOT EXISTS user SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE id = $auth.id OR $auth.role IN ['operator', 'manager', 'admin'],
|
|
FOR create, update WHERE id = $auth.id,
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS phone ON TABLE user TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS email ON TABLE user TYPE option<string>;
|
|
DEFINE FIELD IF NOT EXISTS role ON TABLE user TYPE string
|
|
ASSERT $value IN ['user', 'jst', 'operator', 'manager', 'admin'];
|
|
DEFINE FIELD IF NOT EXISTS organization ON TABLE user TYPE option<record<organization>>;
|
|
DEFINE FIELD IF NOT EXISTS password ON TABLE user TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS updated_at ON TABLE user TYPE option<datetime> DEFAULT time::now();
|
|
DEFINE FIELD IF NOT EXISTS version ON TABLE user TYPE int DEFAULT 1;
|
|
DEFINE FIELD IF NOT EXISTS deleted_at ON TABLE user TYPE option<datetime>;
|
|
DEFINE INDEX IF NOT EXISTS user_phone ON TABLE user FIELDS phone UNIQUE;
|
|
DEFINE INDEX IF NOT EXISTS user_email ON TABLE user FIELDS email UNIQUE;
|
|
|
|
-- Record access: users sign up / sign in and receive a JWT.
|
|
DEFINE ACCESS IF NOT EXISTS account ON DATABASE TYPE RECORD
|
|
SIGNUP ( CREATE user SET phone = $phone, email = $email, password = crypto::argon2::generate($password), role = 'user' )
|
|
SIGNIN ( SELECT * FROM user WHERE phone = $phone AND crypto::argon2::compare(password, $password) )
|
|
DURATION FOR TOKEN 15m, FOR SESSION 12h;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Matchmaking (F-1): problems, submissions, innovations
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
DEFINE TABLE IF NOT EXISTS problem SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE author = $auth.id OR visibility != 'private' OR $auth.role IN ['operator', 'manager', 'admin'],
|
|
FOR create, update WHERE author = $auth.id OR $auth.role IN ['operator', 'admin'],
|
|
FOR delete WHERE author = $auth.id OR $auth.role = 'admin';
|
|
DEFINE FIELD IF NOT EXISTS title ON TABLE problem TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS description ON TABLE problem TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS challenge ON TABLE problem TYPE record<social_challenge>;
|
|
DEFINE FIELD IF NOT EXISTS location ON TABLE problem TYPE record<location>;
|
|
DEFINE FIELD IF NOT EXISTS author ON TABLE problem TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS visibility ON TABLE problem TYPE string
|
|
ASSERT $value IN ['private', 'shared', 'public'] DEFAULT 'private';
|
|
DEFINE FIELD IF NOT EXISTS tags ON TABLE problem TYPE array<record<tag>> DEFAULT [];
|
|
DEFINE FIELD IF NOT EXISTS updated_at ON TABLE problem TYPE option<datetime> DEFAULT time::now();
|
|
DEFINE FIELD IF NOT EXISTS version ON TABLE problem TYPE int DEFAULT 1;
|
|
DEFINE FIELD IF NOT EXISTS deleted_at ON TABLE problem TYPE option<datetime>;
|
|
DEFINE INDEX IF NOT EXISTS problem_challenge ON TABLE problem FIELDS challenge;
|
|
DEFINE INDEX IF NOT EXISTS problem_location ON TABLE problem FIELDS location;
|
|
|
|
DEFINE TABLE IF NOT EXISTS submission SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE submitted_by = $auth.id OR $auth.role IN ['operator', 'admin'],
|
|
FOR create WHERE submitted_by = $auth.id,
|
|
FOR update WHERE $auth.role IN ['operator', 'admin'],
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS problem ON TABLE submission TYPE record<problem>;
|
|
DEFINE FIELD IF NOT EXISTS submitted_by ON TABLE submission TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS status ON TABLE submission TYPE string
|
|
ASSERT $value IN ['new', 'triaged', 'matched', 'in_progress', 'closed', 'rejected'] DEFAULT 'new';
|
|
DEFINE FIELD IF NOT EXISTS keywords ON TABLE submission TYPE array<string> DEFAULT [];
|
|
DEFINE FIELD IF NOT EXISTS created_at ON TABLE submission TYPE datetime DEFAULT time::now();
|
|
DEFINE FIELD IF NOT EXISTS updated_at ON TABLE submission TYPE option<datetime> DEFAULT time::now();
|
|
DEFINE FIELD IF NOT EXISTS version ON TABLE submission TYPE int DEFAULT 1;
|
|
DEFINE FIELD IF NOT EXISTS deleted_at ON TABLE submission TYPE option<datetime>;
|
|
DEFINE INDEX IF NOT EXISTS submission_status ON TABLE submission FIELDS status;
|
|
DEFINE INDEX IF NOT EXISTS submission_problem ON TABLE submission FIELDS problem;
|
|
|
|
DEFINE TABLE IF NOT EXISTS innovation SCHEMAFULL
|
|
PERMISSIONS FOR select FULL,
|
|
FOR create, update WHERE $auth.role IN ['operator', 'manager', 'admin'],
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS title ON TABLE innovation TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS summary ON TABLE innovation TYPE string DEFAULT "";
|
|
DEFINE FIELD IF NOT EXISTS description ON TABLE innovation TYPE string DEFAULT "";
|
|
DEFINE FIELD IF NOT EXISTS challenge ON TABLE innovation TYPE record<social_challenge>;
|
|
DEFINE FIELD IF NOT EXISTS stage ON TABLE innovation TYPE string
|
|
ASSERT $value IN ['idea', 'pilot', 'scaling', 'service'] DEFAULT 'idea';
|
|
DEFINE FIELD IF NOT EXISTS cost ON TABLE innovation TYPE option<string>;
|
|
DEFINE FIELD IF NOT EXISTS contact ON TABLE innovation TYPE option<object>;
|
|
DEFINE FIELD IF NOT EXISTS media ON TABLE innovation TYPE array<object> DEFAULT [];
|
|
DEFINE FIELD IF NOT EXISTS embedding ON TABLE innovation TYPE option<array<float>>;
|
|
DEFINE FIELD IF NOT EXISTS published ON TABLE innovation TYPE bool DEFAULT false;
|
|
DEFINE FIELD IF NOT EXISTS updated_at ON TABLE innovation TYPE option<datetime> DEFAULT time::now();
|
|
DEFINE FIELD IF NOT EXISTS version ON TABLE innovation TYPE int DEFAULT 1;
|
|
DEFINE INDEX IF NOT EXISTS innovation_challenge ON TABLE innovation FIELDS challenge;
|
|
DEFINE INDEX IF NOT EXISTS innovation_embedding ON TABLE innovation FIELDS embedding
|
|
HNSW DIMENSION 1536 DIST COSINE TYPE F32;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Idea creator (F-4)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
DEFINE TABLE IF NOT EXISTS idea SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE author = $auth.id OR $auth.role IN ['manager', 'operator', 'admin'],
|
|
FOR create, update WHERE author = $auth.id,
|
|
FOR delete WHERE author = $auth.id;
|
|
DEFINE FIELD IF NOT EXISTS title ON TABLE idea TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS description ON TABLE idea TYPE string DEFAULT "";
|
|
DEFINE FIELD IF NOT EXISTS author ON TABLE idea TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS status ON TABLE idea TYPE string
|
|
ASSERT $value IN ['draft', 'submitted', 'in_review', 'accepted', 'rejected'] DEFAULT 'draft';
|
|
DEFINE FIELD IF NOT EXISTS updated_at ON TABLE idea TYPE option<datetime> DEFAULT time::now();
|
|
DEFINE FIELD IF NOT EXISTS version ON TABLE idea TYPE int DEFAULT 1;
|
|
DEFINE FIELD IF NOT EXISTS deleted_at ON TABLE idea TYPE option<datetime>;
|
|
DEFINE INDEX IF NOT EXISTS idea_author ON TABLE idea FIELDS author;
|
|
DEFINE INDEX IF NOT EXISTS idea_status ON TABLE idea FIELDS status;
|
|
|
|
DEFINE TABLE IF NOT EXISTS idea_card SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE idea.author = $auth.id OR $auth.role IN ['manager', 'operator', 'admin'],
|
|
FOR create, update WHERE idea.author = $auth.id,
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS idea ON TABLE idea_card TYPE record<idea>;
|
|
DEFINE FIELD IF NOT EXISTS essence ON TABLE idea_card TYPE string DEFAULT "";
|
|
DEFINE FIELD IF NOT EXISTS audience ON TABLE idea_card TYPE string DEFAULT "";
|
|
DEFINE FIELD IF NOT EXISTS stage ON TABLE idea_card TYPE string
|
|
ASSERT $value IN ['idea', 'pilot', 'scaling', 'service'] DEFAULT 'idea';
|
|
DEFINE INDEX IF NOT EXISTS idea_card_idea ON TABLE idea_card FIELDS idea UNIQUE;
|
|
|
|
DEFINE TABLE IF NOT EXISTS grant_call SCHEMAFULL
|
|
PERMISSIONS FOR select FULL,
|
|
FOR create, update, delete WHERE $auth.role IN ['operator', 'admin'];
|
|
DEFINE FIELD IF NOT EXISTS name ON TABLE grant_call TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS opens_at ON TABLE grant_call TYPE datetime;
|
|
DEFINE FIELD IF NOT EXISTS closes_at ON TABLE grant_call TYPE datetime;
|
|
DEFINE FIELD IF NOT EXISTS status ON TABLE grant_call TYPE string
|
|
ASSERT $value IN ['open', 'closed', 'planned'] DEFAULT 'planned';
|
|
DEFINE FIELD IF NOT EXISTS external_id ON TABLE grant_call TYPE option<string>;
|
|
DEFINE INDEX IF NOT EXISTS grant_call_status ON TABLE grant_call FIELDS status;
|
|
|
|
DEFINE TABLE IF NOT EXISTS application SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE idea.author = $auth.id OR $auth.role IN ['operator', 'admin'],
|
|
FOR create WHERE idea.author = $auth.id,
|
|
FOR update WHERE idea.author = $auth.id OR $auth.role IN ['operator', 'admin'],
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS grant_call ON TABLE application TYPE record<grant_call>;
|
|
DEFINE FIELD IF NOT EXISTS idea ON TABLE application TYPE record<idea>;
|
|
DEFINE FIELD IF NOT EXISTS content ON TABLE application TYPE object DEFAULT {};
|
|
DEFINE FIELD IF NOT EXISTS status ON TABLE application TYPE string
|
|
ASSERT $value IN ['draft', 'submitted', 'accepted', 'rejected'] DEFAULT 'draft';
|
|
DEFINE FIELD IF NOT EXISTS created_at ON TABLE application TYPE datetime DEFAULT time::now();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Tester (F-5)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
DEFINE TABLE IF NOT EXISTS test_feedback SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE author = $auth.id OR $auth.role IN ['manager', 'operator', 'admin'],
|
|
FOR create WHERE author = $auth.id,
|
|
FOR update, delete WHERE author = $auth.id;
|
|
DEFINE FIELD IF NOT EXISTS innovation ON TABLE test_feedback TYPE record<innovation>;
|
|
DEFINE FIELD IF NOT EXISTS author ON TABLE test_feedback TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS rating ON TABLE test_feedback TYPE int ASSERT $value >= 1 AND $value <= 5;
|
|
DEFINE FIELD IF NOT EXISTS comment ON TABLE test_feedback TYPE string DEFAULT "";
|
|
DEFINE FIELD IF NOT EXISTS improvements ON TABLE test_feedback TYPE string DEFAULT "";
|
|
DEFINE FIELD IF NOT EXISTS created_at ON TABLE test_feedback TYPE datetime DEFAULT time::now();
|
|
DEFINE INDEX IF NOT EXISTS test_feedback_innovation ON TABLE test_feedback FIELDS innovation;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Communication (F-6)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
DEFINE TABLE IF NOT EXISTS partnership SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE initiator = $auth.id OR $auth.role IN ['operator', 'admin'],
|
|
FOR create, update WHERE initiator = $auth.id OR $auth.role IN ['operator', 'admin'],
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS initiator ON TABLE partnership TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS subject ON TABLE partnership TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS partners ON TABLE partnership TYPE array<record<organization>> DEFAULT [];
|
|
DEFINE FIELD IF NOT EXISTS status ON TABLE partnership TYPE string
|
|
ASSERT $value IN ['proposed', 'active', 'closed'] DEFAULT 'proposed';
|
|
|
|
DEFINE TABLE IF NOT EXISTS thread SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE $auth.id IN participants OR $auth.role IN ['operator', 'admin'],
|
|
FOR create WHERE $auth.id IN participants,
|
|
FOR update WHERE $auth.id IN participants,
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS subject ON TABLE thread TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS participants ON TABLE thread TYPE array<record<user>> DEFAULT [];
|
|
DEFINE FIELD IF NOT EXISTS created_at ON TABLE thread TYPE datetime DEFAULT time::now();
|
|
|
|
DEFINE TABLE IF NOT EXISTS message SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE thread.participants CONTAINS $auth.id OR $auth.role IN ['operator', 'admin'],
|
|
FOR create WHERE thread.participants CONTAINS $auth.id,
|
|
FOR update, delete WHERE author = $auth.id;
|
|
DEFINE FIELD IF NOT EXISTS thread ON TABLE message TYPE record<thread>;
|
|
DEFINE FIELD IF NOT EXISTS author ON TABLE message TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS body ON TABLE message TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS created_at ON TABLE message TYPE datetime DEFAULT time::now();
|
|
DEFINE INDEX IF NOT EXISTS message_thread ON TABLE message FIELDS thread;
|
|
|
|
DEFINE TABLE IF NOT EXISTS notification SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE recipient = $auth.id,
|
|
FOR create WHERE $auth.role IN ['operator', 'admin'],
|
|
FOR update WHERE recipient = $auth.id,
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS recipient ON TABLE notification TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS type ON TABLE notification TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS payload ON TABLE notification TYPE object DEFAULT {};
|
|
DEFINE FIELD IF NOT EXISTS read_at ON TABLE notification TYPE option<datetime>;
|
|
DEFINE FIELD IF NOT EXISTS created_at ON TABLE notification TYPE datetime DEFAULT time::now();
|
|
DEFINE INDEX IF NOT EXISTS notification_recipient ON TABLE notification FIELDS recipient, read_at;
|
|
|
|
DEFINE TABLE IF NOT EXISTS comment SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select FULL,
|
|
FOR create WHERE author = $auth.id,
|
|
FOR update, delete WHERE author = $auth.id;
|
|
DEFINE FIELD IF NOT EXISTS target ON TABLE comment TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS author ON TABLE comment TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS body ON TABLE comment TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS created_at ON TABLE comment TYPE datetime DEFAULT time::now();
|
|
DEFINE INDEX IF NOT EXISTS comment_target ON TABLE comment FIELDS target;
|
|
|
|
DEFINE TABLE IF NOT EXISTS reaction SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select FULL,
|
|
FOR create, delete WHERE author = $auth.id,
|
|
FOR update NONE;
|
|
DEFINE FIELD IF NOT EXISTS target ON TABLE reaction TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS author ON TABLE reaction TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS kind ON TABLE reaction TYPE string;
|
|
DEFINE INDEX IF NOT EXISTS reaction_target ON TABLE reaction FIELDS target;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Knowledge base (F-3)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
DEFINE TABLE IF NOT EXISTS knowledge_item SCHEMAFULL
|
|
PERMISSIONS FOR select FULL,
|
|
FOR create, update WHERE $auth.role IN ['operator', 'admin'],
|
|
FOR delete WHERE $auth.role = 'admin';
|
|
DEFINE FIELD IF NOT EXISTS title ON TABLE knowledge_item TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS kind ON TABLE knowledge_item TYPE string
|
|
ASSERT $value IN ['report', 'map', 'education', 'dataset', 'canvas', 'media'];
|
|
DEFINE FIELD IF NOT EXISTS challenge ON TABLE knowledge_item TYPE option<record<social_challenge>>;
|
|
DEFINE FIELD IF NOT EXISTS source_url ON TABLE knowledge_item TYPE option<string>;
|
|
DEFINE FIELD IF NOT EXISTS attachments ON TABLE knowledge_item TYPE array<object> DEFAULT [];
|
|
DEFINE FIELD IF NOT EXISTS published ON TABLE knowledge_item TYPE bool DEFAULT false;
|
|
DEFINE FIELD IF NOT EXISTS updated_at ON TABLE knowledge_item TYPE option<datetime> DEFAULT time::now();
|
|
|
|
DEFINE ANALYZER IF NOT EXISTS ascii TOKENIZERS class FILTERS lowercase, ascii;
|
|
DEFINE INDEX IF NOT EXISTS knowledge_item_ft ON TABLE knowledge_item FIELDS title
|
|
FULLTEXT ANALYZER ascii BM25;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Documents, consent, audit (privacy)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
DEFINE TABLE IF NOT EXISTS document SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE owner = $auth.id OR $auth.role IN ['operator', 'admin'],
|
|
FOR create WHERE owner = $auth.id,
|
|
FOR update WHERE owner = $auth.id,
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS title ON TABLE document TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS doc_type ON TABLE document TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS processing_purpose ON TABLE document TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS scope ON TABLE document TYPE string
|
|
ASSERT $value IN ['local', 'partial', 'full', 'anonymized'] DEFAULT 'local';
|
|
DEFINE FIELD IF NOT EXISTS owner ON TABLE document TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS archived ON TABLE document TYPE bool DEFAULT false;
|
|
DEFINE FIELD IF NOT EXISTS related ON TABLE document TYPE option<string>;
|
|
DEFINE FIELD IF NOT EXISTS updated_at ON TABLE document TYPE option<datetime> DEFAULT time::now();
|
|
DEFINE FIELD IF NOT EXISTS version ON TABLE document TYPE int DEFAULT 1;
|
|
DEFINE FIELD IF NOT EXISTS deleted_at ON TABLE document TYPE option<datetime>;
|
|
DEFINE INDEX IF NOT EXISTS document_owner ON TABLE document FIELDS owner;
|
|
|
|
DEFINE TABLE IF NOT EXISTS consent SCHEMAFULL
|
|
PERMISSIONS
|
|
FOR select WHERE user = $auth.id OR $auth.role IN ['operator', 'admin'],
|
|
FOR create WHERE user = $auth.id,
|
|
FOR update WHERE user = $auth.id,
|
|
FOR delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS user ON TABLE consent TYPE record<user>;
|
|
DEFINE FIELD IF NOT EXISTS scope ON TABLE consent TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS granted_at ON TABLE consent TYPE datetime DEFAULT time::now();
|
|
DEFINE FIELD IF NOT EXISTS expires_at ON TABLE consent TYPE option<datetime>;
|
|
DEFINE FIELD IF NOT EXISTS revoked_at ON TABLE consent TYPE option<datetime>;
|
|
DEFINE INDEX IF NOT EXISTS consent_user ON TABLE consent FIELDS user;
|
|
|
|
DEFINE TABLE IF NOT EXISTS audit_log SCHEMAFULL
|
|
PERMISSIONS FOR select WHERE $auth.role = 'admin', FOR create, update, delete NONE;
|
|
DEFINE FIELD IF NOT EXISTS actor ON TABLE audit_log TYPE option<record<user>>;
|
|
DEFINE FIELD IF NOT EXISTS action ON TABLE audit_log TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS target ON TABLE audit_log TYPE string;
|
|
DEFINE FIELD IF NOT EXISTS context ON TABLE audit_log TYPE object DEFAULT {};
|
|
DEFINE FIELD IF NOT EXISTS at ON TABLE audit_log TYPE datetime DEFAULT time::now();
|
|
DEFINE INDEX IF NOT EXISTS audit_log_at ON TABLE audit_log FIELDS at;
|