Data Modeling Patterns

-- Normalized base tables
CREATE TABLE users (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT        NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at  TIMESTAMPTZ  -- Soft delete
);

CREATE TABLE organizations (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT        NOT NULL,
  slug        TEXT        NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Normalized join table with payload
CREATE TABLE memberships (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  organization_id UUID        NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  role            TEXT        NOT NULL DEFAULT 'member',
  joined_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(user_id, organization_id)
);

-- Instead of COUNT(*) on every request, maintain a counter column
ALTER TABLE posts ADD COLUMN comments_count INT NOT NULL DEFAULT 0;

-- Trigger to maintain count
CREATE OR REPLACE FUNCTION update_post_comments_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET comments_count = comments_count + 1 WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts SET comments_count = comments_count - 1 WHERE id = OLD.post_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_comments_count
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION update_post_comments_count();

-- When attributes vary by type and are rarely filtered
ALTER TABLE products ADD COLUMN metadata JSONB NOT NULL DEFAULT '{}';

-- Query with GIN index
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- Usage: WHERE metadata @> '{"color": "red"}'
-- Usage: WHERE metadata->>'sku' = 'ABC-123'

-- Each type has its own table; no polymorphism at DB level
CREATE TABLE comment_on_post (
  id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  body    TEXT NOT NULL,
  user_id UUID NOT NULL REFERENCES users(id)
);

CREATE TABLE comment_on_product (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  body       TEXT NOT NULL,
  user_id    UUID NOT NULL REFERENCES users(id)
);

-- commentable pattern
CREATE TABLE comments (
  id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  body             TEXT        NOT NULL,
  user_id          UUID        NOT NULL REFERENCES users(id),
  commentable_type TEXT        NOT NULL,  -- 'post', 'product', 'review'
  commentable_id   UUID        NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_comments_polymorphic ON comments(commentable_type, commentable_id);
-- Note: No FK constraint — must enforce referential integrity in application

CREATE TABLE attachments (
  id         UUID  PRIMARY KEY DEFAULT gen_random_uuid(),
  url        TEXT  NOT NULL,
  -- Exactly one must be non-null (enforce with CHECK)
  post_id    UUID  REFERENCES posts(id),
  comment_id UUID  REFERENCES comments(id),
  CONSTRAINT exactly_one_parent CHECK (
    (post_id IS NOT NULL)::INT + (comment_id IS NOT NULL)::INT = 1
  )
);

-- All queries must filter deleted_at IS NULL
-- Use RLS or default scope in ORM to enforce

-- PostgreSQL Row Level Security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY users_soft_delete ON users
  USING (deleted_at IS NULL);

-- Prisma soft delete middleware
const softDeleteMiddleware: Prisma.Middleware = async (params, next) => {
  if (params.action === 'delete') {
    params.action = 'update';
    params.args.data = { deletedAt: new Date() };
  }
  if (params.action === 'findMany' || params.action === 'findFirst') {
    params.args.where = { ...params.args.where, deletedAt: null };
  }
  return next(params);
};

CREATE TABLE audit_log (
  id          BIGSERIAL   PRIMARY KEY,
  table_name  TEXT        NOT NULL,
  record_id   UUID        NOT NULL,
  operation   TEXT        NOT NULL,  -- INSERT, UPDATE, DELETE
  changed_by  UUID        REFERENCES users(id),
  changed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  old_values  JSONB,
  new_values  JSONB
);

CREATE INDEX idx_audit_log_record ON audit_log(table_name, record_id);
CREATE INDEX idx_audit_log_changed_at ON audit_log(changed_at DESC);

-- Generic audit trigger
CREATE OR REPLACE FUNCTION log_audit_event() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END
  );
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Valid time: when fact was true in the real world
-- Transaction time: when fact was recorded in the database
CREATE TABLE product_prices (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id      UUID        NOT NULL REFERENCES products(id),
  price_cents     INT         NOT NULL,
  valid_from      TIMESTAMPTZ NOT NULL,
  valid_to        TIMESTAMPTZ,  -- NULL means current
  recorded_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  EXCLUDE USING GIST (
    product_id WITH =,
    tstzrange(valid_from, valid_to, '[)') WITH &&
  )  -- No overlapping periods for same product
);