backend/migrations/011_employee_invitations.sqlSQL
CREATE TABLE IF NOT EXISTS employee_invitations (
id CHAR(36) PRIMARY KEY,
tenant_id CHAR(36) NOT NULL,
employee_profile_id CHAR(36) NOT NULL,
email VARCHAR(320) NOT NULL,
role_id CHAR(36) NOT NULL,
token_hash CHAR(64) NOT NULL,
expires_at DATETIME NOT NULL,
created_by CHAR(36) NOT NULL,
created_at DATETIME NOT NULL,
accepted_at DATETIME NULL,
revoked_at DATETIME NULL,
UNIQUE KEY employee_invitations_token_unique (token_hash),
INDEX employee_invitations_tenant_status (tenant_id, accepted_at, revoked_at, expires_at),
CONSTRAINT employee_invitations_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id),
CONSTRAINT employee_invitations_profile_fk FOREIGN KEY (employee_profile_id) REFERENCES employee_profiles(id),
CONSTRAINT employee_invitations_role_fk FOREIGN KEY (role_id) REFERENCES roles(id),
CONSTRAINT employee_invitations_creator_fk FOREIGN KEY (created_by) REFERENCES users(id)
);