π End-to-end encrypted cloud for everything.
Dart 2faandroidauthye2eeencryptionend-to-end-encryptionfluttergolanggoogle-photosgoogle-photos-alternativeiosopensourcephotosprivacyprivacy-toolstypescriptzero-knowledge
LOCK TABLE space_friend_requests, space_messages IN SHARE ROW EXCLUSIVE MODE;
CREATE TABLE space_post_likes (
like_id TEXT PRIMARY KEY,
post_id BIGINT NOT NULL REFERENCES space_posts (post_id) ON DELETE CASCADE,
actor_space_id TEXT NOT NULL REFERENCES spaces (space_id) ON DELETE CASCADE,
created_at BIGINT NOT NULL DEFAULT now_utc_micro_seconds(),
UNIQUE (post_id, actor_space_id)
);
CREATE TABLE space_pokes (
poke_id TEXT PRIMARY KEY,
sender_space_id TEXT NOT NULL REFERENCES spaces (space_id) ON DELETE CASCADE,
recipient_space_id TEXT NOT NULL REFERENCES spaces (space_id) ON DELETE CASCADE,
client_request_id TEXT NOT NULL,
created_at BIGINT NOT NULL DEFAULT now_utc_micro_seconds(),
CHECK (sender_space_id <> recipient_space_id),
UNIQUE (sender_space_id, recipient_space_id, client_request_id)
);
CREATE INDEX idx_space_pokes_recipient_created
ON space_pokes (recipient_space_id, created_at DESC);
CREATE TABLE space_notifications (
notification_id TEXT PRIMARY KEY,
recipient_space_id TEXT NOT NULL REFERENCES spaces (space_id) ON DELETE CASCADE,
actor_space_id TEXT NOT NULL REFERENCES spaces (space_id) ON DELETE CASCADE,
kind TEXT NOT NULL,
post_like_id TEXT REFERENCES space_post_likes (like_id) ON DELETE CASCADE,
created_at BIGINT NOT NULL DEFAULT now_utc_micro_seconds(),
read_at BIGINT,
friend_request_id BIGINT REFERENCES space_friend_requests (request_id) ON DELETE CASCADE,
poke_id TEXT REFERENCES space_pokes (poke_id) ON DELETE CASCADE,
CHECK (recipient_space_id <> actor_space_id),
CONSTRAINT chk_space_notifications_target CHECK (
(kind = 'post_like' AND post_like_id IS NOT NULL AND friend_request_id IS NULL AND poke_id IS NULL)
OR (kind = 'friend_request' AND friend_request_id IS NOT NULL AND post_like_id IS NULL AND poke_id IS NULL)
OR (kind = 'friend_accepted' AND post_like_id IS NULL AND friend_request_id IS NULL AND poke_id IS NULL)
OR (kind = 'poke' AND poke_id IS NOT NULL AND post_like_id IS NULL AND friend_request_id IS NULL)
),
UNIQUE (recipient_space_id, kind, post_like_id)
);
CREATE INDEX idx_space_notifications_recipient_created
ON space_notifications (recipient_space_id, created_at DESC, notification_id DESC);
CREATE INDEX idx_space_notifications_unread
ON space_notifications (recipient_space_id)
WHERE read_at IS NULL;
CREATE INDEX idx_space_notifications_post_like
ON space_notifications (post_like_id);
CREATE UNIQUE INDEX idx_space_notifications_friend_request
ON space_notifications (friend_request_id) WHERE friend_request_id IS NOT NULL;
CREATE UNIQUE INDEX idx_space_notifications_poke ON space_notifications (poke_id);
INSERT INTO space_post_likes (like_id, post_id, actor_space_id, created_at)
SELECT m.message_id, m.reply_post_id, m.sender_space_id, m.created_at
FROM space_messages m
JOIN space_posts p ON p.post_id = m.reply_post_id AND p.is_deleted = FALSE
WHERE m.kind = 'post_like';
INSERT INTO space_notifications (
notification_id, recipient_space_id, actor_space_id, kind, post_like_id, created_at, read_at
)
SELECT 'wnot_' || l.like_id, p.space_id, l.actor_space_id, 'post_like', l.like_id, l.created_at,
CASE WHEN marker.read_at >= l.created_at THEN marker.read_at END
FROM space_post_likes l
JOIN space_posts p ON p.post_id = l.post_id
LEFT JOIN space_notification_read_markers marker
ON marker.viewer_space_id = p.space_id AND marker.friend_space_id = l.actor_space_id;
INSERT INTO space_notifications (
notification_id, recipient_space_id, actor_space_id, kind, friend_request_id, created_at
)
SELECT 'wnot_request_' || request_id, target_space_id, requester_space_id, 'friend_request', request_id, created_at
FROM space_friend_requests;
INSERT INTO space_notifications (
notification_id, recipient_space_id, actor_space_id, kind, created_at, read_at
)
SELECT m.message_id, m.recipient_space_id, m.sender_space_id, 'friend_accepted', m.created_at,
CASE WHEN marker.read_at >= m.created_at THEN marker.read_at END
FROM space_messages m
LEFT JOIN space_notification_read_markers marker
ON marker.viewer_space_id = m.recipient_space_id AND marker.friend_space_id = m.sender_space_id
WHERE m.kind = 'friend_added';
DELETE FROM space_messages WHERE kind IN ('post_like', 'friend_added');
ALTER TABLE space_messages DROP CONSTRAINT chk_space_messages_kind;
ALTER TABLE space_messages ADD CONSTRAINT chk_space_messages_kind
CHECK (kind IN ('regular', 'post_reply'));