31 lines
1.2 KiB
SQL
31 lines
1.2 KiB
SQL
CREATE TYPE particle_type AS ENUM (
|
|
'stream', 'folder', 'media', 'file', 'text', 'quest', 'paper', 'think'
|
|
);
|
|
|
|
CREATE TYPE visibility_mode AS ENUM ('network_all', 'custom');
|
|
|
|
CREATE TABLE particles (
|
|
id TEXT PRIMARY KEY,
|
|
type particle_type NOT NULL,
|
|
network_id TEXT NOT NULL REFERENCES networks(id) ON DELETE CASCADE,
|
|
parent_id TEXT REFERENCES particles(id) ON DELETE CASCADE,
|
|
created_by_email VARCHAR(255) NOT NULL,
|
|
visibility visibility_mode NOT NULL DEFAULT 'network_all',
|
|
data JSONB NOT NULL DEFAULT '{}',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE particle_members (
|
|
particle_id TEXT NOT NULL REFERENCES particles(id) ON DELETE CASCADE,
|
|
email VARCHAR(255) NOT NULL,
|
|
added_at TIMESTAMPTZ DEFAULT NOW(),
|
|
PRIMARY KEY (particle_id, email)
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_particles_parent_updated ON particles(parent_id, updated_at DESC);
|
|
CREATE INDEX idx_particles_network_root ON particles(network_id, updated_at DESC) WHERE parent_id IS NULL;
|
|
CREATE INDEX idx_particles_open_streams ON particles(network_id) WHERE type = 'stream' AND data->>'status' = 'open';
|
|
CREATE INDEX idx_particle_members_email ON particle_members(email, particle_id);
|