22 lines
778 B
SQL
22 lines
778 B
SQL
-- Seen tracking: private per-user state
|
|
CREATE TABLE particle_seen (
|
|
particle_id TEXT NOT NULL REFERENCES particles(id) ON DELETE CASCADE,
|
|
email VARCHAR(255) NOT NULL,
|
|
seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (particle_id, email)
|
|
);
|
|
|
|
-- Ack tracking: public, visible to others
|
|
CREATE TABLE particle_acks (
|
|
particle_id TEXT NOT NULL REFERENCES particles(id) ON DELETE CASCADE,
|
|
email VARCHAR(255) NOT NULL,
|
|
acked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (particle_id, email)
|
|
);
|
|
|
|
-- Index for bulk "which particles has this user seen?"
|
|
CREATE INDEX idx_particle_seen_email ON particle_seen(email, particle_id);
|
|
|
|
-- Index for "who acked this particle?"
|
|
CREATE INDEX idx_particle_acks_particle ON particle_acks(particle_id);
|