The Power of SQL and SQL Views in surveilr
SQL views are one of surveilr's most powerful features, enabling complex data transformations without compromising performance or security.
Why SQL Views?
Abstraction Layer
Views provide a clean interface to underlying data:
- Hide complexity from end users
- Enforce data access patterns
- Simplify reporting queries
Performance Benefits
- Computed on-demand
- Can be indexed for frequent queries
- No data duplication
Security Advantages
- Row-level security through view definitions
- Column masking for sensitive data
- Audit-friendly query patterns
Practical Examples
Compliance Dashboard View
```sql
CREATE VIEW compliance_status AS
SELECT
control_family,
COUNT(*) as total_controls,
SUM(CASE WHEN status = 'compliant' THEN 1 ELSE 0 END) as compliant
FROM controls
GROUP BY control_family;
```
Evidence Timeline View
Views can materialize complex temporal queries for audit purposes.
