Chore: Create read-only database role for Signal Score

[divideby0]

Summary

Signal Score (#590) needs read access to application data for scoring and analysis. Currently working from a static CSV export, but for ongoing scoring (and eventual production integration), we need a dedicated database role.

Proposed Role

CREATE ROLE signal_score_reader WITH LOGIN PASSWORD '...' NOINHERIT;

-- Read-only access to relevant tables only
GRANT SELECT ON projects TO signal_score_reader;
GRANT SELECT ON chapters TO signal_score_reader;
GRANT SELECT ON comments TO signal_score_reader;

Privacy Considerations

Application data contains PII (names, emails, project descriptions). To minimize exposure:

1. Materialized view for analysis — strips PII, retains only the fields needed for scoring:

   CREATE MATERIALIZED VIEW signal_score_projects AS
   SELECT
     id, title, about_me, about_project, use_for_money,
     chapter_id, funded_on, hidden_at, hidden_reason,
     created_at, extra_answer_1, extra_answer_2, extra_answer_3
   FROM projects;
   -- Excludes: name, email, phone, address, ip_address

2. Row-level security (optional) — scope to specific chapters if needed

3. Connection via read replica preferred if available, to avoid load on primary

Use Cases

• Offline analysis: Pull current month's applications for batch scoring
• Validation: Compare scores against trustee decisions across chapters
• Production integration (future): The Rails app would use normal ActiveRecord access via SignalScorer service object — this DB role is for the development/analysis phase only

What We Need

• A read-only Postgres role (or connection string to a replica)
• Confirmation of which tables/columns are available
• Any firewall/VPN requirements for remote access

Happy to work within whatever access constraints make sense. The role can be as locked down as needed — we only need SELECT on projects, chapters, and optionally comments.

Refs: #590