Data Model

The portal schema is defined in supabase/migrations/. RLS is enabled across contributor, document, session, compensation, payout, receipt, cap, exception, and audit tables.

Migration Order

20260430233000_initial_portal_schema.sql
20260501133000_add_onboarding_document_acceptance_evidence.sql
20260501193000_add_validation_rewards_payout_flow.sql
20260501194500_add_playroll_time_milestones.sql
20260501200000_add_payout_receiving_methods.sql
20260501201000_lock_payout_receiving_method_confirmation.sql
20260501204000_validate_payout_receiving_iban.sql
20260511161000_add_contributor_app_links.sql
… (full chain in supabase/migrations/: security hardening 2026-05-16, program
phases 2026-05-25, beneficiary details 2026-07-01)
20260701090000_add_payout_beneficiary_details.sql

Shortcuts in package.json:

npm run deploy:schema
npm run deploy:schema:validation-payout
npm run deploy:schema:time-milestones
npm run deploy:schema:payout-receiving
npm run deploy:schema:payout-iban-validation
npm run deploy:schema:payout-beneficiary

Browser-Written Tables

public.applications

Written by insertVerifiedApplication.

Core fields:

auth_user_id
legal_name
email
email_verified_at
city
university
discord_handle
weekly_availability
notes
privacy_notice_version
referral_code
source
status
metadata.city
metadata.university

Security boundary:

• authenticated insert only
• auth_user_id = auth.uid()
• submitted email must match verified Supabase email
• email_verified_at must be present
• contributor can read only their own rows

public.onboarding_submissions

Written by insertOnboardingSubmission.

Core fields:

auth_user_id
application_id
invite_code
legal_name
email
city
contact_handle
tax_status
accepted_documents
document_acceptance_evidence
metadata.city
metadata.sensitive_data_collection
metadata.payout
metadata.recorder_access

The table still has compatibility columns for date_of_birth, residential_address, codice_fiscale, account_holder_name, and iban. The current visible portal does not collect those values before recorder access.

public.payout_receiving_methods

Written by savePayoutReceivingMethod (INSERT, plus column-level UPDATE on account_holder_name/manual_review_note/metadata). The status, email_confirmation_status, email_confirmed_at, and confirmation_token columns are writable only via the SECURITY DEFINER RPCs mint_payout_confirmation_token(p_method_id) and confirm_payout_receiving_method_with_token(p_token) (audit 2026-05-16, EC-231 M1+M2).

Since 2026-07-02 the form also collects the beneficiary details the payment rail needs to create a recipient (legal name split + full address) together with the IBAN. Beneficiary columns on existing rows — including status = confirmed — are writable only via the owner-scoped SECURITY DEFINER RPC complete_payout_beneficiary_details(...): a direct UPDATE cannot work because the "update own active" policy deliberately excludes confirmed rows. The RPC never touches status, so completing the address does not re-trigger email confirmation (backfill path). The deployed frontend is pre-migration-tolerant: on a 42703 missing-column error it falls back to the legacy column list and renders the pre-beneficiary form.

Core fields:

auth_user_id
verified_email
method_type
account_holder_name
beneficiary_first_name
beneficiary_last_name
beneficiary_country          # CHECK ^[A-Z]{2}$
beneficiary_address
beneficiary_address_2
beneficiary_city
beneficiary_state            # required for IT (provincia sigla) and US
beneficiary_postal_code
bic                          # optional, schema-ready, not collected by the form
bank_name                    # optional, schema-ready, not collected by the form
iban
iban_last4
manual_review_note
email_confirmation_status
email_confirmation_sent_at
email_confirmed_at
status
confirmation_token
confirmation_token_expires_at
metadata

public.ops_payout_export_log and ops_export_altitude_recipients()

Ops-only recipient export replacing raw-SQL IBAN access. ops_export_altitude_recipients(p_note) is a service-role-only SECURITY DEFINER function returning one row per confirmed SEPA method (latest per email — production duplicates are deduped) with the full IBAN, beneficiary address, a contributor join (auth_user_id first, verified-email fallback), missing_fields[] and an altitude_ready flag. Every call writes an ops_payout_export_log row (actor, actor email, row count, note). Invoked via npm run ops:export-recipients in the portal repo (--csv for spreadsheets, --note to tag the audit row).

Allowed methods:

sepa_iban
manual_review

Operational statuses:

pending_email_confirmation
confirmed
manual_review
disabled

Database functions normalize and validate supported SEPA IBANs:

public.normalize_iban(value text)
public.is_supported_sepa_iban(value text)
public.normalize_payout_receiving_method_iban()

The insert policy requires email_confirmation_status = pending and restricts inserted statuses:

method_type = sepa_iban -> status = pending_email_confirmation
method_type = manual_review -> status = manual_review

Updates are allowed only while the user's active row is still pending_email_confirmation or manual_review.

Reward And Payout Tables

The schema includes the downstream reward and payment model, but the current portal does not fully operate it from the browser.

Validation source of truth:

public.session_validation_results

Important rule:

public.session_validation_results.status = validated

Validation must not directly compute payable EUR. payable_eur is deprecated and should remain 0.

Time-credit source of truth:

public.playroll_time_ledger

Milestone source of truth:

public.reward_milestones
public.contributor_milestone_unlocks

Value source of truth:

public.compensation_items

Money/reward balance view:

public.contributor_reward_balances

Receiving-method export view:

public.confirmed_payout_receiving_methods

Main Views

Contributor-visible balance views:

public.contributor_playroll_time_balances
public.contributor_milestone_progress
public.contributor_reward_balances

These views are created with security_invoker = true and granted to authenticated users. RLS still controls row visibility through the underlying contributor/user relationship.

Playroll App Identity Link

The portal source table for the Playroll app/contributor bridge is:

public.contributor_app_links

Important fields:

id
contributor_id
portal_auth_user_id
app_project
app_user_id
steam_id
steam_username
status
intent_issued_at
intent_expires_at
intent_nonce
pending_expires_at
linked_at
revoked_at

Statuses:

pending_activation
active
revoked

Constraints and indexes enforce:

one active link per app_project + app_user_id
one active link per app_project + contributor_id
one pending link per app_project + app_user_id + portal_auth_user_id
one recorded intent_nonce per app_project

Activation helpers:

public.has_completed_contributor_activation_gate()
public.ensure_current_contributor()

The activation gate requires all required document acceptances and a confirmed SEPA IBAN receiving method. manual_review is not sufficient for automatic activation.

The Playroll app project owns the read projection:

playroll_supa_new.public.contributor_status_projection

Important fields:

app_user_id
contributor_id
link_status
source_link_id
steam_id
linked_at
source_updated_at
last_synced_at

RLS allows authenticated app users to read only their own projection row. The projection is intentionally minimal and must not contain legal-document evidence, IBAN data, payout method details, or other portal PII.

RLS Helpers

The initial migration defines:

public.current_contributor_id()
public.is_ec_domain_user()

public.current_contributor_id() supports own-row contributor access. public.is_ec_domain_user() is reserved for a future admin surface and must not be used to gate the public application page.

Admin/Service-Role Boundary

The following operations should not be browser-owned:

• creating contributor canonical records outside ensure_current_contributor() after the activation gate
• assigning recorder sessions
• writing validation results
• creating PlayRoll time ledger entries
• unlocking milestones
• creating compensation items from milestones
• creating payout requests
• approving and submitting payments
• generating receipts or value sheets
• calculating cap snapshots
• resolving exceptions