SQLite Forum

cycle detection in CHECK constraint with recursive CTE
OK, sure. What I propose is a pure TREE, a simpler form of DAG indeed.  
But still, what you have will not scale well. At least index the from/to tag,  
and probably also have a `UNIQUE(from, to)` contraints too.

That way the `WHERE from = ...` queries will range-scan that auto-index,  
and either the `WHERE to = ...` ones will skip-scan it using that same index,  
or create an explicit (non-UNIQUE) index on it. Look at the plans.

You probably also want to ON DELETE CASCADE in your FKs, to auto-delete edges,  
when tags are removed. Unless your ORM does that in code instead?

And you also don't need an `id` column for edges, but I suspect it's your ORM adding it, right?  
I'd make the edges table a `WITHOUT ROWID` table, and have the PK be `(from, to)`,  
effectively replacing the UNIQUE-index I proposed above.

Still, I somehow feel there's a better way to do what you want, I just don't see it now. Maybe I'm just imagining things.

PS: Also, don't forget to enable `pragma foreign_keys = 1`, unlike me!